MartinC
asked on
SQL "GROUP BY" where the groups are weeks
Hi experts, I am looking for a way in SQL (on Microsoft SQL Query Analyzer if that matters) to do a GROUP BY function on COUNT, but where the data to be counted have to be grouped logically into time periods. These are the two tables:
tblCustomer
CustID StartDate
1 2005-02-21
2 2005-05-18
3 2005-06-13
tblCall
CustID CallDate
1 2005-02-24
1 2005-02-25
1 2005-02-29
2 2005-05-20
2 2005-05-27
2 2005-05-28
3 2005-06-14
3 2005-06-15
3 2006-06-30
And this is the result set I want to get:
Week Calls
1 5
2 3
3 1
There are two issues here: firstly I have to get the CallDate RELATIVE to that customer's StartDate; and secondly I have to group by weeks, where Week 1 is defined as "CallDate > StartDate AND CallDate < StartDate + 7"; Week 2 is defined as "CallDate > StartDate + 7 AND CallDate < StartDate + 14" etc.
I actually have 52 weeks to do, but I don't mind laboriously typing in definitions as long as it all works.
I suppose some sort of temporary calculated field will be required, but I am quite hazy on them! I need the actual SQL that would result in the data set above. Thanks!
tblCustomer
CustID StartDate
1 2005-02-21
2 2005-05-18
3 2005-06-13
tblCall
CustID CallDate
1 2005-02-24
1 2005-02-25
1 2005-02-29
2 2005-05-20
2 2005-05-27
2 2005-05-28
3 2005-06-14
3 2005-06-15
3 2006-06-30
And this is the result set I want to get:
Week Calls
1 5
2 3
3 1
There are two issues here: firstly I have to get the CallDate RELATIVE to that customer's StartDate; and secondly I have to group by weeks, where Week 1 is defined as "CallDate > StartDate AND CallDate < StartDate + 7"; Week 2 is defined as "CallDate > StartDate + 7 AND CallDate < StartDate + 14" etc.
I actually have 52 weeks to do, but I don't mind laboriously typing in definitions as long as it all works.
I suppose some sort of temporary calculated field will be required, but I am quite hazy on them! I need the actual SQL that would result in the data set above. Thanks!
refining appari's approach a bit:
select weekNo, count(*) calls from
(
SELECT INT(DATEDIFF("d", StartDate, callDate)/7)+1 AS weekNo
from tblCall join tblCustomer on tblCustomer.CustID = tblCall.CustID
)
group by weekNo
select weekNo, count(*) calls from
(
SELECT INT(DATEDIFF("d", StartDate, callDate)/7)+1 AS weekNo
from tblCall join tblCustomer on tblCustomer.CustID = tblCall.CustID
)
group by weekNo
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to both of you. Your solution was very helpful. I had to tweak it a bit (bracket before "/7", and I had to use a temporary table because I am using an old version of SQL Server and it didn't like getting results from subselects), but it is working now and management are very happy. Well, they're happy they've GOT the data, they're not happy WITH the data, but that is not my problem. ;-)
select weekNo, sum(callNo) calls from (
SELECT DATEDIFF(day, StartDate, callDate)/7 AS weekNo, 1 as callNo from
tblCall join tblCustomer on tblCustomer.CustID = tblCall.CustID
) A group by weekNo