Thank you dwain.c, i'm working through the query now to under stand it (pretty complicated 🙂
hi Chris.m below is the sample data as well as my expected output (sorry i should have provided this earlier)
CREATE TABLE [dbo].[sampleA](
[visit_date] [date] NULL,
[enter_datetime] [datetime] NULL,
[depart_datetime] [datetime] NULL,
[vin] [int] NULL,
[make] [char](10) NULL
) ON [PRIMARY]
INSERT INTO [sampleA]
([visit_date]
,[enter_datetime]
,[depart_datetime]
,[vin]
,[make])
VALUES
('2011-01-01','2011-01-01 23:00','2011-01-02 1:35','222','vw'),
('2012-01-08','2012-01-08 22:30','2012-01-11 17:01','333','honda'),
('2013-12-25','2013-12-25 0:10','2013-12-25 2:25','111','ford')
GO
--DROP TABLE SampleA
Expected output (i insert these into another table for further manipulation:)
visit_date VIN make Hour_in_service
12/25/2013111ford 0
12/25/2013111ford 1
12/25/2013111ford 2
1/1/2011222vw 23
1/2/2011222vw 0
1/2/2011222vw 1
1/8/2012333honda22
1/8/2012333honda23
1/9/2012333honda0
1/9/2012333honda1
1/9/2012333honda2
1/9/2012333honda3
1/9/2012333honda4
1/9/2012333honda5
1/9/2012333honda6
1/9/2012333honda7
1/9/2012333honda8
1/9/2012333honda9
1/9/2012333honda10
1/9/2012333honda11
1/9/2012333honda12
1/9/2012333honda13
1/9/2012333honda14
1/9/2012333honda15
1/9/2012333honda16
1/9/2012333honda17
1/9/2012333honda18
1/9/2012333honda19
1/9/2012333honda20
1/9/2012333honda21
1/9/2012333honda22
1/9/2012333honda23
1/10/2012333honda0
1/10/2012333honda1
1/10/2012333honda2
1/10/2012333honda3
1/10/2012333honda4
1/10/2012333honda5
1/10/2012333honda6
1/10/2012333honda7
1/10/2012333honda8
1/10/2012333honda9
1/10/2012333honda10
1/10/2012333honda11
1/10/2012333honda12
1/10/2012333honda13
1/10/2012333honda14
1/10/2012333honda15
1/10/2012333honda16
1/10/2012333honda17
1/10/2012333honda18
1/10/2012333honda19
1/10/2012333honda20
1/10/2012333honda21
1/10/2012333honda22
1/10/2012333honda23
1/11/2012333honda0
1/11/2012333honda1
1/11/2012333honda2
1/11/2012333honda3
1/11/2012333honda4
1/11/2012333honda5
1/11/2012333honda6
1/11/2012333honda7
1/11/2012333honda8
1/11/2012333honda9
1/11/2012333honda10
1/11/2012333honda11
1/11/2012333honda12
1/11/2012333honda14
1/11/2012333honda15
1/11/2012333honda16
1/11/2012333honda17