Re: In SQL how to calculate how many days ago last Monday -- MIDRANGE-L

select dayname(current_date) from sysibm/sysdummy1

On Wed, Sep 1, 2021 at 11:40 AM Tom Hambel <thambel@xxxxxxxxxxxxxxx> wrote:

yes Alan, my bad, this should work.
SELECT
DAYOFWEEK(CURRENT TIMESTAMP) as curday#,
case
when DAYOFWEEK(cadate) <=2 then 5+ DAYOFWEEK(cadate)
else DAYOFWEEK(CURRENT TIMESTAMP)-2
end as Last_monday_days_ago,
CASE DAYOFWEEK(CURRENT TIMESTAMP)
WHEN 1
THEN 'SUNDAY'
WHEN 2
THEN 'MONDAY'
WHEN 3
THEN 'TUESDAY'
WHEN 4
THEN 'WEDNESDAY'
WHEN 5
THEN 'THURSDAY'
WHEN 6
THEN 'FRIDAY'
ELSE
'SATURDAY'
END AS DAYTEXT

FROM SYSIBM.SYSDUMMY1;

By the way, decades ago I had many date issues because the vendor that
wrote our initial system used decimal dates.
So I created a calendar table that holds every possible combination of a
date, including a date data type, DOW, etc. We even integrated work days,
holidays, etc.
that is very useful for things like this, and things like computing aging
in work days, etc.
Here is what the sql would be using that concept. I can give you the file
layout if your interested.

select cadate,days(current date) - days(cadate) as Last_monday_days_ago
from calndr
where cadow='Monday' and cadate<=current date and cadate >=current date -
7 days

-----Original Message-----
From: Tom Hambel
Sent: Wednesday, September 1, 2021 9:57 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: RE: In SQL how to calculate how many days ago last Monday

SELECT
DAYOFWEEK(CURRENT TIMESTAMP) as curday#,
case
when DAYOFWEEK(CURRENT TIMESTAMP) =2 then 7
else DAYOFWEEK(CURRENT TIMESTAMP)-2
end as Last_monday_days_ago,
CASE DAYOFWEEK(CURRENT TIMESTAMP)
WHEN 1
THEN 'SUNDAY'
WHEN 2
THEN 'MONDAY'
WHEN 3
THEN 'TUESDAY'
WHEN 4
THEN 'WEDNESDAY'
WHEN 5
THEN 'THURSDAY'
WHEN 6
THEN 'FRIDAY'
ELSE
'SATURDAY'
END AS DAYTEXT

FROM SYSIBM.SYSDUMMY1;

CENTRAL SEMICONDUCTOR CORP. CONFIDENTIALITY NOTICE: This electronic mail
transmission may be privileged, contain trade secrets, or otherwise
confidential information and should be read or retained only by the
intended recipient. If you have received this transmission in error, you
are hereby notified that any review, copying or distribution of it is
strictly prohibited. Please inform us immediately and destroy the original
transmittal. Thank you for your cooperation.
--
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