Settng Date parameters to update automatically

[LMSSAS]

My below code has a variable in it (CPC.CHNL_PTNR_CASE_BGN_DT AS CaseStartDate) that is a format=date9. I want to automate this report and have the date update automatically every month. Example of what I need is: For Dec report a CaseStartDate 20Dec2021 - 20Jan2022 and for Jan report 21Jan2022 - 20Feb2022, etc etc... Can someone provide an example of how this will look in my code? Thank you!

proc sql; 
create table CPIM_Automation as 
SELECT DISTINCT
CPC.CHNL_PTNR_CASE_ID AS TrackingRecord
,CPT2.CHNL_PTNR_CASE_TYP_DE AS WorkType
,CPT1.CHNL_PTNR_CASE_TYP_DE AS SubType
,CPC.CHNL_PTNR_CASE_STAT_CD AS CaseStatusCode
,CPCS.SRC_SYS_CD AS SourceCode
,CPC.CHNL_PTNR_CASE_BGN_DT AS CaseStartDate
,AGET.CHNL_PTNR_CASE_SBJ_VAL_ID AS AgentType
,AGEY.CHNL_PTNR_CASE_SBJ_VAL_ID AS AgencyType
,CPCS.CHNL_PTNR_CASE_STEP_NB AS StepNumber

FROM 
CPM1P1.CHNL_PTNR_CASE_STEP CPCS
LEFT JOIN CPM1P1.CHNL_PTNR_CASE CPC ON CPCS.CHNL_PTNR_CASE_ID = CPC.CHNL_PTNR_CASE_ID
LEFT JOIN CPM1P1.CHNL_PTNR_CASE_TYP CPT1 ON CPC.CHNL_PTNR_CASE_TYP_CD = CPT1.CHNL_PTNR_CASE_TYP_CD
LEFT JOIN CPM1P1.CHNL_PTNR_CASE_TYP CPT2 ON CPT1.PRNT_CHNL_PTNR_CASE_TYP_CD = CPT2.CHNL_PTNR_CASE_TYP_CD 

       
LEFT JOIN (SELECT DISTINCT
          SUB2.CHNL_PTNR_CASE_ID, SUB2.CHNL_PTNR_CASE_SBJ_VAL_ID
          FROM CPM1P1.CHNL_PTNR_CASE_SBJ SUB2
          WHERE SUB2.CHNL_PTNR_CASE_SBJ_TYP_CD = 'CONTACTAGENTTYPE') AGET
          ON CPCS.CHNL_PTNR_CASE_ID = AGET.CHNL_PTNR_CASE_ID 

LEFT JOIN (SELECT DISTINCT
          SUB3.CHNL_PTNR_CASE_ID, SUB3.CHNL_PTNR_CASE_SBJ_VAL_ID
          FROM CPM1P1.CHNL_PTNR_CASE_SBJ SUB3
          WHERE SUB3.CHNL_PTNR_CASE_SBJ_TYP_CD = 'AGENCYTYPE') AGEY
          ON CPCS.CHNL_PTNR_CASE_ID = AGEY.CHNL_PTNR_CASE_ID
WHERE 
/*CaseStartDate ge intnx (month, today(), -1,'b') and CaseStartDate le intnx ('month',today(),-1,'e')*/
CaseStartDate between '20Oct2021'd and '20Nov2021'd
and SourceCode = 'SSPortal'
and StepNumber = 1 

ORDER BY CPC.CHNL_PTNR_CASE_ID ASC
;
quit;

[PaigeMiller]

Please clarify this:

For Dec report a CaseStartDate 20Dec2021 - 20Jan2022 and for Jan report 21Jan2022 - 20Feb2022, etc etc... Can someone provide an example of how this will look in my code? Thank you!

So for December 2021, you want to analyze case start dates in parts of December 2021 and January 2022? And why sometimes the range goes from the 20th of a month to the 20th of the next month, and other times the range goes from the 21st of one month to the 20th of the next month?

