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> On Behalf Of Alan
Shore via MIDRANGE-L
Sent: Mittwoch, 1. September 2021 14:38
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Alan Shore <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
'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 To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
