I have a query that outputs daily conveyor start time over a span of 7 days using the following query:
Select
format(min(case when location = 0 and scantime between getdate()-1 and getdate() then scantime end),'hh:mm:ss:tt') [StartTime1]
,format(min(case when location = 0 and scantime between getdate()-2 and getdate()-1 then scantime end),'hh:mm:ss:tt') [StartTime2]
,format(min(case when location = 0 and scantime between getdate()-3 and getdate()-2 then scantime end),'hh:mm:ss:tt') [StartTime3]
,format(min(case when location = 0 and scantime between getdate()-4 and getdate()-3 then scantime end),'hh:mm:ss:tt') [StartTime4]
,format(min(case when location = 0 and scantime between getdate()-5 and getdate()-4 then scantime end),'hh:mm:ss:tt') [StartTime5]
,format(min(case when location = 0 and scantime between getdate()-6 and getdate()-5 then scantime end),'hh:mm:ss:tt') [StartTime6]
,format(min(case when location = 0 and scantime between getdate()-7 and getdate()-6 then scantime end),'hh:mm:ss:tt') [StartTime7]
FROM [BaldorFoods_WcsDb].[dbo].[ScanLog]
where datepart(hour,scantime) in (18,19,20,21,22,23,0,1,2,3,4,5,6)
This outputs table in the image: MyTable. How would I go about changing this table so that the row names are labeled StartTime1, StartTime2, StartTime3.... etc.
I have tried using the Pivot table function but I am having difficulty specifying the inputs for the function.
I'd approach this somewhat differently
select format(scantime, 'hh:mm:ss:tt')
from (values
(0),
(1),
(2),
(3),
(4),
(5),
(6)
) as up(n)
cross apply (
select top(1) scantime
from [BaldorFoods_WcsDb].[dbo].[ScanLog]
where datepart(hour,scantime) in (18,19,20,21,22,23,0,1,2,3,4,5,6)
and scantime between getdate() - up.n and getdate() - (up.n-1)
order by scantime
) as d;
By way of explanation, let's break it down. Here's a simpler version that just gets you just the date boundaries:
select n, getdate() - up.n, getdate() - (up.n-1)
from (values
(0),
(1),
(2),
(3),
(4),
(5),
(6)
) as up(n)
From there, we use the cross apply
to get the first row (as ordered by your scantime column) that falls within a given set of date boundaries and has the predicate on the hour portion also applied.