How does the program know what date range to use, if say, the program is run on a specific date? When TODAY() returns December 29, 2021, what date interval should be used? What date interval in general?

[LMSSAS]

It would be from the 20th of month through the 20th of the next month. So CaseStartDate 20Dec2021 - 20Jan2022 and for Jan report 20Jan2022 - 20Feb2022, etc etc...

If Today () returns December 29, 2021 then it would be Today (),-9

[PaigeMiller]

---

@LMSSAS wrote:
It would be from the 20th of month through the 20th of the next month. So CaseStartDate 20Dec2021 - 20Jan2022 and for Jan report 20Jan2022 - 20Feb2022, etc etc...

If Today () returns December 29, 2021 then it would be Today (),-9

---

Well since you seem to have an answer, I won't bother providing another one, but I believe your problem description (or planned analysis) is still flawed, as the 20th of the month will appear in two intervals, and the rest of the days in the month appear in one interval.

And you have Case Start Date values in January 2022, when that hasn't happened yet?

[LMSSAS]

Thank you for your response, you caused me to think. And yes, my logic is flawed. My concern is that different months have a different number of days, so how will the program know how many days from today(), will get me to the 20th of the month. This may be what you are explaining with the 20th of the month appearing in two intervals and the rest of the days in the month appear in one interval..

Regarding the Case Start Date values in January 2022, that date will have passed by the time this runs for the Dec - Jan report. So I will run the 20Dec2021 - 20Jan2022 on Jan 21st. The Jan - Feb will run on Feb 21st.

So Starting on Jan 21st it will run to include the previous days.

[LMSSAS]

Thank you for your response, you caused me to think. And yes, my logic is flawed. My concern is that different months have a different number of days, so how will the program know how many days from today(), will get me to the 20th of the month. This may be what you are explaining with the 20th of the month appearing in two intervals and the rest of the days in the month appear in one interval, yes?

Regarding the Case Start Date values in January 2022, that date will have passed by the time this runs for the Dec - Jan report. So I will run the 20Dec2021 – 20Jan2022 on Jan 21st. The Jan – Feb will run on Feb 21st.
So Starting on Jan 21st it will run to include the previous days.

[Reeza]

---

@LMSSAS wrote:
Thank you for your response, you caused me to think. And yes, my logic is flawed. My concern is that different months have a different number of days, so how will the program know how many days from today(), will get me to the 20th of the month. This may be what you are explaining with the 20th of the month appearing in two intervals and the rest of the days in the month appear in one interval, yes?

The fourth parameter in the INTNX function causes the date to align to the beginning of the month and then adding 20 guarantee's it's always the 20th date. 

I think @PaigeMiller concern is that you're reporting 20th June to 20th July and then 20th of July to 20th of August. This means that the 20th of July is included in both data extracts. I suspect you actually want not overlapping periods instead? 

Second issue is when you're running. If you plan to run this daily, what happens if you run it the first 20 days of the month? How you plan to operationalize this matters.

I'd also recommend modifying this to include the dates in the query somehow so each extract was uniquely identified.

[LMSSAS]

Yes, that is correct. I do NOT want overlapping periods? Can I make this adjustment in the INTNX Function or will it require additional logic?
I do not plan to run this daily, it will be a monthly report. it will begin on the 21st of January and will include the first 20 days of the current month, in this case January and I will pull (December 20-31 - Jan 1-20). The rest of the schedule will look like the list below: Do you see an issue with this logic? I do not, I think this will work.
Dec 20, 2021 - Jan 20, 2022
Jan 21, 2022 - Feb 20, 2022
Feb 21, 2022 - March 20, 2022
March 21, 2022 - April 20, 2022
April 21, 2022 - May 20, 2022
May 21, 2022 - June 20, 2022
June 21, 2022 - July 20, 2022
July 21, 2022 - August 20, 2022
Aug 21, 2022 - Sept 20, 2022
Sept 21, 2022 - Oct 20, 2022
Oct 21, 2022 - Nov 20, 2022
Nov 21, 2022 - Dec 20, 2022

