SQL performing day difference by matching value

Question

My goal is to get the duration when the 1st OLD or 1st NEW status reaches to the 1st END.

For example: Table1

ID   Day STATUS
111   1   NEW
111   2   NEW
111   3   OLD
111   4   END
111   5   END
112   1   OLD
112   2   OLD
112   3   NEW
112   4   NEW
112   5   END
113   1   NEW
113   2   NEW

The desired outcome would be:

STATUS Count

NEW 2 (1 for ID 111-New on day 1 to End on day 4,and 1 for 112-new on day 3 to End on day 5)

OLD 2 (1 for ID 111-Old on day 3 to End on day 4, and 1 for 112-OLD on day 1 to End on day 5)

tag the dbms being used. For eg: Oracle, MySQL, SQL Server, Postgres etc. — Vamsi Prabhala, Dec 14 '17 at 23:22
Oh I'm currently using ms access now but will be moving to SQL sever soon. Either one is fine. I'm more curious on the method. :) — TylerNG, Dec 14 '17 at 23:23
TylerNG we need to now what "brand" of database it is because while "sql" is a standard different databases implement it in different ways. so is it for example MySQL? Oracle? SQLite? Postgres? etc — Paul Maxwell, Dec 14 '17 at 23:31

score 1 · Accepted Answer · answered Dec 15 '17 at 01:57

The following is T-SQL (SQL Server) and NOT available in MySQL. The choice of dbms is vital in a question because there are so many dbms specific choices to make. The query below requires using a "window function" row_number() over() and a common table expression neither of which exist yet in MySQL (but will one day). This solution also uses cross apply which (to date) is SQL Server specific but there are alternatives in Postgres and Oracle 12 using lateral joins.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Table1
    (id int, day int, status varchar(3))
;

INSERT INTO Table1
    (id, day, status)
VALUES
    (111, 1, 'NEW'),
    (111, 2, 'NEW'),
    (111, 3, 'OLD'),
    (111, 4, 'END'),
    (111, 5, 'END'),
    (112, 1, 'OLD'),
    (112, 2, 'OLD'),
    (112, 3, 'NEW'),
    (112, 4, 'NEW'),
    (112, 5, 'END'),
    (113, 1, 'NEW'),
    (113, 2, 'NEW')
;

Query 1:

with cte as (
      select
              *
      from (
        select t.*
              , row_number() over(partition by id, status order by day) rn
        from table1 t
            ) d
      where rn = 1
      )
select 
       t.id, t.day, ca.nxtDay, t.Status, ca.nxtStatus   
from cte t
outer apply (
    select top(1) Status, day
    from cte nxt
    where t.id = nxt.id
    and t.status = 'NEW' and nxt.status = 'END'
    order by day
    ) ca (nxtStatus, nxtDay)
where nxtStatus IS NOT NULL or Status = 'OLD'
order by id, day

Results:

|  id | day | nxtDay | Status | nxtStatus |
|-----|-----|--------|--------|-----------|
| 111 |   1 |      4 |    NEW |       END |
| 111 |   3 | (null) |    OLD |    (null) |
| 112 |   1 | (null) |    OLD |    (null) |
| 112 |   3 |      5 |    NEW |       END |

As you can see, counting that Status column would result in NEW = 2 and OLD = 2

Thank you! I'll try that out once I get to move to ssms :) – TylerNG Dec 15 '17 at 02:11 — TylerNG, Dec 15 '17 at 02:11

SQL performing day difference by matching value

1 Answers1