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,

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?

2 - breaking down the query so i understand it more, whats going on is:

creating 4 cte tables

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