Solved

Query to test relevance of dates, then filter on nearest date.

Posted on 2013-06-14
6
240 Views
Last Modified: 2013-06-20
Hi Experts
Challenge: Populate the ClusterGroup by determining the patients CareCluster at the point of inpatient admittance.

Initial-Attempt.xls
Expected-Output.xls

The objective of the report is to measure inpatient admissions i.e. patients that stay in a hospital bed overnight, for each care cluster.

A care cluster is a needs-based grouping. A patient is assessed and assigned a care cluster based on severity.

Patient 10000003 has 3 Inpatient Admission (IPAdmissions)
Patient 10000003 was reassessed and downgraded from CareCluster P17 to CareCluster P12 on 13th March 2013 (ClusterAssessmentDate)

So, was Patient 10000003 in CareCluster P12 or P17 at point of inpatient admission?

InitialApproach retrieves the hour difference between ClusterAssessmentDate  and  IPAdmissionDate using:
DATEDIFF(HOUR, ClusterAssessmentDate, IPAdmissionDate)

Open in new window

My assumption is that the smallest number ABOVE zero will be the CareCluster that should be assigned to the ClusterGroup.

Question:

How do I write a query to return the smallest number ABOVE Zero and populate ClusterGroup with correct CareCluster?
Is there a more eloquent way of solving the smallest number above zero  challenge?
Any alternative solutions?
0
Comment
Question by:JohnAeris
  • 4
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39249390
>> Any alternative solutions?
not sure I fully follow the needed logic, but this alternative approach produces the expected results from the available sample data. I've used 2 row_number() calculations to align assessment sequence to admission sequence - but the sample data is of course quite limited. On this it appears that admission date/time is repeated and so is assessment date/time - so that would need to be true in the real tables too.
SELECT
  ClientID
, CareCluster
, ClusterAssessmentDate
, IPAdmissionDate
, ClusterGroup
, assess_ref
, admit_ref
FROM (
      SELECT
        ClientID
      , CareCluster
      , ClusterAssessmentDate
      , IPAdmissionDate
      , ClusterGroup
      , row_number() over (partition BY ClientID, ClusterAssessmentDate ORDER BY IPAdmissionDate ASC) AS assess_ref
      , row_number() over (partition BY ClientID, IPAdmissionDate ORDER BY ClusterAssessmentDate ASC) AS admit_ref
      FROM table1
      ) AS derived
WHERE admit_ref = assess_ref

Open in new window

see this working at: http://sqlfiddle.com/#!3/213295/5

>>How do I write a query to return the smallest number ABOVE Zero
>>Is there a more eloquent way of solving the smallest number above zero  challenge?
will comment on these next

>>and populate ClusterGroup with correct CareCluster?
only once the calculation is confirmed :)
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39249411
>>How do I write a query to return the smallest number ABOVE Zero
I would suggest (again) using row_number() for this as follows:
SELECT
  ClientID
, CareCluster
, ClusterAssessmentDate
, IPAdmissionDate
, ClusterGroup
, diff_ref
FROM (
      SELECT
        ClientID
      , CareCluster
      , ClusterAssessmentDate
      , IPAdmissionDate
      , ClusterGroup
      , row_number() over (PARTITION BY ClientID, IPAdmissionDate 
                           ORDER BY datediff(minute,ClusterAssessmentDate,IPAdmissionDate) ASC
                     ) AS diff_ref
      FROM table1
      WHERE ClusterAssessmentDate <= IPAdmissionDate
      ) AS derived
WHERE diff_ref = 1

Open in new window

is it more eloquent? (I don't know)
see this variant working at: http://sqlfiddle.com/#!3/cecae/1
This approach looks to be more robust that the alternative above, in that it specifically deals with the relevance of dates by testing if ClusterAssessmentDate <= IPAdmissionDate. But more testing would be needed to prove this.

Once the calculation method is known, then updating the table is requested. I'll propose an approach on this next. {+ edit for typo - sorry}
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39249464
the update approach would look like this:
update table1
set table1.ClusterGroup = upd.CareCluster
FROM table1
inner join  (
              SELECT
                ClientID
              , CareCluster
              , ClusterAssessmentDate
              , IPAdmissionDate
              , ClusterGroup
              , diff_ref
              FROM (
                    SELECT
                      ClientID
                    , CareCluster
                    , ClusterAssessmentDate
                    , IPAdmissionDate
                    , ClusterGroup
                    , row_number() over (PARTITION BY ClientID, IPAdmissionDate 
                                         ORDER BY datediff(minute,ClusterAssessmentDate,IPAdmissionDate) ASC
                                   ) AS diff_ref
                    FROM table1
                    WHERE ClusterAssessmentDate <= IPAdmissionDate
                    ) AS derived
              WHERE diff_ref = 1
            ) as upd ON table1.ClientID = upd.ClientID
                    AND table1.IPAdmissionDate = upd.IPAdmissionDate
;

select
*
from table1

Open in new window

depending on method of calculation adopted.
0
 

Author Closing Comment

by:JohnAeris
ID: 39252677
Spot on!
PortletPaul, this solves the challenge and resolves two future challenges as well!

Have used Windowed Functions in the past, but your solution has opened my eyes to how truly powerful the OVER Clause can be!

Keep up the good work! Thank you!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39252689
>> truly powerful the OVER Clause can be!
yep, ain't that the truth

thank you very much for the kind words, v.pleased I could help. Cheers, Paul
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now