[Reeza · Accepted solution]

Why does the interval change from 20 to 20 in your first one to 21 to 20 in the second one?

20Dec2021 - 20Jan2022 and for Jan report 21Jan2022 - 20Feb2022

Assuming that's a typo of some kind AND that you're running this after the 20th of the reporting month and before the end of the reporting month something like this may work:

CaseStartDate between (intnx('month', today(), -1, 'b')+20) and (intnx('month', today(), 0, 'b')+20)

---

@LMSSAS wrote:

My below code has a variable in it (CPC.CHNL_PTNR_CASE_BGN_DT AS CaseStartDate) that is a format=date9. I want to automate this report and have the date update automatically every month. Example of what I need is: For Dec report a CaseStartDate 20Dec2021 - 20Jan2022 and for Jan report 21Jan2022 - 20Feb2022, etc etc... Can someone provide an example of how this will look in my code? Thank you!

proc sql; 
create table CPIM_Automation as 
SELECT DISTINCT
CPC.CHNL_PTNR_CASE_ID AS TrackingRecord
,CPT2.CHNL_PTNR_CASE_TYP_DE AS WorkType
,CPT1.CHNL_PTNR_CASE_TYP_DE AS SubType
,CPC.CHNL_PTNR_CASE_STAT_CD AS CaseStatusCode
,CPCS.SRC_SYS_CD AS SourceCode
,CPC.CHNL_PTNR_CASE_BGN_DT AS CaseStartDate
,AGET.CHNL_PTNR_CASE_SBJ_VAL_ID AS AgentType
,AGEY.CHNL_PTNR_CASE_SBJ_VAL_ID AS AgencyType
,CPCS.CHNL_PTNR_CASE_STEP_NB AS StepNumber

FROM 
CPM1P1.CHNL_PTNR_CASE_STEP CPCS
LEFT JOIN CPM1P1.CHNL_PTNR_CASE CPC ON CPCS.CHNL_PTNR_CASE_ID = CPC.CHNL_PTNR_CASE_ID
LEFT JOIN CPM1P1.CHNL_PTNR_CASE_TYP CPT1 ON CPC.CHNL_PTNR_CASE_TYP_CD = CPT1.CHNL_PTNR_CASE_TYP_CD
LEFT JOIN CPM1P1.CHNL_PTNR_CASE_TYP CPT2 ON CPT1.PRNT_CHNL_PTNR_CASE_TYP_CD = CPT2.CHNL_PTNR_CASE_TYP_CD 

       
LEFT JOIN (SELECT DISTINCT
          SUB2.CHNL_PTNR_CASE_ID, SUB2.CHNL_PTNR_CASE_SBJ_VAL_ID
          FROM CPM1P1.CHNL_PTNR_CASE_SBJ SUB2
          WHERE SUB2.CHNL_PTNR_CASE_SBJ_TYP_CD = 'CONTACTAGENTTYPE') AGET
          ON CPCS.CHNL_PTNR_CASE_ID = AGET.CHNL_PTNR_CASE_ID 

LEFT JOIN (SELECT DISTINCT
          SUB3.CHNL_PTNR_CASE_ID, SUB3.CHNL_PTNR_CASE_SBJ_VAL_ID
          FROM CPM1P1.CHNL_PTNR_CASE_SBJ SUB3
          WHERE SUB3.CHNL_PTNR_CASE_SBJ_TYP_CD = 'AGENCYTYPE') AGEY
          ON CPCS.CHNL_PTNR_CASE_ID = AGEY.CHNL_PTNR_CASE_ID
WHERE 
/*CaseStartDate ge intnx (month, today(), -1,'b') and CaseStartDate le intnx ('month',today(),-1,'e')*/
CaseStartDate between '20Oct2021'd and '20Nov2021'd
and SourceCode = 'SSPortal'
and StepNumber = 1 

ORDER BY CPC.CHNL_PTNR_CASE_ID ASC
;
quit;

---

[LMSSAS]

Thanks so much, that worked!!!

I appreciate it!!