[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2013-06-14
6
Medium Priority
?
255 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 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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 49

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

649 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