Search code examples
sqlms-accessssmsarray-difference

sql queries by group and day difference

I want to calculate the day difference from Start/End comparing to End. I know I have to group them by Id but I'm not sure how to perform the difference between the Day. I got help on this using Python but I want to see the logic in SQL.

Table1

Id Day Status

111 1 Start

111 5 End

222 2 Begin

222 7 End

333 1 Start

333 3 Begin

333 7 End

Ideal result would be: 

Id Day Status Length

111 1 Start 4

111 5 End 

222 2 Begin 5

222 7 End

333 1 Start 6 (since we Start on Day 1 and End on day 7)

333 3 Begin 4 (since we Begin on Day 3 and End on day 7)

333 7 End

Thank you

Solution

  • Eg. You could do like this:

    Select a.Id, a.Day, a.Status, b.day-a.day From table1 a Left Join table1 b on a.id=b.id and a.status!='end' and b.status='end'