DateDiff of 2 dates in the same SQL column

Question

Using SQL Server 2005 I need to get a datediff from two dates that are in the same column, the table looks like this:

 OrderNo OpNo ResType LoadedStartDate ----------------------------------------------------- 12345 1 PAINT 2014-05-01 00:00:00.000 12345 2 PAINT 2014-05-02 00:00:00.000 12345 3 PAINT 2014-05-03 00:00:00.000 12345 4 ASMB 2014-05-04 00:00:00.000 67890 1 PAINT 2014-05-02 00:00:00.000 67890 2 PAINT 2014-05-03 00:00:00.000 67890 3 PAINT 2014-05-04 00:00:00.000 67890 4 ASMB 2014-05-05 00:00:00.000 

I need to get the diff date of OpNo 1 and OpNo 4, where they match the serial number. OpNo will always be 1 and 4 like the ones I'm trying to compare, as well as ResType.

The result should look like this:

 OrderNo Difference ---------------------- 12345 3 67890 3 

Thanks for any help :)

Answer 1

Just attach the table to yourself:

 SELECT t1.OrderNo,DATEDIFF(day,t1.LoadedStartDate,t2.LoadedStartDate) FROM UnnamedTableFromQuestion t1 INNER JOIN UnnamedTableFromQuestion t2 on t1.OrderNo = t2.OrderNo WHERE t1.OpNo = 1 and t2.OpNo = 4 

Answer 2

It should be pretty simple.

 SELECT DATEDIFF(day, (SELECT LoadedStartDate FROM Orders WHERE OrderNo = 12345 AND OpNo = 1), (SELECT LoadedStartDate FROM Orders WHERE OrderNo = 12345 AND OpNo = 4) ) 

Since internal selects, return scalar values ​​- they can be used as parameters for the DATEDIFF function

To make this work for all orders in the table, you can do something like this:

 SELECT DISTINCT OrderNo, DATEDIFF(day, (SELECT LoadedStartDate FROM Orders WHERE OrderNo = Ord.OrderNo AND OpNo = 1), (SELECT LoadedStartDate FROM Orders WHERE OrderNo = Ord.OrderNo AND OpNo = 4) ) AS Diff FROM Orders Ord 

Demo: http://sqlfiddle.com/#!3/bc085/5

Answer 3

In such scenarios, a miracle works. For your requirement, you can use the following query.

 with op1 As ( select orderno, opno, restype, LoadedStartDate From orders where opno = 1 ), op4 As ( select orderno, opno, restype, LoadedStartDate From orders where opno = 4 ) select op1.orderno, datediff(day, op1.loadedstartdate, op4.loadedstartdate) DifferenceDays from op1 inner join op4 on op1.orderno = op4.orderno 

Hope this helps

Answer 4

 SELECT DISTINCT ORDERNO,MAX(DATEPART(DAY,LoadedStartDate))-MIN(DATEPART(DAY,LoadedStartDate)) FROM ORDERS GROUP BY ORDERNO 

Remember, you can use:

 SELECT DISTINCT ORDERNO,MAX(LoadedStartDate)-MIN(LoadedStartDate) FROM ORDERS GROUP BY ORDERNO 

which returns 4 days in date and time format; possibly because without any time and because of the nature of MIN and MAX, the database assumes four whole days from the beginning of the first day to the end of day 4.

If you have time to order, and you want to get the exact time interval between the first and last order, it may be possible to convert both datetime times to an integer, which each gives in milliseconds, subtract the smaller number from the higher number, and then repeatedly translate the response back to the date and time format.