March 15, 2011 at 8:33 am
Hi there,
I know this is old ground but I seem to be struggling with this.
I'm trying to find a way of counting the amount of records in a table, I'll call tblRecord, that are created in a week from Saturday to Friday.
So I'd be grouping the calls between these days of the week and having the call count showing against the last day, which is the Friday
Please see sample data examples below:
The table I'm taking the data from would look like this
RecordID LoggedDate
987 5 Feb 2011
933 6 Feb 2011
784 6 Feb 2011
128 7 Feb 2011
748 8 Feb 2011
784 9 Feb 2011
745 10 Feb 2011
968 11 Feb 2011 --(8 record)
984 12 Feb 2011
156 13 Feb 2011
747 13 Feb 2011
852 13 Feb 2011
189 14 Feb 2011
365 15 Feb 2011
111 15 Feb 2011
164 16 Feb 2011
845 16 Feb 2011
945 17 Feb 2011
555 18 Feb 2011
645 18 Feb 2011 --(12 records)
etc etc....
I'd like the result to look like this:
RecordCount Week(LastdayFri)
8 11 Feb 2011 --see above example
12 18 Feb 2011 --see above example
15 18 Feb 2011
Each row has counted all calls from the previous Saturday to, and including, Friday
I'm usure if I need to create a calendar table for this and then join tblRecord using the dates.
If someone could help me with this I'd be most grateful
Cheers
March 15, 2011 at 10:23 am
Fun!
--==== Script test data
DECLARE @tblRecord TABLE
(
RecordID INT,
LoggedDate DATETIME NOT NULL
)
INSERT
@tblRecord
SELECT 987, '20110205' UNION ALL
SELECT 933, '20110206' UNION ALL
SELECT 784, '20110206' UNION ALL
SELECT 128, '20110207' UNION ALL
SELECT 748, '20110208' UNION ALL
SELECT 784, '20110209' UNION ALL
SELECT 745, '20110210' UNION ALL
SELECT 968, '20110211' UNION ALL
SELECT 984, '20110212' UNION ALL
SELECT 156, '20110213' UNION ALL
SELECT 747, '20110213' UNION ALL
SELECT 852, '20110213' UNION ALL
SELECT 189, '20110214' UNION ALL
SELECT 365, '20110215' UNION ALL
SELECT 111, '20110215' UNION ALL
SELECT 164, '20110216' UNION ALL
SELECT 845, '20110216' UNION ALL
SELECT 945, '20110217' UNION ALL
SELECT 555, '20110218' UNION ALL
SELECT 645, '20110218'
--==== Assuming some fridays could be missing from @tblRecord
--==== I will generate a Weeks table
DECLARE @Weeks TABLE
(
Saturday DATETIME,
Friday DATETIME
)
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
SELECT @StartDate = MIN(LoggedDate) FROM @tblRecord
SELECT @EndDate = MAX(LoggedDate) FROM @tblRecord
;WITH Dates AS
(
SELECT
Date = @StartDate
UNION ALL
SELECT
Date = DATEADD(DAY, 1, Date)
FROM
Dates
WHERE
DATEADD(DAY, 1, Date) <= @EndDate
)
INSERT
@Weeks
SELECT
Saturday = Date - 6,
Friday = Date
FROM
Dates
WHERE
--==== On my system @@DATEFIRST = 1 so Friday = 5
DATEPART(WEEKDAY, Date) = 5
--==== Count records grouped by Friday
SELECT
RecordCount = COUNT(*),
[Week(LastDayFri)] = Weeks.Friday
FROM
@Weeks AS Weeks
LEFT JOIN
@tblRecord AS Records ON
Weeks.Saturday <= Records.LoggedDate
AND
Records.LoggedDate <= Weeks.Friday
GROUP BY
Weeks.Friday
This should do the trick, make sure you run some test before running live!
Maxim
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply