The purpose of this blog is to highlight two things
1. Generic solution for aggregating data over fixed time slots
2. Illustration of passing table valued parameters in SQL 2008
Quite often we’ve had occasions where we required to aggregate our data over time slots like average number of calls that came, summary of sales happened etc.This would typically for doing trend analysis over slots for identifying off peak and peak slots. Below given is a generic approach for dealing with such scenarios.
To illustrate this, I’ve created a function which accepts a table as a parameter. Table valued parameters are new feature that is introduced from SQL 2008 onwards.For creating a table valued parameter we need to first define a user defined table type in our db. For example scenario I’m creating a table type with structure as below
CREATE TYPE dbo.LogTable AS TABLE
(
ID int IDENTITY(1,1) NOT NULL,
LogTime datetime NOT NULL DEFAULT GETDATE(),
Value int,
PRIMARY KEY (ID)
)
GO
Consider the scenario where we have sales data with time and we require analysis of sales summary over fixed slots of time which we need to determine dynamically.
This can represented by the below sample data. Please note the type specified for the variable. It represents an instance of table type we created earlier.
DECLARE @MyTable LogTable
INSERT @MyTable (Value,LogTime)
SELECT FLOOR(100*RAND()+number) ,DATEADD(mi,-number,GETDATE())
FROM master..spt_values
where [type]=’p’
and number BETWEEN 1 AND 10
SELECT * FROM @MyTable ORDER BY LogTime
Now create a function like below
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME =’GetSlotTotals‘ AND ROUTINE_SCHEMA = ‘dbo’ AND ROUTINE_TYPE=’FUNCTION’)
DROP FUNCTION dbo.GetSlotTotals
GO
CREATE FUNCTION dbo.GetSlotTotals
(
@InputTable LogTable READONLY,
@SlotWidth int
)
RETURNS @RESULTS Table
(
ID int IDENTITY(1,1),
SlotStart datetime,
SlotEnd datetime,
ValSum int
)
AS
BEGIN
INSERT INTO @RESULTS (SlotStart,SlotEnd,ValSum)
SELECT DATEADD(mi,DATEDIFF(mi,0,LogTime)/@SlotWidth*@SlotWidth,0) As Start , DATEADD(mi,(DATEDIFF(mi,0,LogTime)/@SlotWidth+1)*@SlotWidth-1,0) AS [End], SUM(Value)
FROM @InputTable
GROUP BY DATEADD(mi,DATEDIFF(mi,0,LogTime)/@SlotWidth*@SlotWidth,0), DATEADD(mi,(DATEDIFF(mi,0,LogTime)/@SlotWidth+1)*@SlotWidth-1,0)
RETURN
END
The above function consists of two parameters one of table type created earlier to pass actual data table in example and another integer parameter representing span of slot we require
And call it like below for getting each of slot aggregates
DECLARE @MyTable LogTable
INSERT @MyTable (Value,LogTime)
SELECT FLOOR(100*RAND()+number) ,DATEADD(mi,-number,GETDATE())
FROM master..spt_values
where [type]=’p’
and number BETWEEN 1 AND 10
SELECT * FROM @MyTable ORDER BY LogTime
SELECT SlotStart,SlotEnd,ValSum FROM dbo.GetSlotTotals(@MyTable,10) ORDER BY SlotStart
SELECT SlotStart,SlotEnd,ValSum FROM dbo.GetSlotTotals(@MyTable,2) ORDER BY SlotStart
Good work , Keep it up