How to write a stored procedure to return a count of events grouped by 'X' week blocks?

Question

I have a table that records market interactions by employees with customers. Relevant fields would be customerid, date and customer type.

I want to be able to get a count of interactions by 5 week blocks going back as far as the between dates that would be submitted by the user and discriminate by customer type a,b,c.

I want to be able to take the returned data and create an a,b and c series with counts of market interactions grouped by 5 week blocks.

Something like:

 double[] byValues = { 14, 16, 18, 19, 15, 18, 19, 14, 15 };

 string[] bxValues = { "50", "45", "40", "35", "30", "25", "20", "15", "10" };

which might be the A-Series. Once I have my A, B and C series i can then feed them into a stackedchart graph.

I would also like the user to be able to adjust X=5 from 1 to 20 so it would be nice to have that variable as dynamic. Any pointers or urls would be appreciated.

My initial thoughts were to query once and create a temp table with an additional field that was populated by an if statement with some math in it that calculated the block rank of the record according to its date. Then i could requery that with a grouping count for the other criteria. But Im really not sure what the most performant way of getting that first step done, is. It seems like the kind of thing i could get badly wrong perhaps by orders of magnitude.

UPDATE:

This is what I have done and it works. Its actually quite simple. There are some literals in there which I can easily convert to parameters.

SELECT  mi1.[CAQ] AS CAQ, FLOOR(DATEDIFF(d, mi1.IDate, CONVERT(datetime, '11/07/2010', 103))/5) AS X_AXISBLOCK 
INTO #myTempTable
FROM [ql10_crm].[MarketInteraction] mi1 INNER JOIN [ql10_crm].[TerritoryCustomer] 
ON mi1.[CustomerId] =[ql10_crm].[TerritoryCustomer].[CustomerId] 
WHERE mi1.[CAQ] IN(1,2,4) AND 
[ql10_crm].[TerritoryCustomer].[TerritoryId] IN(19) AND (mi1.[IDate] BETWEEN CONVERT(datetime, '18/06/2009', 103) AND  CONVERT(datetime, '11/07/2010', 103));

SELECT CAQ, X_AXISBLOCK , COUNT([CAQ])
FROM #myTempTable
GROUP BY CAQ, X_AXISBLOCK 
ORDER BY CAQ, X_AXISBLOCK;

Not sure how performant or flexible it is in comparision to the answers. Will have to test.

This is my output (CAQ, X_AXISBLOCK , COUNT) Where a CAQ of 1=A, 2=B, 4=C:

2 0 2
2 1 6
2 6 2
4 0 3
4 1 5
4 6 4

Answer 1

You've lost me a bit with the talk of temp tables and block ranks but won't this do what you need?

DECLARE @X INT
SET @X =5

DECLARE @endDate DATETIME
DECLARE @startDate DATETIME

SET @endDate = '2010-07-18'
SET @startDate = '2009-07-18'

DECLARE @EndDateDays INT
SET @EndDateDays = CAST(@endDate AS INT);



WITH MarketInteractions AS
(
SELECT 1 AS customerid, CAST('2010-07-18' AS DATETIME) AS [DATE], 'a' AS CustomerType
UNION ALL
SELECT 1 AS customerid, CAST('2010-06-18' AS DATETIME) AS [DATE], 'b' AS CustomerType
UNION ALL
SELECT 1 AS customerid, CAST('2010-05-18' AS DATETIME) AS [DATE], 'a' AS CustomerType

),
MarketInteractions2 AS 
(
SELECT 1+ ((CAST(@endDate - [DATE] AS INT)) / (@X * 7)) AS Period, customerid, CustomerType
FROM MarketInteractions
WHERE [DATE] BETWEEN @startDate AND @endDate AND CustomerType IN ('a','b','c')
)

/* Possibly with a join on a numbers table to get periods with zero interactions if 
 that is possible*/

SELECT 
Period, 
COUNT(CASE WHEN CustomerType = 'a' THEN 1 END) AS a,
COUNT(CASE WHEN CustomerType = 'b' THEN 1 END) AS b,
COUNT(CASE WHEN CustomerType = 'c' THEN 1 END) AS c
FROM MarketInteractions2
GROUP BY Period,  CustomerType 

Answer 2

Just a thought, but it seems to me like you might be able to do this in pure SQL (usually preferable), rather than resorting to a stored procedure.

Have a look at the analytical functions available to you here. These have been well supported in Oracle for a long time now, but they are finding their way into SQL Server now.

By way of example, you would be looking at something like this (by no means a complete solution - just a pointer really):

SELECT a.custid,
       a.weekblock,
       COUNT(a.custid) OVER (PARTITION BY a.weekblock, a.custid)
  FROM (SELECT x.custid, DATEPART(wk, x.date) / 5 AS weekblock
          FROM market x) a

If you provide more information about your table structure, a better solution could probably be derived.