Hi everyone
I believe I have what I am looking for
Using Toms suggestion - but using DAYOFWEEK_ISO results in the simplest piece of code
select
case dayofweek_iso(current_date)
when 1 then 7
else (dayofweek_iso(current_date) - 1) end as Days_Ago
FROM SYSIBM.SYSDUMMY1
Now if current_date is substituted with an actual date
If I run this query on:-
Monday 08/30/2021, Days_Ago is 7, the final prior Mondays date is 08/23/2021
Tuesday 08/31/2021, Days_Ago is 1, the final prior Mondays date is 08/30/3021
Wednesday 09/01/2021, Days_Ago is 2, the final prior Mondays date is 08/30/3021
Thursday 09/02/2021, Days_Ago is 3, the final prior Mondays date is 08/30/3021
Friday 09/03/2021, Days_Ago is 4, the final prior Mondays date is 08/30/3021
Saturday 09/04/2021, Days_Ago is 5, the final prior Mondays date is 08/30/3021
Sunday 09/05/2021, Days_Ago is 6, the final prior Mondays date is 08/30/3021
Alan Shore
Solutions Architect
IT Supply Chain Execution
[cid:image003.png@01D79F4D.25FAA380]
60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx
'If you're going through hell, keep going.'
Winston Churchill
From: Alan Shore
Sent: Wednesday, September 1, 2021 1:24 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] RE: In SQL how to calculate how many days ago last Monday was
Thanks Birgitta - but Im not sure this is what I am looking for
I did some substitutions with what you sent
Hopefully - what I attempted is correct
I wanted to see what the value would have been for Tuesday August 31, 2021
Then for Monday August 30, 2021
Im wondering if what I did is incorrect
Here is what I ran
1.
SELECT (current_date) ,
(current_date - (DayOfWeek(Current_Date) - 2) Days)
FROM sysibm.sysdummy1
Which gave the following results
CURRENT DATE Date expression
2021-09-01 2021-08-30
This is good
2.
SELECT (current_date - 1 day),
((current_date - 1 day) -
(DayOfWeek((Current_Date - 1 day)) - 2) Days)
FROM sysibm.sysdummy1
Which gave the following results
Date expression Date expression
2021-08-31 2021-08-30
This is good
3.
SELECT (current_date - 2 day),
((current_date - 2 day) -
(DayOfWeek((Current_Date - 2 day)) - 2) Days)
FROM sysibm.sysdummy1
Which gave the following result
Date expression Date expression
2021-08-30 2021-08-30
This is NOT what is needed
Mondays date in this situation needs to be the prior Monday (the second one needs to be 2021-08-23)
Alan Shore
Solutions Architect
IT Supply Chain Execution
[cid:image005.png@01D79F4D.25FAA380]
60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx<mailto:ASHORE@xxxxxxxxxxxxxxxxxxxx>
'If you're going through hell, keep going.'
Winston Churchill
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Wednesday, September 1, 2021 12:35 PM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: [EXTERNAL] RE: In SQL how to calculate how many days ago last Monday was
You want to start with the date of the previous Monday?
Then try this:
between (current_date - (DayOfWeek(Current_Date) - 2) Days) and
(current_date - 1 day) and Followed by Other selection
criteria
or
between (Next_Day(Current_Date - 7 Days, 'Monday')) and
(current_date - 1 day) and Followed by Other selection
criteria
If the current_date is a Monday, in both cases the current date will be
returned.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to." (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Alan
Shore via MIDRANGE-L
Sent: Mittwoch, 1. September 2021 14:38
To: midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx<mailto:ashore@xxxxxxxx>>
Subject: In SQL how to calculate how many days ago last Monday was
Hi everyone
We are on V7r3
I have a number of SQL queries that are run early each Monday, that collects
data from the previous Monday through yesterday (Sunday), using the
following code
WHERE nbtygpl.idate((a.CHKDAT), '*MDY')
between (current_date - 7 day) and
(current_date - 1 day) and Followed by Other selection
criteria
I was asked today (Wednesday September 1, 2021) if I could run these but
extract the data from this past Monday (August 30, 2021) So - for today - I
changed the selection criteria to be WHERE nbtygpl.idate((a.CHKDAT), '*MDY')
between (current_date - 2 day) and
(current_date - 1 day) and Followed by the remaining
selection criteria
It looks like this "special" run will be needed for each month end
NOW - here is my question
In SQL, s there any way to determine how long ago the prior Monday was?
So that if the job is run on a Monday - the field Prior_Mondays_days_ago (in
the SQL below) is 7
between (current_date - Prior_Mondays_days_ago day) and
If the job is run on Tuesday - Prior_Mondays_days_ago (in
the SQL) is 1
Wednesday -
Prior_Mondays_days_ago (in the SQL) is 2
Thursday -
Prior_Mondays_days_ago (in the SQL) is 3
Friday -
Prior_Mondays_days_ago (in the SQL) is 4
Saturday -
Prior_Mondays_days_ago (in the SQL) is 5
Sunday -
Prior_Mondays_days_ago (in the SQL) is 6
Monday -
Prior_Mondays_days_ago (in the SQL) is 7
As always - all answers gratefully accepted
Alan Shore
Solutions Architect
IT Supply Chain Execution
[cid:image001.png@01D79F0C.A45ED020]
60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx<mailto:ASHORE@xxxxxxxxxxxxxxxxxxxx>
'If you're going through hell, keep going.'
Winston Churchill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l<
https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l<
https://archive.midrange.com/midrange-l>.
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com<
https://amazon.midrange.com>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l<
https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l<
https://archive.midrange.com/midrange-l>.
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com<
https://amazon.midrange.com>
midrange.com
