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

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
Asked:
JackW9653
1 Solution
 
appariCommented:
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
 
TSmoothCommented:
Try:

SELECT CASE (SELECT COUNT(*) FROM LookupUnits WHERE Units=@Units AND Low <= @Minutes AND High >= @Minutes) WHEN 0 THEN 'False' ELSE 'True' END
0
 
JackW9653Author Commented:
Excellent Solution appari !!

Thanks,

JackW9653
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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