Help please!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help please!!!

Author

Topic

Diablos
Starting Member

10 Posts

Posted - 2011-12-07 : 05:28:22

Hi
I'm having troubles with the following i have a table like this:~

Driver Date Departure Hour Arrival Hour
P0003 2008-12-18 1754-01-01 06:50:00.000 1754-01-01 07:20:00.000
P0003 2008-12-18 1754-01-01 07:20:00.000 1754-01-01 07:50:00.000
P0003 2008-12-18 1754-01-01 08:00:00.000 1754-01-01 08:15:00.000
P0003 2008-12-18 1754-01-01 08:45:00.000 1754-01-01 09:25:00.000

I want to calculate the breaks so i need to this:
Driver Date Break(next departure - previous arrival hour)
P0003 2008-12-18 (1754-01-01 07:20:00.000-(1754-01-01 07:20:00.000)
P0003 2008-12-18 (1754-01-01 08:00:00.000)-(1754-01-01 07:50:00.000)
P0003 2008-12-18 (1754-01-01 08:45:00.000)-(1754-01-01 08:15:00.000)

Any ideias? Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-12-07 : 05:32:52

formatted with [C0DE] [/C0DE]


Driver	Date	    Departure Hour	    Arrival Hour
P0003	2008-12-18  1754-01-01 06:50:00.000 1754-01-01 07:20:00.000
P0003	2008-12-18  1754-01-01 07:20:00.000 1754-01-01 07:50:00.000
P0003	2008-12-18  1754-01-01 08:00:00.000 1754-01-01 08:15:00.000
P0003	2008-12-18  1754-01-01 08:45:00.000 1754-01-01 09:25:00.000

I want to calculate the breaks so i need to this:
Driver	Date	    Break(next departure - previous arrival hour)
P0003	2008-12-18  (1754-01-01 07:20:00.000-(1754-01-01 07:20:00.000)
P0003	2008-12-18  (1754-01-01 08:00:00.000)-(1754-01-01 07:50:00.000)
P0003	2008-12-18  (1754-01-01 08:45:00.000)-(1754-01-01 08:15:00.000)

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 05:39:29

[code]
SELECT t.Driver,t.Date,
DATEDIFF(ss,t.[Arrival Hour],t1.[Departure Hour]) AS [Break]
FROM Table t
CROSS APPLY (SELECT MIN([Departure Hour]) AS [Departure Hour]
FROM table
WHERE Driver=t.Driver
AND Date = t.Date
AND [Departure Hour] > t.[Arrival Hour]
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Diablos
Starting Member

10 Posts

Posted - 2011-12-07 : 06:14:44

quote:

Originally posted by visakh16


SELECT t.Driver,t.Date,
DATEDIFF(ss,t.[Arrival Hour],t1.[Departure Hour]) AS [Break]
FROM Table t
CROSS APPLY (SELECT MIN([Departure Hour]) AS [Departure Hour]
             FROM table
             WHERE Driver=t.Driver
             AND Date = t.Date 
             AND [Departure Hour] > t.[Arrival Hour]
            )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

THanks a lot visakh16 it worked cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 06:32:26

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources