SQL - Getting date difference between two rows between three tables

3
votes

I've scoured the forums, but couldn't quite find a proper solution.

I have two tables with the following information:

-TableA-

   Id   |   Created
  11111 | 2016-01-01
  22222 | 2016-02-02
  33333 | 2016-03-03

-TableB-

   Id   |   Created   |   Comment   
  11111 | 2016-01-01  | Blah Blah Blah
  11111 | 2016-01-02  | Blah Blah Blah
  11111 | 2016-01-15  | Blah Blah Blah
  11111 | 2016-01-17  | Blah Blah Blah
  22222 | 2016-02-02  | Blah Blah Blah
  22222 | 2016-02-05  | Blah Blah Blah
  22222 | 2016-02-09  | Blah Blah Blah
  33333 | 2016-03-03  | Blah Blah Blah
  33333 | 2016-03-14  | Blah Blah Blah

TableA is the master table (it has a whole bunch of other fields, but the important thing is the ID and the Created date field), while TableB is a comment table that gets tied back to TableA.

What I'm trying to do is to calculate the time difference between two rows in TableB and then isolate the very first row where the record is created. I figured the best way to do this would be to use TableA to provide the definitive Created date and somehow use that against TableB after I obtain all of the calculated time differences.

I've written out a reasonable query for TableB to give me the calculated date differences:

SELECT C1.Id,
       C1.Created,
       MIN(C2.Created) AS Created2,
       DATEDIFF(C1.Created, MIN(C2.Created) AS DaysDiff
       FROM TableB C1
            LEFT JOIN TableB C2
            ON C1.Id = C2.Id
            AND C2.Created > C1.Created
       GROUP BY C1.Id, C1.Created

-TableB Queried-

   Id   |   Created   |  Created2   |    DaysDiff
  11111 | 2016-01-01  | 2016-01-02  |       1
  11111 | 2016-01-02  | 2016-01-15  |      13
  11111 | 2016-01-15  | 2016-01-17  |       2
  11111 | 2016-01-17  |             |       
  22222 | 2016-02-02  | 2016-02-05  |       3
  22222 | 2016-02-05  | 2016-02-09  |       4
  22222 | 2016-02-09  |             |        
  33333 | 2016-03-03  | 2016-03-14  |      11
  33333 | 2016-03-14  |             |

But I need to take this one step further and only get the earliest Created record, so it looks like this:

   Id   |   Created   |  Created2   |    DaysDiff
  11111 | 2016-01-01  | 2016-01-02  |       1
  22222 | 2016-02-02  | 2016-02-05  |       3
  33333 | 2016-03-03  | 2016-03-14  |      11

I'm pretty sure I need to do one more JOIN here, but any JOIN I've done usually ends up where I get no records or I get just the Id and Created columns and nothing else.

Thanks for the help!

4
sql
Welcome to stack overflow. From the example results you have given, you seem to be looking for the entry in table B that was created the least number of days after the ID was created in table A. Is that right? - bitfiddler
Sort of. What I'm looking to do is to get the difference between the first and second record in TableB for each ID number. - Jon
sql bu itself isn't sufficient as a tag, please be more specific as options vary a lot: what flavour of dbms is it? - Paul Maxwell
Technically it's sitting on top of a PostgreSQL DB, but the problem is that there is a layer above the PostgreSQL DB that you can run SQL queries on it, but you don't get all the regular bells and whistles that you would normally get with PostgreSQL. Also, this layer can sit on MS SQL as well, but the just like before, you don't get all the bells and whistles either. This is why I put down just SQL for the tag, since the query needed to be somewhat universal. - Jon

4 Answers

1
votes

To get your expected result, you should use this:

SELECT C1.Id,
       C1.Created,
       MIN(C2.Created) AS Created2,
       DATEDIFF(C1.Created, MIN(C2.Created)) AS DaysDiff
FROM (select id, min(created) created from TableB group by id) C1
    JOIN TableB C2
    ON C1.Id = C2.Id
    AND C2.Created > C1.Created
GROUP BY C1.Id, C1.Created
;

And in the beginning, i thought that the temp C1 table should be tableA, not subquery select min(created) from tableB. If that's true then change that line: FROM (....) C1 to From tableA C1

0
votes

Try something like this:

select A.id, A.created, B.created, DATEDIFF(B.created, A.created) AS DaysDiff
  from TableA A
  join TableB B on B.id = A.id and
  B.created = (select min(created) from TableB where
            created > A.created)

Here's a SQLFiddle to try it: http://www.sqlfiddle.com/#!9/859113/3

0
votes

Use TableA to join with TableB instead of using TableB twice that way you'll get rid of the unwanted minimum created date

SELECT
    TableA.Id,
    TableA.Created,
    MIN(TableB.Created) AS Created2,
    DATEDIFF(MIN(TableB.Created), TableA.Created) AS DaysDiff
FROM TableA 
     JOIN TableB 
        ON TableA.Id = TableB.Id
            AND TableA.Created < TableB.Created
GROUP BY
    TableA.Id,
    TableA.Created

Tested and working + doesn't need a SELECT in another SELECT http://www.sqlfiddle.com/#!9/859113/8

Thx to @bitfiddler for the fiddle I reused :)

0
votes

As a T-SQL option, cross apply could be used e.g.

|   id |    created |    created | DaysDiff |
|------|------------|------------|----------|
| 1111 | 2016-01-01 | 2016-01-02 |        1 |
| 2222 | 2016-02-02 | 2016-02-05 |        3 |
| 3333 | 2016-03-03 | 2016-03-14 |       11 |


SELECT
      A.id
    , A.created
    , B.created
    , DATEDIFF(day,A.created,B.created) AS DaysDiff
FROM TableA A
      CROSS APPLY (
            SELECT
                  MIN(created) created
            FROM TableB
            WHERE created > A.created
                  AND TableB.id = A.id
      ) B

See this SQL Fiddle