John Cobby John Cobby - 1 month ago 11
SQL Question

SQL DATEDIFF on multiple different start dates

I'm trying to calculate how many days a batch has been going since it was started using

DATEDIFF
. I'm confident that what I've got would work if there were only one batch, however when I go onto using it for multiple at the same time it obviously won't work the way I want it to.

The issue I have is that there are multiple batches, and each batch has a different start date. So I need the
DATEDIFF
calculation to take into account which particular batch it's looking at when choosing which start date to use. Right now it's just using the earliest date in the table regardless of batch.

SELECT
T0.[Batch],
T0.[DateTime],
DATEDIFF(day, (SELECT TOP 1 T0.[DateTime]
FROM t005 T0
ORDER BY T0.[DateTime]), T0.[DateTime]) AS [Days],
MAX(T0.[PG]) AS [PG]
FROM
t005 T0


The results look like this:

Batch DateTime Days PG
3160036 2016-09-30 11:30:52.000 5 11.83
3160036 2016-09-28 08:35:50.000 3 11.85
3160036 2016-09-27 11:27:41.000 2 11.92
3160036 2016-09-26 07:33:27.000 1 12.18
3160036 2016-09-25 12:42:08.000 0 18.16


3160039 2016-09-28 08:28:50.000 3 18.4
3160039 2016-09-27 09:29:42.000 2 53.59
3160039 2016-09-27 09:19:32.000 2 52.73
3160039 2016-09-27 09:12:46.000 2 52.99
3160039 2016-09-27 09:05:52.000 2 53.08
3160039 2016-09-27 08:34:59.000 2 47.17

3160051 2016-10-27 12:56:33.000 32 11.31
3160051 2016-10-27 12:42:58.000 32 11.17
3160051 2016-10-24 06:12:35.000 29 11.18
3160051 2016-10-24 05:57:18.000 29 11.13


Many thanks.

tgr tgr
Answer

UPDATE: Also a filter was missing + desc sorting...

Have you maybe missed the connection between the batches?

CREATE TABLE t005 (
 Batch int
,[DateTime] datetime2
,pg float
)


INSERT INTO t005 values (3160036,  cast('2016-09-30 11:30:52' as datetime2), 11.83)
INSERT INTO t005 values (3160036,  cast('2016-09-28 08:35:50' as datetime2), 11.85)
INSERT INTO t005 values (3160036,  cast('2016-09-27 11:27:41' as datetime2), 11.92)
INSERT INTO t005 values (3160036,  cast('2016-09-26 07:33:27' as datetime2), 12.18)
INSERT INTO t005 values (3160036,  cast('2016-09-25 12:42:08' as datetime2), 18.16)

INSERT INTO t005 values (3160039,  cast('2016-09-28 08:28:50' as datetime2), 18.4 )
INSERT INTO t005 values (3160039,  cast('2016-09-27 09:29:42' as datetime2), 53.59)
INSERT INTO t005 values (3160039,  cast('2016-09-27 09:19:32' as datetime2), 52.73)
INSERT INTO t005 values (3160039,  cast('2016-09-27 09:12:46' as datetime2), 52.99)
INSERT INTO t005 values (3160039,  cast('2016-09-27 09:05:52' as datetime2), 53.08)
INSERT INTO t005 values (3160039,  cast('2016-09-27 08:34:59' as datetime2), 47.17)

INSERT INTO t005 values (3160051,  cast('2016-10-27 12:56:33' as datetime2), 11.31)
INSERT INTO t005 values (3160051,  cast('2016-10-27 12:42:58' as datetime2), 11.17)
INSERT INTO t005 values (3160051,  cast('2016-10-24 06:12:35' as datetime2), 11.18)
INSERT INTO t005 values (3160051,  cast('2016-10-24 05:57:18' as datetime2), 11.13)


SELECT 
T0.[Batch],
T0.[DateTime],
(SELECT TOP 1
                T1.[DateTime]
             FROM
                t005 T1
                WHERE T1.Batch = T0.Batch
                  AND T1.[DateTime] < T0.[DateTime]
            ORDER BY T1.[DateTime] DESC) as LastDateTime,
ISNULL(DATEDIFF(day,(SELECT TOP 1
                T1.[DateTime]
             FROM
                t005 T1
                WHERE T1.Batch = T0.Batch
                  AND T1.[DateTime] < T0.[DateTime]
            ORDER BY T1.[DateTime] DESC), T0.[DateTime]),0) AS [Days],
T0.[PG]  AS [PG]
FROM t005 as T0
ORDER BY T0.[Batch],
T0.[DateTime]

--drop table t005

Output:

