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:
Diagnosis Code Length of Stay EffectiveDate
700 5.7 10/1/2013
700 5.9 10/1/2014
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?