How do you create a dynamic date out of a hardcoded date? Can someone please help me identify what is missing?

Question

Hardcoded date is: 12-31-2023. I am trying to make this date dynamic so that it runs for the current year starting with 12-31-2023.

These are the notes I have on this:

-- Look at the previous month, and get the last day of year before that

-- Get the previous month, take the year and month. Find the last day of year from year before that

-- So let's say it's January 2024, the previous month is December 2023

-- The last day of year is December 31, 2023. We want one year before that

-- So our result should be December 31, 2022.

-- Let's say it's February 2024. the previous month is January 2024

-- The last day of the year is December 31, 2024. We want one year before that

-- So our result should be December 31, 2023.

This is what I've gotten so far but something is missing because it is not pulling the correct number of lines.

EventFromDt > DATEADD(DD, -1, DATEADD(YY,DATEDIFF(YY,0,DATEADD(YEAR,0,DATEADD(MM, -1,DATEADD(MM, -1,EOMONTH(GETDATE()))))),0))

Entire Query:

/****** Script for SelectTopNRows command from SSMS ******/

SELECT distinct [EventId]

,[EventFromDt] as 'Outreach From Date'

,[EventCloseDt] as 'Outreach Close Date'

,[EventSummaryTxt] as 'Outreach Program'

,[IssuerMemberId]

,[BirthDt] as 'Birth Date'

,[FirstNameTxt] as 'First Name'

,[LastNameTxt] as 'Last Name'

,[GenderTxt] as 'Gender'

,[AffiliationGroupCd] 'Master Group Number'

,[AffiliationGroupTxt] 'Master Group Name'

,cc.[GroupId]

,cc.[GroupLevel1Id]

,gp.GroupLevel1NameTxt as 'Group Name'

,[EventReviewTypeTxt] as 'Case Management Condition'

,[DiagnosisCd1]

,[DiagnosisTxt1]

,[DiagnosisCd2]

,[DiagnosisTxt2]

,[DiagnosisCd3]

,[DiagnosisTxt3]

FROM [edw].[vwMedicalManagementCaseMgmtandOutreachSummary] cc

left join edw.DimGroup gp

on gp.groupid = cc.groupid

and gp.GroupLevel1Id = cc.GroupLevel1Id

and gp.GroupLevel2Id = cc.GroupLevel2Id

and gp.GroupLevel3Id = cc.GroupLevel3Id

where AffiliationGroupCd = '10838'

and EventFromDt > '2023-12-31' --this will need updated to '2023-12-31' after 2/1/24

or EventCloseDt = NULL

and validind = 'Y'

Answer 1

Mallorie -

Lets take a look at what dates are output by each function in your solution step by step starting from the inner most function.

4/30/24

Go back 1 month to 3/30/24

Go back 1 month to 2/29/24

Add 0 years to 2/29/24

Get the year (2024)

Go to the first day of the year, 1/1/24

Go back one day to get to prior year, 12/31/23

With no changes, this would still be accurate 11 months out of the year

What will happen during the month of February:

2/1/24

Go back 1 month to 1/1/24

Go back 1 month to 12/1/23

Add 0 years to 12/1/23

Get the year (2023)

Go to the first day of the year, 1/1/23

Go back one day to get to prior year, 12/31/22

With the dates laid out this way, I hope you're able to see what functions can be removed as unnecessary as well as how you'd solve for dates in February.

I would also encourage you to review your where clause, particularly ensuring you understand the difference between "x = null" and "x is null", and the benefits of using parenthesis to help contain your logical operators and their precedence.

Hope this helps.