SSRS create Age range

Question

I'm trying to make a report that will display how many patients came in during a specific time frame for an age range. This is what I got so far, but the numbers its outputting are wrong, so I'm not sure what I missed. I've followed a couple of examples on here, but none have worked so far. Not sure if its cause I'm Joining to a different table or what.

select COUNT (DISTINCT MPFILE.PATIENT_NO) as 'Male 0-4'
from ENHFILE 
Join MPFILE
on MPFILE.PATIENT_NO = ENHFILE.PATIENT_NO
where ENHFILE.COSITE = '300'
and ENHFILE.VISIT_PURPOSE = '2'
and MPFILE.SEX = 'M'
and (DATEDIFF(hour,MPFILE.DOB,GETDATE())/8766) > 5
and ENHFILE.ENCOUNTER_DATE between (@StartDate) and (@EndDate)



select COUNT (DISTINCT MPFILE.PATIENT_NO) as 'FeMale 0-4'
from ENHFILE 
Join MPFILE
on MPFILE.PATIENT_NO = ENHFILE.PATIENT_NO
where ENHFILE.COSITE = '300'
and ENHFILE.VISIT_PURPOSE = '2'
and MPFILE.SEX = 'F'
and (DATEDIFF(hour,MPFILE.DOB,GETDATE())/8766) > 5
and ENHFILE.ENCOUNTER_DATE between (@StartDate) and (@EndDate)

what range are you wanting to look for? people that are between the ages of ### and ###? — S3S, Aug 16 '16 at 19:34
im looking for 0-4, 5-9, 10-17, 18+, i think my mistake was i had the > facing the wrong direction in the example...... I seem to be getting the correct numbers now. That feel... — mol, Aug 16 '16 at 20:34
8760 is the hours in a year and 8784 is for leap year. Where did 8766 come from? Will your code account for leap year? — S3S, Aug 16 '16 at 20:38
It was in the example i used, but I'm guessing since leap year is an extra 24 hours, 6 x 4, so that it would account for leap year. — mol, Aug 17 '16 at 12:59

S3S · Answer 1 · 2016-08-17T14:28:12.987

Here is something that should get you what you want.

--First i just created some test data
if object_id('tempdb..#temp') is not null drop table #temp

select '8/15/1995' as DOB into #temp union all   --Note this person is 21
select '8/16/1995' union all                     --Note this person is 21 TODAY
select '8/17/1995' union all                     --Note this person is 21 TOMORROW
select '4/11/1996' union all
select '5/15/1997' union all
select '9/7/2001' 

--set the years old you want to use here. Create another variable if you need to use ranges
declare @yearsOld int
set @yearsOld = 21

select
    convert(date,DOB) as DOB,

    --This figures out how old they are by seeing if they have had a birthday 
    --this year and calculating accordingly. It is what is used in the filter
    --I only put it here so you can see the results
    CASE 
        WHEN CONVERT(DATE, CONVERT(VARCHAR(4), YEAR(GetDate()))+ '-'+ CONVERT(VARCHAR(2),MONTH(DOB)) + '-' + CONVERT(VARCHAR(2),DAY(DOB))) <= GETDATE() THEN DATEDIFF(yy,DOB,GETDATE())
        ELSE DATEDIFF(yy,DOB,GETDATE()) -1
    END AS YearsOld
from #temp
where
    --here is your filter. Feel free to change the >= to what ever you want, or combine it to make it a range.
    CASE 
        WHEN CONVERT(DATE, CONVERT(VARCHAR(4), YEAR(GetDate()))+ '-'+ CONVERT(VARCHAR(2),MONTH(DOB)) + '-' + CONVERT(VARCHAR(2),DAY(DOB))) <= GETDATE() THEN DATEDIFF(yy,DOB,GETDATE())
        ELSE DATEDIFF(yy,DOB,GETDATE()) -1
    END >= @yearsOld

EDIT

This is your method which doesn't account for if they have had a birthday this year. I use some test data. Notice the person born on 8/18/1995. They turn 21 tomorrow but using (DATEDIFF(hour,DOB,GETDATE())/8766) >= @yearsOld includes them when it shouldn't...

--First i just created some test data
if object_id('tempdb..#temp') is not null drop table #temp

select '8/15/1995' as DOB into #temp union all   --Note this person is 21
select '8/16/1995' union all                     --Note this person is 21 TODAY
select '8/18/1995' union all                     --Note this person is 21 TOMORROW
select '4/11/1996' union all
select '5/15/1997' union all
select '9/7/2001' 

--set the years old you want to use here. Create another variable if you need to use ranges
declare @yearsOld int
set @yearsOld = 21

select
    convert(date,DOB) as DOB,

    --This figures out how old they are by seeing if they have had a birthday 
    --this year and calculating accordingly. It is what is used in the filter
    --I only put it here so you can see the results
    CASE 
        WHEN CONVERT(DATE, CONVERT(VARCHAR(4), YEAR(GetDate()))+ '-'+ CONVERT(VARCHAR(2),MONTH(DOB)) + '-' + CONVERT(VARCHAR(2),DAY(DOB))) <= GETDATE() THEN DATEDIFF(yy,DOB,GETDATE())
        ELSE DATEDIFF(yy,DOB,GETDATE()) -1
    END AS YearsOld
from #temp
where
    --here is your filter. Feel free to change the >= to what ever you want, or combine it to make it a range.
    (DATEDIFF(hour,DOB,GETDATE())/8766) >= @yearsOld

RESULTS

DOB        | YearsOld
1995-08-15 | 21
1995-08-16 | 21
1995-08-18 | 20         --this shouldn't be here...

SSRS create Age range

1 Answers1