Picking the correct field value based on matching criteria range

I would like to write a SQL statement that obtains a field value from a table with effective date ranges based on the value of another data field

An example is:
DRG Table:
Diagnosis Code            Length of Stay         EffectiveDate
700                                    5.7                              10/1/2013
700                                    5.9                               10/1/2014

Admissions:

DRG                    Admission Date                   Length Of Stay
700                              8/31/14                                5.7
700                            11/1/14                                  5.9

In this example, the admission date 8/31/14 is between 10/1/13 and 9/30/14 so the Length of Stay would be 5.7.  While the other admission had a date after 10/1/14 and before 9/30/15, has a Length Of Stay of 5.9.

I do I write the code to evaluate this from the two tables:  DRG and Admissions?

Thanks

Glen
GPSPOWAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
one way:
SELECT
      A.*
    , OA.[LENGTH OF STAY]
FROM admissions AS A
OUTER APPLY (
            SELECT TOP (1)
                  [LENGTH OF STAY]
            FROM [DRG Table] AS D
            WHERE A.[Admission Date] >= D.EffectiveDate
                  AND A.[Admission Date] < DATEADD(YEAR, 1, D.EffectiveDate)
            ORDER BY D.EffectiveDate DESC
      ) AS OA ([LENGTH OF STAY])
;

Open in new window


note outer apply use in case there is no match to [DRG Table]
0
GPSPOWAuthor Commented:
Here is my SQL query.


SELECT     AV.RoomLocation, AV.RoomLocationName, BV.AccountNumber, BV.Name, DATEDIFF(year, BV.BirthDateTime, GETDATE()) AS Age, AV.RoomID, AV.ReasonForVisit, 
                      SUBSTRING(BV.AdmitSourceName, 3, 25) AS SourceName, CONVERT(char, CASE WHEN BV.AdmitDateTime IS NULL 
                      THEN BV.ServiceDateTime ELSE BV.AdmitDateTime END, 101) AS AdmDt, BV.InpatientOrOutpatient AS Status, CASE WHEN ADG.WithAllProcedures IS NULL 
                      THEN '' ELSE ADG.WithAllProcedures END AS DRG, CASE WHEN ADG.WithAllProcedureName IS NULL THEN '' ELSE ADG.WithAllProcedureName END AS DRGName, 
                      CASE WHEN ADG.GeometricLengthOfStay IS NULL THEN 0 ELSE CONVERT(numeric(6, 2), ADG.GeometricLengthOfStay) END AS GLOS, 
                      CASE WHEN DAFC.StdLengthOfStayArithmetic IS NULL THEN 0 ELSE CONVERT(numeric(6, 2), DAFC.StdLengthOfStayArithmetic) END AS ALOS, 
                      BV.FinancialClassName, DMI.Name AS Insurance, NQ.Response AS LaceScore, CASE WHEN BV.AdmitDateTime IS NULL THEN 1 ELSE DATEDIFF(day, 
                      BV.AdmitDateTime, GETDATE() - 1) END AS LOS, CONVERT(numeric(10, 2), BVFD.Balance) AS Bal, CONVERT(numeric(10, 2), CASE WHEN BVFD.ReceiptTotal IS NULL 
                      THEN 0 ELSE BVFD.ReceiptTotal END) AS Recpts, DMP.Name AS Physician, NQ.DateTime
FROM         dbo.DAbsFinancialClasses AS DAFC RIGHT OUTER JOIN
                      dbo.AdmVisits AS AV RIGHT OUTER JOIN
                      dbo.NurQueryResults AS NQ RIGHT OUTER JOIN
                      dbo.AdmProviders AS AP RIGHT OUTER JOIN
                      dbo.AbsDrgData AS ADG RIGHT OUTER JOIN
                      dbo.DMisInsurance AS DMI RIGHT OUTER JOIN
                      dbo.BarVisits AS BV LEFT OUTER JOIN
                      dbo.BarVisitFinancialData AS BVFD ON BV.VisitID = BVFD.VisitID ON DMI.InsuranceID = BV.PrimaryInsuranceID ON ADG.VisitID = BV.VisitID ON 
                      AP.VisitID = BV.VisitID ON NQ.VisitID = BV.VisitID ON AV.VisitID = BV.VisitID LEFT OUTER JOIN
                      dbo.DMisProvider AS DMP ON AP.AdmitID = DMP.ProviderID ON DAFC.CodeID = ADG.WithAllProcedures
