Solved

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

Posted on 2013-06-14
6
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

733 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