Search code examples
sqlms-accessduplicatesdatediff

Access SQL calculating date difference from dup

I have a table that records the status by date t1

ID  Date    Status
101 1   N
101 2   N
101 3   P
101 4   P
101 5   R
101 6   R
101 7   R
102 1   N
102 2   N
102 3   P
102 4   N
102 5   N
102 6   N
102 7   P
103 5   N
103 6   N
103 7   P

but I've been trying to get a result where I can count the period for each status and only show the date where the status changes.

ID  Date    Status  Period
101 1   N   2
101 3   P   2
101 5   R   2
102 1   N   2
102 3   P   1
102 4   N   3
102 7   P   1
103 5   N   2
103 7   P   1

Please help... Thank you in advance!

Solution

  • I think this gets you what you're looking for:

    SELECT Table.*, tA.Period
FROM Table INNER JOIN (SELECT ID, Status, MIN(Date) AS DateOfChange, COUNT(*) as Period
                       FROM Table
                       GROUP BY ID, Status) AS tA ON Table.ID = tA.ID 
                                               AND Table.Date = tA.DateOfChange