WHERE     (BV.AdmitDateTime IS NOT NULL) AND CASE WHEN BV.AdmitDateTime IS NULL 
                      THEN BV.ServiceDateTime ELSE BV.AdmitDateTime END > '2015-02-20' AND (NQ.QueryID = 'CM.LACETOT') AND (DAFC.FinancialClassID = 'MCR') AND 
                      (DAFC.EffectiveDateTime > CONVERT(DATETIME, '2014-08-31 00:00:00', 102)) AND (NQ.Response > '5') OR
                      (BV.AdmitDateTime IS NULL) AND (NQ.QueryID = 'CM.LACETOT') AND (BV.ServiceDateTime > '2015-02-20')

Open in new window


The statement below is what I came up with to substitute for your answer above.

outer apply ( select top (1) 
CASE WHEN dbo.DAbsFinancialClasses.StdLengthOfStayArithmetic IS NULL THEN 0 ELSE CONVERT(numeric(6, 2), dbo.DAbsFinancialClasses.StdLengthOfStayArithmetic) END
from dbo.DAbsFinancialClasses as D
where  CONVERT(char,CASE WHEN dbo.BarVisits.AdmitDateTime IS NULL THEN dbo.BarVisits.ServiceDateTime ELSE dbo.BarVisits.AdmitDateTime END, 101) > D.EffectiveDateTime and 
CONVERT(char,CASE WHEN dbo.BarVisits.AdmitDateTime IS NULL THEN dbo.BarVisits.ServiceDateTime ELSE dbo.BarVisits.AdmitDateTime END, 101) <= dateadd(Year,1,D.EffectiveDateTime)
order by D.EffectiveDateTime) as OA (ALOS)

Open in new window



Do I substitute this for:

CASE WHEN DAFC.StdLengthOfStayArithmetic IS NULL THEN 0 ELSE CONVERT(numeric(6, 2), DAFC.StdLengthOfStayArithmetic) END AS ALOS

Thanks

Glen
0
PortletPaulEE Topic AdvisorCommented:
When comparing  date/time data to date/time data do NOT convert either side of the comparison to a string

WHERE CONVERT( char, CASE
      WHEN dbo.BarVisits.AdmitDateTime IS NULL THEN dbo.BarVisits.ServiceDateTime
      ELSE dbo.BarVisits.AdmitDateTime
END, 101 ) > D.EffectiveDateTime
AND CONVERT( char, CASE
      WHEN dbo.BarVisits.AdmitDateTime IS NULL THEN dbo.BarVisits.ServiceDateTime
      ELSE dbo.BarVisits.AdmitDateTime
END, 101 ) <= DATEADD( YEAR, 1, D.EffectiveDateTime )

Those converts simple aren't required and slow down the query.
----------------------------------------------------------------------------------------------

Personally I would do it much like I suggested in my original answer. All you require from the outer apply is that piece of data. When you come to use that data in the main select clause us ISNULL()
e.g.
SELECT
      ISNULL(ALOS,0)
FROM ...
joins ...
OUTER APPLY (
      SELECT TOP (1)
            CONVERT( numeric(6, 2), dbo.DAbsFinancialClasses.StdLengthOfStayArithmetic )
      FROM dbo.DAbsFinancialClasses AS D
      WHERE CASE
                  WHEN dbo.BarVisits.AdmitDateTime IS NULL THEN dbo.BarVisits.ServiceDateTime
                  ELSE dbo.BarVisits.AdmitDateTime
            END > D.EffectiveDateTime
      AND CASE
                  WHEN dbo.BarVisits.AdmitDateTime IS NULL THEN dbo.BarVisits.ServiceDateTime
                  ELSE dbo.BarVisits.AdmitDateTime
          END <= DATEADD( YEAR, 1, D.EffectiveDateTime )
      ORDER BY D.EffectiveDateTime
      ) AS OA (ALOS)

Open in new window


Please also note in my original I suggested use of DESC(ending) order, which would help ensure you got the latest applicable date range just in case there did happen to be more than one. I leave that decision to you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
Thank you
Worked great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.