September 18, 2007 at 10:25 am
Need some ideas and direction on how to handle this. I have records in a table that have a start date and end date. What I need to do is get a count by week of how many records were open in each week. I want to base this by passing in a date to begin analyzing with.
So, as an example if I had the following records (PKID, start_date, end_date)
1, 1/1/2007, 1/12/2007
2, 1/8/2007, 1/22/2007
3, 1/22/2007, 1/25/2007
I'd like the results to look like this if I said to look at anyting with a start date >= 1/1/2007
BeginWkDate, EndWkDate, Count
1/1/2007, 1/7/2007, 1
1/8/2007, 1/14/2007, 2
1/15/2007, 1/21/2007, 1
1/22/2007, 1/28/2007, 2
September 18, 2007 at 11:01 am
You will need a Numbers table to get the date range. You can get the script to create the numbers table from here
DECLARE @tbl TABLE(PkID int, Start_Date datetime, End_Date datetime)
DECLARE @Pass_Date datetime
INSERT INTO @tbl
SELECT 1,'2007-01-01', '2007-01-12'
UNION
SELECT 2, '2007-01-08', '2007-01-22'
UNION
SELECT 3, '2007-01-22', '2007-01-25'
SELECT * FROm @tbl
SET @Pass_Date = '2007-01-01'
SELECT DATEADD(DAY,(n.N-1) * 7,@Pass_Date) BeginWeek, DATEADD(DAY,(n. N * 7)-1,@Pass_Date) EndWeek,
(SELECT COUNT(*) FROM @tbl t WHERE
t.Start_Date BETWEEN DATEADD(DAY,(n.N-1) * 7 ,@Pass_Date) AND DATEADD(DAY,(n. N * 7)-1,@Pass_Date) OR
t.End_Date BETWEEN DATEADD(DAY,(n.N-1) * 7 ,@Pass_Date) AND DATEADD(DAY,(n. N * 7)-1,@Pass_Date) OR
(t.Start_Date < DATEADD(DAY,(n.N-1) * 7 ,@Pass_Date) AND t.End_Date > DATEADD(DAY,(n. N * 7)-1,@Pass_Date) )
)
FROM dbo.[Numbers] n
WHERE DATEADD(DAY,n. N * 7,@Pass_Date) <= GETDATE()
September 18, 2007 at 1:42 pm
Thanks. This works great. Had to add some logic in to account for items that did not yet have an end_date but that was easy. Just used ISNULL and assigned the current date when there was no end_date.
Thanks again for your help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply