CODEWITHSUNDEEP
sql-server
eltel2910
eltel2910

Reputation: 345

SQL Server sum total every half hour

I have 2 columns: [StartDT] and [EndDT], they are datetime format like such: 2014-05-20 12:45:18.383

I need to do two things: (1) Extract the difference (at the millisecond level) between [StartDT] and [EndDT] as 'Difference' and (2) Take the sum of 'Difference' at 1/2 hour intervals.

For (1) I have:

DATEDIFF ( MS , [StartDT] , [EndDT] ) [Difference]

and that works fine by creating a new column and inserting the difference in it, but how can I take the sum 'Difference' every half hour and create a 'sum' column for these results at 1/2 hour intervals? So really I don't need the 'Difference' column, but rather a 'Sum' column, but not sure where to start.

Upvotes: 0

Views: 534

Answers (2)

Anon
Anon

Reputation: 10908

;WITH
  intervals AS (
    SELECT
      DATEADD(minute,30*ROW_NUMBER() OVER(ORDER BY (SELECT 1)),0) [interval]
    FROM master.dbo.spt_values t1, master.dbo.spt_values t2
  )
 ,running_totals AS (
    SELECT
      [EndDT]
     ,SUM(DATEDIFF(MS,[StartDT],[EndDT])) OVER(ORDER BY [EndDt]) [rt_Difference]
    FROM MyTable
  )
SELECT 
  [interval]
 ,[rt_Difference]
FROM [intervals] i
CROSS APPLY (
  SELECT TOP (1)
    [rt_Difference]
  FROM running_totals
  WHERE [EndDt] <= [interval]
  ORDER BY [EndDt] DESC
) d

Upvotes: 1

bepike
bepike

Reputation: 19

Create a Stored Procedure that Sums your datediff and inserts it into a table. then set up a job that runs your stored proc every half hour.

Upvotes: 0

Related Questions