MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Monthend job -get previous month start and end dates

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9

PostPosted: Wed Aug 02, 2017 3:35 pm    Post subject: Monthend job -get previous month start and end dates Reply with quote

We have to run the below query on month end,

For July 2017 month end job would run on August 1st

Wv-YR = 2017 , Wv-Mo = 07

SAle_dt should retrieve records between '2017-06-01' and '2017-06-30' below logic works fine

For Sept 2017 monthend job would run on Sept 30 2017
Wv-YR = 2017 , Wv-Mo = 09

SAle_dt would retrieve records between '2017-07-01' and '2017-07-31'
actually, it has to be pickup records between

'2017-08-01' and '2017-08-31'

For Jan 2018 month end job would run on Feb 1st 2018
Wv-YR = 2018 , Wv-Mo = 01

SAle_dt should retrieve records between '2017-12-01' and '2017-12-31'


Code:


Select col1, col2
From table1                                       
WHERE  PR_YR = :Wv-YR                                                 
AND    PR_MO = :Wv-Mo     
AND   SALE_DT BETWEEN LAST_DAY(CURRENT DATE) + 1 DAY- 3 MONTH   
      AND LAST_DAY(CURRENT DATE - 2 MONTH)

Above logic doesn't help in case it runs for August month end in September.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Aug 02, 2017 3:53 pm    Post subject: Reply with quote

Suchay,

You have the right idea about month beginning date, however you need subtract the months first and then add 1 days. something like this

Code:

SELECT LAST_DAY(CURRENT DATE - 3 MONTH) + 1 DAY   
      ,LAST_DAY(CURRENT DATE - 2 MONTH)           
  FROM SYSIBM.SYSDUMMY1                           
  ;                                               


So august - 3 months = May date = lastday(maydate) = May 31 + 1 1 day = june 1st.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9

PostPosted: Wed Aug 02, 2017 4:03 pm    Post subject: Reply with quote

Kolusu
For Sept 2017 monthend job would run on Sept 30 2017
Wv-YR = 2017 , Wv-Mo = 09

SAle_dt would retrieve records between '2017-07-01' and '2017-07-31'
actually, it has to be pickup records between

'2017-08-01' and '2017-08-31'

Code:

                                                         
 SELECT LAST_DAY( DATE('2017-09-30') - 3 MONTH) + 1 DAY   
       ,LAST_DAY(DATE('2017-09-30')  - 2 MONTH)           
                                                         
   FROM SYSIBM.SYSDUMMY1                                 
                                                         
                                 
 COL1        COL2       
 ----------  ---------- 
 2017-07-01  2017-07-31                                                   




Above logic doesn't work for September month end, since the job would run on Sept 30 instead of October 1st 2017
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Aug 02, 2017 4:13 pm    Post subject: Reply with quote

Suchay,

You have an uncanny way of making the requirements complicated. You need to design generic solutions.

Basically your query can run on any day, but it needs to get the current_month -2 months start and begin dates?

but if runs on the month end then you need to get the current_month -1 months start and begin dates?

Is that it?

What happens when you run this job in the middle of the month? or for some reason the job failed on September 30th but then it couldn't restarted till Oct 1st?
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9

PostPosted: Wed Aug 02, 2017 4:36 pm    Post subject: Reply with quote

kolusu wrote:
Suchay,

You have an uncanny way of making the requirements complicated. You need to design generic solutions.



Requirements I am working are running for years as ad-hoc, manually supported by Users, currently, I am working on the automatization of the old processes without manual intervention


Quote:

Basically, your query can run on any day, but it needs to get the current_month -2 months start and begin dates?

but if runs on the month end then you need to get the current_month -1 months start and begin dates?

Is that it?

What happens when you run this job in the middle of the month? or for some reason, the job failed on September 30th but then it couldn't be restarted till Oct 1st?


Kolusu,

This is a month-end job, no chance of running middle of the month. IF month end falls on the weekend the job would run the last working day of the month. so I have given the example of September
since October 1st falls on Sunday so the job would run on September 29, 2017.We have two columns and I have mentioned in my post Wv-YR = 2017, Wv-Mo = 09, which is process month and year

If it runs on Sept 30th,2017 it should pick '2017-08-01' and '2017-08-31'
in case if it fails and runs on Oct 1st, 2017 it should pick '2017-08-01' and '2017-08-31'
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Aug 02, 2017 4:57 pm    Post subject: Reply with quote

Suchay wrote:
We have two columns and I have mentioned in my post Wv-YR = 2017, Wv-Mo = 09, which is process month and year


So basically the user passes you year and month and you just have to get the previous month begin and end date. So then it is quite simple, use those variables and create the begin and end dates. like below

Code:

SELECT DATE('2017'||'-'||'09'||'-'||'01') - 1 MONTH           
      ,LAST_DAY(DATE('2017'||'-'||'09'||'-'||'01') - 1 MONTH) 
  FROM SYSIBM.SYSDUMMY1                                       
  ;                                                                     

will produce
Code:

2017-08-01  2017-08-31

with host variables

Code:

SELECT DATE(:WV-YR ||'-'||:WV-MO||'-'||'01') - 1 MONTH             
      ,LAST_DAY(DATE(:WV-YR ||'-'||:WV-MO||'-'||'01') - 1 MONTH)   
  FROM SYSIBM.SYSDUMMY1                                             
  ;                                                                 

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group