View previous topic :: View next topic |
Author |
Message |
Suchay Beginner
Joined: 29 Jun 2017 Posts: 43 Topics: 9
|
Posted: Wed Aug 02, 2017 3:35 pm Post subject: Monthend job -get previous month start and end dates |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
|
Posted: Wed Aug 02, 2017 3:53 pm Post subject: |
|
|
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 |
|
|
Suchay Beginner
Joined: 29 Jun 2017 Posts: 43 Topics: 9
|
Posted: Wed Aug 02, 2017 4:03 pm Post subject: |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
|
Posted: Wed Aug 02, 2017 4:13 pm Post subject: |
|
|
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 |
|
|
Suchay Beginner
Joined: 29 Jun 2017 Posts: 43 Topics: 9
|
Posted: Wed Aug 02, 2017 4:36 pm Post subject: |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
|
Posted: Wed Aug 02, 2017 4:57 pm Post subject: |
|
|
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 |
|
|
|
|