Re: In SQL how to calculate how many days ago last Monday was

[Raul Alberto Jager Weiler <raul.jager@xxxxxxxxx>]

Use DOW function  (Day Of Week)

On Wed, Sep 1, 2021 at 8:38 AM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> 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