Intersecting/overlapping dates
-
Hey gang,
I need to do a monthly rollup query that will give me a total number of patients for whom three date-dependent factors were true at the same time. Any help structuring this will be much welcome. Here we go. The key factors are...
- Patients at this facility can be either "active" or "discharged" -- and can go back and forth from one state to the other at any time.
- Patients are assigned to nurses, and those nurses come in two types -- call them Type A and Type B. Patients can be reassigned from a Type A nurse to a Type B nurse, or vice versa, at any time. (Reassignments can also happen between two Type A nurses, or two Type B nurses, as well.)
- Patients can be either "off-record" or "on-record." Unlike the above, this is a one-way transition: Once on-record, a patient never goes back off-record. All patients are initially off-record and most go on-record (but not all).
These three things can happen in any combination in the course of a month. For some patients none of them will happen (the previous month's states just carry forward thru the reporting month). For some, all three will change. For some only one or two will change. Items 1 and 2 above might change more than once in a month.
My challenge... Get the SUM of patients who -- at some moment during the month -- had these three states simultaneously:
- Active
- Managed by a Type A nurse
- On-record
These three state must all be true at any moment during the month. That moment could have lasted three milliseconds or all month long -- but if such a moment occured, I want to include that patient in my SUM.
My history logs have date-stamps for all the key bits of info...
- The datetime for each nurse-assignment change, and the IDs for the old and new nurses (a simple link to the personnel table tells me what Type each nurse is)
- The datetime for each active/discharge transition
- The datetime that this patient went from off-record to on-record.
Thanks in advance. Any help will be rewarded handsomely! (With admiration and praise. Void where prohibited.)
- Tom
-
Difficult without table defs but this may get you going but will need adjusting.
SELECT p.PatientID,
rl.date
INTO #patient
FROM Patient p
INNER JOIN RecordLog rl
ON rl.PatientID = p.PatientID
AND [on-record]
SELECT p.PatientID,
al.date as 'StartDate',
(SELECT ISNULL(MIN(al2.date),GETDATE())
FROM ActiveLog al2
WHERE al2.PatientID = p.PatientID
AND al2.date > al.date) AS 'EndDate'
INTO #active
FROM #patient p
INNER JOIN ActiveLog al
ON al.PatientID = p.PatientID
AND al.date >= p.date
AND [active]
SELECT p.PatientID,
nl.NurseID,
nl.date as 'StartDate',
(SELECT ISNULL(MIN(nl2.date),GETDATE())
FROM NurseLog nl2
WHERE nl2.PatientID = p.PatientID
AND nl2.date > nl.date) AS 'EndDate'
INTO #nurse
FROM #patient p
INNER JOIN NurseLog nl
ON nl.PatientID = p.PatientID
AND nl.date >= p.date
INNER JOIN personnel pe
ON pe.NurseID = nl.NurseID
AND [NurseType] = 'B'
SELECT p.PatientID,
(CASE WHEN a.startdate < n.startdate THEN n.startdate ELSE a.startdate END) AS 'startdate',
(CASE WHEN a.enddate > n.enddate THEN n.enddate ELSE a.enddate END) AS 'enddate'
INTO #result
FROM #patient p
INNER JOIN #active a
ON a.PatientID = p.PatientID
INNER JOIN #nurse n
ON n.PatientID = a.PatientID
AND n.startdate <= a.enddate
AND n.enddate >= a.startdate
SELECT c.Year,
c.Month,
COUNT(*) as 'Num'
FROM [calendar] c
INNER JOIN #result r
ON r.Year >= YEAR(r.startdate)
AND r.Year <= YEAR(r.enddate)
AND r.Month >= MONTH(r.enddate)
AND r.Month <= MONTH(r.enddate)
GROUP BY c.Year,c.Month
Far away is close at hand in the images of elsewhere.
Anon. -
David,
Wow. Thank you! That was very generous of you, especially considering I didn't provide table-defs. You put a lot of work and thought into that, and I am already making use of some of the elements in it. I very much appreciate it.
One little errata that I should point out in my first note... The places where I mentioned getting SUMs -- as in "Get the SUM of patients who..." -- those should of course say "COUNT" and not SUM.
D'oh.
- Tom
-
You're welcome, hope it is of some use.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply