cs_source (1/3/2014)
okay, wow well thank you all for your queries, i learnt something new with each one, the last query outputs the data in the format i'm exactly looking for 🙂 so thank you,
You're welcome
2 quick questions:
1 - if i want the visit date reflected with the date the hours fall under is that another query where i update the table that this data will go into?
Easiest way to answer this is if you can post it as expected output. A picture tells etc etc
2 - breaking down the query so i understand it more, whats going on is:
creating 4 cte tables
CTE's aren't tables. They're more or less views built on the fly, kinda like macro's.
populating them with ascending integers
select the data and cross apply
its the second half of the query that i'm trying to wrap my head around:
SELECT TOP (1 + DATEDIFF(hour, a.enter_datetime, a.depart_datetime))
-- This gets the difference in hours for each enter/depart time and top 1 signals ?? when i remove the 1 i lose a record when i add the 1 i gain a record
Hour_in_service = DATEPART(HOUR,DATEADD(hour,n,a.enter_datetime))
-- a little confused here how n plays a role with the enter time
FROM iTally t
Have a look at Jeff's Tally Tables article, currently on the hotlist.
Edited to fix tags
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