troubleshooting Question

SQL "GROUP BY" where the groups are weeks

Avatar of MartinC
MartinC asked on
Microsoft SQL Server
5 Comments2 Solutions2421 ViewsLast Modified:
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!

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros