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