Calculating Date Differences - 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

General SQL Server Forums

New to SQL Server Programming

Calculating Date Differences

Author

Topic

Selley
Starting Member

3 Posts

Posted - 2009-08-13 : 18:51:27

Hello,

I have a table that contains production information for a factory:

|Start_date           |End_Date            |Product_id
|01/01/2009 05:00 |02/01/2009 08:00 |1
|02/01/2009 17:00 |04/01/2009 11:00 |2
|04/01/2009 23:00 |08/01/2009 10:00 |3
|08/01/2009 15:00 |12/01/2009 11:00 |1

I would like to run a query that will calculate the changeover time between products and return something like:

Prod_a | Date_Stopped    | Prod_b | Date_Started      | Changeover_Time
1        |02/01/2009 08:00| 2         |02/01/2009 17:00 | 9hrs
2        |04/01/2009 11:00| 3         |04/01/2009 23:00 | 12hrs
3        |08/01/2009 10:00| 1         |08/01/2009 15:00 | 5hrs

Any help would be appreciated.
Thank you.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-13 : 19:38:36

Don't understand the prod_a and prod_b in your sample, but you're looking for the DateDiff() function


SELECT DateDiff(hour, start_date, end_date) FROM yourTable

Selley
Starting Member

3 Posts

Posted - 2009-08-13 : 19:48:56

Sorry russell, I didn't make it very clear. I need to get the time difference between the end date of the first record and the start date of the next record and so on
e.g product 1 end date is 02/01/2009 08:00 and product 2 start date is 02/01/2009 17:00. I need to get the difference between these dates.

Thanks for your reply.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 01:10:39

[code]
With cte (id, End_Date, Start_Date, Product_id)
AS (
SELECT row_number() over (order by start_date) id,
End_Date, Start_Date, Product_id
FROM yourTable
)
SELECT a.Product_id Prod_a, a.End_Date Date_Stopped,
b.Product_id Prod_b, b.Start_Date Date_Started,
Convert(varchar(12), DateDiff(hour, a.End_Date, b.Start_Date)) + 'hrs' Changeover_Time
FROM cte a
JOIN cte b
On a.id = b.id - 1[/code]

Selley
Starting Member

3 Posts

Posted - 2009-08-14 : 16:36:13

Thanks Russell, really appreciate your help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 16:45:14

You're welcome. Glad 2 help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-15 : 03:41:47

another way


SELECT t.*,DATEDIFF(hh,t.End_Date,t1.Start_Date) AS ChangeOverTime
FROM YourTable t
CROSS APPLY (SELECT TOP 1 Start_Date
             FROM YourTable
             WHERE Start_date > t.End_Date 
             ORDER BY Start_date)t1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources