June 8, 2012 at 8:49 am
I've got an interesting little problem that I can work my way around for a one-off exercise, but I'm intrigued by the problem and would love to discover a 'proper' solution.
Conisder an ongoing service contract where someone can be on-the-books for some time (possibly years). I need to identify the average number of clients on-the-books per week over the course of a year.
If this was a one-off service (i.e. instant delivery), it would be easy (just add the rows and divide by 52). However, because this example has one record covering multiple weeks, I'm a little stumped on how to do it efficiently.
Here is some sample data.
CREATE TABLE OngoingServices (client_no int, ServiceStartDate datetime, ServiceEndDate datetime)
GO
INSERT INTO OngoingServices
VALUES
('1', '2009-06-25 00:00:00', '2012-01-25 00:00:00'),
('2', '2011-02-16 00:00:00', NULL),
('3', '2011-05-09 00:00:00', '2011-09-15 00:00:00'),
('4', '2007-12-22 00:00:00', '2012-05-02 00:00:00'),
('5', '2010-04-01 00:00:00', '2011-06-15 00:00:00'),
('6', '2010-04-05 00:00:00', NULL),
('7', '2011-03-18 00:00:00', NULL),
('8', '2011-08-19 00:00:00', NULL),
('9', '2010-09-16 00:00:00', NULL),
('10', '2009-03-03 00:00:00', NULL)
Thoughts?
Stuart
June 8, 2012 at 9:06 am
the only way i've found to do this is to build a cross join of all clients and all weeks in the year where they are present
e.g (pseudo)
select weeknumber,count(*)
from clients cl cross join
weeksinyear yr
where... (insert calculation to determine if client is in this week here)
group by weeknumber
MVDBA
June 8, 2012 at 10:57 am
[font="Courier New"]/* Perhaps you could try something like this - using a Calendar table ? */
;
WITH DailyClientCount
AS ( SELECT CT.[Date] ,
COUNT(OS.client_no) AS ClientCount
FROM dbo.CalendarTable AS CT
LEFT JOIN dbo.OngoingServices AS OS
ON CT.[Date] BETWEEN OS.ServiceStartDate AND OS.ServiceEndDate
GROUP BY CT.[Date]
)
SELECT DATEPART(YEAR, Date) AS YearNo ,
DATEPART(ISO_WEEK, Date) AS IsoWeekNo ,
AVG(ClientCount) AS AverageClientCount
FROM DailyClientCount
GROUP BY DATEPART(YEAR, Date) ,
DATEPART(ISO_WEEK, Date)
ORDER BY YearNo ,
IsoWeekNo[/font]
June 9, 2012 at 6:55 am
I feel a bit foolish not thinking of a cross join appraoch. Good (and neat) suggestions - I'll emplore them when I'm back in the office on Monday.
Thanks
Stuart
June 11, 2012 at 5:02 am
Pls try this
;with Calender
As (
select
DATEADD(DD,
-ROW_NUMBER ()over (order by (select null)),GETDATE() ) as [date]
from sys.columns C
)
select count(Acc.client_no)as countofClients,
cal.WeekStartDate,
Cal.WeekEndDate
FROM #OngoingServices Acc
inner join
(
select [Date] as WeekStartDate , dateadd(DD ,7,[Date]) as WeekEndDate from Calender
where 'Sunday'=datename(weekday, [Date])
)Cal on Acc.servicestartdate <= Cal.WeekStartDate
and isnull(Acc.serviceenddate,'20990101')>=Cal.WeekEndDate
group by cal.WeekStartDate,
Cal.WeekEndDate
June 12, 2012 at 2:16 am
"I'll explore them when I'm back in the office on Monday" has become "I'll explore them when I'm back in the office on Thursday." However, I will look at responses.
Thanks
Stuart
June 14, 2012 at 6:32 am
I'm getting rather bogged down in various things and am doubtful that I'm going to have chance to come back for a proper response, but I though I should at least briefly respond.
Suffice it to say that the responses have all been helpful (including having different pros and cons) and I've got my chosen solution that works well.
Thanks
Stuart
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply