Hi ChrisM@work and others!
It seems that the output from this query was great for the customers and now they are asking to expand it and once again i would like to ask for assistance.
The query does a great job of tracking the hours between two dates so i can capture at which hours a car was serviced in the day. The answer to my question (was answered by ChrisM@Work, thank you sir) works great and now i need it expanded into listing days along with the hours.
Data Example:
Mazda
In Shop Date: 2014-07-01
In Shop Time: 22:00
Out Shop Date: 2014-07-02
Out Shop Time: 03:00
This output would look as follows:
date |h0|h1|h2|h3|h4|h5|h6|h7|h8|h9|h10|h11|h12|h13|h14|h15|h16|h17|h18|h19|h20|h21|h22|h23
2014-07-01 1
2014-07-01 1
2014-07-02 1
2014-07-02 1
2014-07-02 1
The code that i have used again was:
;WITH
[4] AS (SELECT n = 0 FROM (VALUES (0), (0), (0), (0)) d (n)),
[16] AS (SELECT n = 0 FROM [4] a, [4] b),
[256] AS (SELECT n = 0 FROM [16] a, [16] b),
[65536] AS (SELECT n = 0 FROM [256] a, [256] b),
[4294967296] AS (SELECT n = 0 FROM [65536] a, [65536] b),
iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 FROM [4294967296] a, [16] b)
INSERT INTO Shop_log
(visit_date
,h0
,h1
...etc)
SELECT
??? <- field i am trying to capture (before it was just the In Shop Date but the records would show same date)
,h0
,h1
..etc
from CARS a
CROSS APPLY (
SELECT TOP (1+ DATEDIFF(hour, a.In_Shop_Date, a.Out_Shop_Date))
Hour_in_service = DATEPART(HOUR,DATEADD(hour,n,a.In_Shop_Date))
FROM iTally t
) x
Much appreciated