• Status: Solved
• Priority: Medium
• Security: Public
• Views: 817

# 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
0
JackW9653
1 Solution

Commented:
Select
case when low is not null and calc.Units = LookupUnits.Units then 'true' else 'false' end
from (
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') calc
left outer join  LookupUnits
on Minutes between  low and high

0

Commented:
Try:

SELECT CASE (SELECT COUNT(*) FROM LookupUnits WHERE Units=@Units AND Low <= @Minutes AND High >= @Minutes) WHEN 0 THEN 'False' ELSE 'True' END
0

Author Commented:
Excellent Solution appari !!

Thanks,

JackW9653
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.