Batch   |DateTime                    |LastDateTime                 |Days |PG
--------|----------------------------|-----------------------------|-----|----
3160036 |2016-09-25 12:42:08.0000000 |NULL                         |0    |18.16
3160036 |2016-09-26 07:33:27.0000000 |2016-09-25 12:42:08.0000000  |1    |12.18
3160036 |2016-09-27 11:27:41.0000000 |2016-09-26 07:33:27.0000000  |1    |11.92
3160036 |2016-09-28 08:35:50.0000000 |2016-09-27 11:27:41.0000000  |1    |11.85
3160036 |2016-09-30 11:30:52.0000000 |2016-09-28 08:35:50.0000000  |2    |11.83
3160039 |2016-09-27 08:34:59.0000000 |NULL                         |0    |47.17
3160039 |2016-09-27 09:05:52.0000000 |2016-09-27 08:34:59.0000000  |0    |53.08
3160039 |2016-09-27 09:12:46.0000000 |2016-09-27 09:05:52.0000000  |0    |52.99
3160039 |2016-09-27 09:19:32.0000000 |2016-09-27 09:12:46.0000000  |0    |52.73
3160039 |2016-09-27 09:29:42.0000000 |2016-09-27 09:19:32.0000000  |0    |53.59
3160039 |2016-09-28 08:28:50.0000000 |2016-09-27 09:29:42.0000000  |1    |18.4
3160051 |2016-10-24 05:57:18.0000000 |NULL                         |0    |11.13
3160051 |2016-10-24 06:12:35.0000000 |2016-10-24  05:57:18.0000000 |0    |11.18
3160051 |2016-10-27 12:42:58.0000000 |2016-10-24  06:12:35.0000000 |3    |11.17
3160051 |2016-10-27 12:56:33.0000000 |2016-10-27  12:42:58.0000000 |0    |11.31

And Query duration from the first execution of the batch:

SELECT 
T0.[Batch],
T0.[DateTime],
(SELECT TOP 1
                T1.[DateTime]
             FROM
                t005 T1
                WHERE T1.Batch = T0.Batch
                  AND T1.[DateTime] < T0.[DateTime]
            ORDER BY T1.[DateTime] ASC) as LastDateTime,
ISNULL(DATEDIFF(day,(SELECT TOP 1
                T1.[DateTime]
             FROM
                t005 T1
                WHERE T1.Batch = T0.Batch
                  AND T1.[DateTime] < T0.[DateTime]
            ORDER BY T1.[DateTime] ASC), T0.[DateTime]),0) AS [Days],
T0.[PG]  AS [PG]
FROM t005 as T0
ORDER BY T0.[Batch],
T0.[DateTime]

Output:

Batch   |DateTime                    |LastDateTime                |Days  |PG
--------|----------------------------|----------------------------|------|-------
3160036 |2016-09-25 12:42:08.0000000 |NULL                        |0     |18,16
3160036 |2016-09-26 07:33:27.0000000 |2016-09-25 12:42:08.0000000 |1     |12,18
3160036 |2016-09-27 11:27:41.0000000 |2016-09-25 12:42:08.0000000 |2     |11,92
3160036 |2016-09-28 08:35:50.0000000 |2016-09-25 12:42:08.0000000 |3     |11,85
3160036 |2016-09-30 11:30:52.0000000 |2016-09-25 12:42:08.0000000 |5     |11,83
3160039 |2016-09-27 08:34:59.0000000 |NULL                        |0     |47,17
3160039 |2016-09-27 09:05:52.0000000 |2016-09-27 08:34:59.0000000 |0     |53,08
3160039 |2016-09-27 09:12:46.0000000 |2016-09-27 08:34:59.0000000 |0     |52,99
3160039 |2016-09-27 09:19:32.0000000 |2016-09-27 08:34:59.0000000 |0     |52,73
3160039 |2016-09-27 09:29:42.0000000 |2016-09-27 08:34:59.0000000 |0     |53,59
3160039 |2016-09-28 08:28:50.0000000 |2016-09-27 08:34:59.0000000 |1     |18,4
3160051 |2016-10-24 05:57:18.0000000 |NULL                        |0     |11,13
3160051 |2016-10-24 06:12:35.0000000 |2016-10-24 05:57:18.0000000 |0     |11,18
3160051 |2016-10-27 12:42:58.0000000 |2016-10-24 05:57:18.0000000 |3     |11,17
3160051 |2016-10-27 12:56:33.0000000 |2016-10-24 05:57:18.0000000 |3     |11,31

Similar solution using Partition by...

SELECT a.* , datediff(day,LastDate,a.[DateTime]) as Diff 
FROM (
    SELECT 
    T0.[Batch],
    T0.[DateTime],
    MIN(T0.[DateTime]) OVER (PARTITION BY T0.[Batch]) as LastDate,
    T0.[PG]  AS [PG]
    FROM t005 as T0
) as a
ORDER BY a.[Batch],
a.[DateTime]