CODEWITHSUNDEEP
sqlt-sql
nicbjones
nicbjones

Reputation: 83

Counts based on an "as of" date

My apologies if I'm not wording the question correctly, and that's why I can't find any previous question/answers on this.....

My specific situation can be generalized as:

I have a table containing records of bed assignments for patients at a system of hospitals. A patient's placement into a bed is tagged with a date, and a reason for their placement there.

Patient |Hospital |Bed |Reason |Date
--------|---------|----|-------|--------
1234    |HOSP1    |111 |A      |1/1/2016
5678    |HOSP1    |222 |A      |2/1/2016
9012    |HOSP2    |333 |B      |3/1/2016
3456    |HOSP3    |444 |C      |3/1/2016
2345    |HOSP3    |555 |A      |3/1/2016
7890    |HOSP1    |111 |D      |4/1/2016

Based on the very small sample set above, I need to get a count of the "Reasons", per Hospital, given an "as of" date. So given an "as of" date of 3/15/2016:

As of Date: 3/15/2016
Hospital|Reason   |Count
--------|---------|-----
HOSP1   |A        |2
HOSP2   |B        |1
HOSP3   |A        |1
HOSP3   |C        |1

But when changing the "as of" date to 4/1/16, I would hope to see the following:

As of Date: 4/15/2016
Hospital|Reason   |Count
--------|---------|-----
HOSP1   |A        |1
HOSP1   |D        |1
HOSP2   |B        |1
HOSP3   |A        |1
HOSP3   |C        |1

Any suggestions on the best route to accomplish this without melting my CPU or the servers? (my real record set is about 36m rows, going back 15 years). And my ultimate end goal is determine yearly averages of "reason" counts at each "hospital", but I know the first step is to get these initial counts finalized first (or is it???).

Upvotes: 0

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

What you want is the most recent record before a certain date. This is pretty easy to do using window functions:

select hospital, reason, count(*)
from (select t.*,
             row_number() over (partition by hospital, bed order by date desc) as seqnum
      from t
      where date <= '2016-03-15'
     ) t
where seqnum = 1
group by hospital, reason;

Upvotes: 3

Related Questions