Can anyone help me update a query to include open cases, as well as cases opened in the current year (it currently only pulls cases opened in the current year) and make the date dynamic?

Question

Beginner here...

This is what I'm working with.

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 > '2022-12-31' --this will need updated to '2023-12-31' after 2/1/24

and validind = 'Y'

Answer 1

If you want to make the date dynamic, you might consider creating a stored procedure that take the date as a parameter. How are you invoking the query? Are you calling it through an application or just from a database query tool?