Comparing Numeric Range in SQL
Hello Experts,
I use the query below to determine the Sum of Minutes and Units for items that meet a specific criteria:
SELECT Sum(T.ContactMins) AS Minutes, Sum(T.Units) As Units
FROM tblTreatmentSummary T
LEFT JOIN LookupTreatments L ON L.Code = T.Treatment
WHERE L.Time_Based = 'Y' AND T.Encounter_Code = '168'
This returns numbers such as 43 minutes and 3 units. Now I need to compare the minutes to a range set in another table LookupUnits where the structure is:
Low (int)
High (int)
Units (int)
If the minutes returned in the above query fall between the Low and High and units match the predefined units in the table then the query should return True else False.
Thanks for any help,
JackW9653
I use the query below to determine the Sum of Minutes and Units for items that meet a specific criteria:
SELECT Sum(T.ContactMins) AS Minutes, Sum(T.Units) As Units
FROM tblTreatmentSummary T
LEFT JOIN LookupTreatments L ON L.Code = T.Treatment
WHERE L.Time_Based = 'Y' AND T.Encounter_Code = '168'
This returns numbers such as 43 minutes and 3 units. Now I need to compare the minutes to a range set in another table LookupUnits where the structure is:
Low (int)
High (int)
Units (int)
If the minutes returned in the above query fall between the Low and High and units match the predefined units in the table then the query should return True else False.
Thanks for any help,
JackW9653
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent Solution appari !!
Thanks,
JackW9653
Thanks,
JackW9653
SELECT CASE (SELECT COUNT(*) FROM LookupUnits WHERE Units=@Units AND Low <= @Minutes AND High >= @Minutes) WHEN 0 THEN 'False' ELSE 'True' END