Calculating minutes between two timestamps in a pivot table

I have a SQL query that makes a pivot table showing the timestamps of various temperature points being collected. I need to add columns to show the difference between point 1 (126) and point 2 (120) and so on.

Here is the current query I am using to get the timestamps for each point

DECLARE @chart1_start datetime
DECLARE @chart2_start datetime
DECLARE @chart3_start datetime
DECLARE @chart4_start datetime
DECLARE @chart5_start datetime

SET @chart1_start = (SELECT top 1 datetime from hotbox.dbo.temps WHERE rack = 'chart 1' AND TEMP >= 126 and DATETIME >= dateadd(day,datediff(day,1,GETDATE()),0) AND datetime < GETDATE() ORDER BY datetime ASC)
SET @chart2_start = (SELECT top 1 datetime from hotbox.dbo.temps WHERE rack = 'chart 2' AND TEMP >= 126 and DATETIME >= dateadd(day,datediff(day,1,GETDATE()),0) AND datetime < GETDATE() ORDER BY datetime ASC)
SET @chart3_start = (SELECT top 1 datetime from hotbox.dbo.temps WHERE rack = 'chart 3' AND TEMP >= 126 and DATETIME >= dateadd(day,datediff(day,1,GETDATE()),0) AND datetime < GETDATE() ORDER BY datetime ASC)
SET @chart4_start = (SELECT top 1 datetime from hotbox.dbo.temps WHERE rack = 'chart 4' AND TEMP >= 126 and DATETIME >= dateadd(day,datediff(day,1,GETDATE()),0) AND datetime < GETDATE() ORDER BY datetime ASC)
SET @chart5_start = (SELECT top 1 datetime from hotbox.dbo.temps WHERE rack = 'chart 5' AND TEMP >= 126 and DATETIME >= dateadd(day,datediff(day,1,GETDATE()),0) AND datetime < GETDATE() ORDER BY datetime ASC)



select * From (
select temps.rack as chart, min(dateadd(mi, datediff(mi, 0, temps.DATETIME), 0)) as time,temps.TEMP as temps from hotbox.dbo.temps WHERE DATETIME >= @chart1_start
  AND DATETIME <= DATEADD(HOUR,11,CONVERT(VARCHAR(10), GETDATE(),110)) and temps.RACK = 'chart 1' group by temps.RACK, temps.TEMP
  UNION
  select temps.rack as chart, min(dateadd(mi, datediff(mi, 0, temps.DATETIME), 0)) as time,temps.TEMP as temps from hotbox.dbo.temps WHERE DATETIME >= @chart2_start
  AND DATETIME <= DATEADD(HOUR,11,CONVERT(VARCHAR(10), GETDATE(),110)) and temps.RACK = 'chart 2' group by temps.RACK, temps.TEMP
  UNION
  select temps.rack as chart, min(dateadd(mi, datediff(mi, 0, temps.DATETIME), 0)) as time,temps.TEMP as temps from hotbox.dbo.temps WHERE DATETIME >= @chart3_start
  AND DATETIME <= DATEADD(HOUR,11,CONVERT(VARCHAR(10), GETDATE(),110)) and temps.RACK = 'chart 3' group by temps.RACK, temps.TEMP
  UNION
  select temps.rack as chart, min(dateadd(mi, datediff(mi, 0, temps.DATETIME), 0)) as time,temps.TEMP as temps from hotbox.dbo.temps WHERE DATETIME >= @chart4_start
  AND DATETIME <= DATEADD(HOUR,11,CONVERT(VARCHAR(10), GETDATE(),110)) and temps.RACK = 'chart 4' group by temps.RACK, temps.TEMP
  UNION
  select temps.rack as chart, min(dateadd(mi, datediff(mi, 0, temps.DATETIME), 0)) as time,temps.TEMP as temps from hotbox.dbo.temps WHERE DATETIME >= @chart5_start
  AND DATETIME <= DATEADD(HOUR,11,CONVERT(VARCHAR(10), GETDATE(),110)) and temps.RACK = 'chart 5' group by temps.RACK, temps.TEMP
  ) t
  PIVOT (
  min(time)
  FOR temps IN ([126],[120],[80],[55],[40])
  ) as Chart_table

The query outputs this

SQL results

Answer

It looks to me like you can just add it to the final select. I don’t know if it has to go into another derived table before it will work.

select *, DATEDIFF(MINUTE, [126], [120]) as [delta_minutes], ... From ( ...

Advertisement

Answer