Certain Age at The End of the Month

SQL Server Administration
1

Hello,

I am constructing query modification that will pick up participants in my table that are 65 years of age at the end of the month in which I am running the report. So whether the month be 28 days long or 30 for example, it would still be able to report the results.

I have already been able to make it produce the listing of the people 65 years old, but this modification would enable me to run it at the end of each month repeatedly and not have to wait until the last day.

Microsoft SQL Server 2012

Thank you in advance.

2

Something like this:

	DateOfBirth,
	OtherInfoFromTheTableYouNeed,
	CAST(CONVERT(CHAR(8),DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,-1), 112) AS INT)
	-  CAST(CONVERT(CHAR(8), DateOfBirth, 112) AS INT) AS AgeAtEndOfThisMonth
FROM
	YourTableWithDateOfBirthColumn


If you want to get the age at the end of last month, add another another column like shown below:

    SELECT
    	DateOfBirth,
    	OtherInfoFromTheTableYouNeed,
    	CAST(CONVERT(CHAR(8),DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,-1), 112) AS INT)
    	-  CAST(CONVERT(CHAR(8), DateOfBirth, 112) AS INT) AS AgeAtEndOfThisMonth,
    	CAST(CONVERT(CHAR(8),DATEADD(mm,DATEDIFF(mm,0,GETDATE()),-1), 112) AS INT)
    	-  CAST(CONVERT(CHAR(8), DateOfBirth, 112) AS INT) AS AgeAtEndOfLastMonth
    FROM
    	YourTableWithDateOfBirthColumn
1 Like
3

Hi James,

Thank you for responding so quickly.

I am getting closer, but I am unsure what I need to update. It is now showing what the last day of the current and last month are, but it is not showing the age as in years at the end of each month. It is showing the Participant's date of birth. Goal is for it to show all the Participants that are going to be 65 for greater at the end of the month and the age should be in years.

here is my current code and output.

SELECT
a.GroupID AS 'Group ID'
, a.DepNo
, a.PartID AS 'Participant ID'
, a.CovCat AS 'Coverage Category'
, CONVERT(varchar(10), ToDate, 101) as 'Coverage End'
, CAST(CONVERT(varchar(8),DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,-1), 112) AS INT)
, CAST(CONVERT(varchar(8), b.ParticipantDOB, 112) AS INT) AS 'Age At End Of Current Month'
, CAST(CONVERT(varchar(8),DATEADD(mm,DATEDIFF(mm,0,GETDATE()),-1), 112) AS INT)
, CAST(CONVERT(varchar(8), b.ParticipantDOB, 112) AS INT) AS 'Age At End Of Previous Month'

FROM tblMemEnroll AS a

left join vwMember AS b on a.PartID = b.ParticipantID and a.GroupID = b.GroupID and a.DepNo = b.DepNo

WHERE a.CovCat = 'GME'

and b.ParticipantDOB <= '19521231'

AND a.ToDate >= getdate()

AND a.DepNo = '00'

order by a.GroupID

output:

(No column name) Age At End Of Current Month (No column name) Age At End Of Previous Month
20171231 19461108 20171130 19461108
20171231 19420329 20171130 19420329
20171231 19460518 20171130 19460518
20171231 19510808 20171130 19510808
20171231 19500924 20171130 19500924

Thank you.

4

There are two minus signs you missed - see screenshot below

Meh! strike that. It doesn't let me upload an image. Look at the code I posted again, or copy and paste it to an SSMS window. You will see that you are missing minus signs at the beginning of two lines

5

WHERE b.ParticipantDOB >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 65 * 12, 0)

1 Like
6

Hi Scott,

Thank you for your reply.

I am getting results from your suggestion, but they are not limited to participants who will be 65 or older at the end of the month within the month that I am running the report in.

Here is the query and results:

SELECT
a.GroupID AS 'Group ID'
, a.DepNo AS 'Dep No'
, a.PartID AS 'Participant ID'
, a.CovCat AS 'Coverage Category'
, CONVERT(varchar(10), ToDate, 101) as 'Coverage End'
, b.ParticipantDOB AS 'Participant DOB'

FROM tblMemEnroll AS a
left join vwMember AS b on a.PartID = b.ParticipantID and a.GroupID = b.GroupID and a.DepNo = b.DepNo

WHERE a.CovCat = 'GME'
AND b.ParticipantDOB >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 65 * 12, 0)
AND a.ToDate >= getdate()
AND a.DepNo = '00'

ORDER BY a.GroupID

Here is a small excerpt from the results:
Dep No Coverage Category Coverage End Participant DOB
0 GME 1/1/9999 5/10/80
0 GME 1/1/9999 10/13/67
0 GME 1/1/9999 12/8/85
0 GME 1/1/9999 7/1/83
0 GME 1/1/9999 11/22/84

Thank you

7

Is the ParticipantDOB stored as a date/datetime data type? Because the date calc is correct, so unless there's an "OR" condition somewhere that lets other rows in, it must be because the data type is not date/datetime(?).

1 Like
8

Yes, the ParticipantDOB is stored as a date.

This is from my data dictionary:
Column type length precision scale
ParticipantDOB date 3 10 0

9

D'OH!! I feel like Homer Simpson. Swap the condition around from >= to <=

AND b.ParticipantDOB <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 65 * 12, 0)

1 Like
10

Hi Scott,

Thank you for your assistance! I am just learning SQL and you have been a great help to me. I ran the query by my manager and they stated that the code that I have to determine the DOB seems to be picking the 1st of the month rather than the last day of the month. I am supposed to be grabbing participants that will be 65 during the month of December now for example.

They suggested changing to the following, but I am not familiar with User Defined Functions:

dateadd(year,-65,(dateadd(month,2,dbo.udfPrevMoBeg())-1))

11

Yep, that's correct in general. Although rather than having to figure out the last day of a month, we should use < the first day of the next month:

AND b.ParticipantDOB < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 65 * 12 + 1, 0)

1 Like
12

As of 2012 we now have the EOMONTH function...

AND b.ParticipantDOB < DATEADD(day, 1, EOMONTH(getdate(), -65 * 12))
13

It's still best to use < the_next_day, as is standard best practice for date and/or datetime. Always use < rather than <=, which always works correctly even if the column data type changes.