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:
And this is the result set I want to get:
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!