sql query count rows per id to by selecting range between 2 min dates in different columns

       temp
|id|received  |changed   |ur|context|
|33|2019-02-18|2019-11-18|
|33|2019-08-02|2019-09-18|
|33|2019-12-27|2019-12-18|
|18|2019-07-14|2019-10-18|
|50|2019-03-20|2019-05-26|
|50|2019-01-19|2019-06-26|

       temp2
|id|min_received  |min_changed   |
|33|2019-02-18    |2019-09-18    |
|18|2019-04-14    |2019-09-18    |
|50|2019-01-11    |2019-05-25    |

The ‘temp’ table shows users who received a request for an activity. A user can make multiple requests. Hence the received column has multiple dates showing when the requests was received. The ‘changed’ table shows when the status was changed. There are also multiple values for it.

There is another temp2 column which shows the min dates for received and changed. Need to count total requests per user between the range of values in temp2

The expected result should look like this :- The third row of id- 33 should not be selected because the received date is after the changed date.

|id|total_requests_sent|
|33|2                  |
|18|1                  |
|50|2                  |

Tried Creating 2 CTE’s for both MIN date values and joined with the original one

Answer

I may be really over-simplifying your task, but wouldn’t something like this work?

select
  t.id, count (*) as total_requests_sent
from
  temp t
  join temp2 t2 on
    t.id = t2.id
where
  t.received between t2.min_received and t2.min_changed
group by
  t.id

I believe the output will match your example on the use case you listed, but with a limited dataset it’s hard to be sure.

Advertisement

Answer