Difference between two date/times and flag each hour in between

Question

Difference between two date/times and flag each hour in between

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

This looks like the output from a CROSSTAB query. If so, you would normally aggregate across whatever the partition is and use MAX() on each column value. Post your query if you are unsure.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

cs_source Default port Points: 1459 More actions · Answer 2

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-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