pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedLoops and Case Statements Involving Dates
4+ messages / 4 participants
[nested] [flat]
* Loops and Case Statements Involving Dates
@ 2023-08-21 07:07 Anthony Apollis <anthony.apollis@gmail.com>
2023-08-21 08:01 ` Re: Loops and Case Statements Involving Dates jian he <jian.universality@gmail.com>
2023-08-21 11:28 ` Re: Loops and Case Statements Involving Dates Peter J. Holzer <hjp-pgsql@hjp.at>
2023-08-21 15:08 ` Re: Loops and Case Statements Involving Dates Ron <ronljohnsonjr@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Anthony Apollis @ 2023-08-21 07:07 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
*Please review my code and make recommendations where needed. I have this code:
*```
NUMBER OF LOOPS FOR POSTGRESQL ETL:
SELECT
CASE
WHEN (((EXTRACT(DAY FROM
((CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS
"Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS
WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS
"Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS
WEEK_NUMBER)) + interval '1 day'
END))::timestamp - --start date
(CAST(NOW() - INTERVAL '1 day' AS DATE))::timestamp)-1) --end date
* -1)) <= 30 THEN 1
ELSE
CEIL(((EXTRACT(DAY FROM
((CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS
"Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS
WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS
"Calendar day" FROM system."IMETA_ZINV_AP1_Invoice_data_TA_BW"" AS
WEEK_NUMBER)) + interval '1 day'
END))::timestamp - --start date
(CAST(NOW() - INTERVAL '1 day' AS DATE))::timestamp)-1) --end date
* -1)/30) --30 DAY INTERVALS UNLESS LESS
END
AS "Number of days"
I have re-written this code to make it less complex, still doing what
it is supposed to do. I want you to review my re-written code(code
must give me report up until yesterday):
```
WITH MaxDateCTE AS (
SELECT
COALESCE(MAX(DISTINCT "CALDAY"), '2021-07-01') AS MaxDate
FROM
"system"."IMETA_ZTRNSPCST$F_Shipment_Cost_TA_BW"
)
SELECT
CASE
WHEN EXTRACT(DAY FROM (MaxDate + INTERVAL '1 day')::timestamp
- (NOW() - INTERVAL '1 day')::timestamp) <= 30 THEN 1
ELSE CEIL(EXTRACT(DAY FROM (MaxDate + INTERVAL '1
day')::timestamp - (NOW() - INTERVAL '1 day')::timestamp) / 30)
END AS "Number of days"
FROM
MaxDateCTE;
*Full Code can be found here:
https://drive.google.com/file/d/1NaoaK0z3s3cfYilAdH4stJ1F6mq6Sc4n/view?usp=sharing
<https://drive.google.com/file/d/1NaoaK0z3s3cfYilAdH4stJ1F6mq6Sc4n/view?usp=sharing>*
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Loops and Case Statements Involving Dates
2023-08-21 07:07 Loops and Case Statements Involving Dates Anthony Apollis <anthony.apollis@gmail.com>
@ 2023-08-21 08:01 ` jian he <jian.universality@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: jian he @ 2023-08-21 08:01 UTC (permalink / raw)
To: Anthony Apollis <anthony.apollis@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Mon, Aug 21, 2023 at 3:07 PM Anthony Apollis
<anthony.apollis@gmail.com> wrote:
>
> Please review my code and make recommendations where needed. I have this code:
> ```
> NUMBER OF LOOPS FOR POSTGRESQL ETL:
> SELECT
> CASE
> WHEN (((EXTRACT(DAY FROM
> ((CASE
> WHEN
> (SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
> WHERE
> WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) IS NULL THEN '2020-07-01'
> ELSE
> (SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
> WHERE
> WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) + interval '1 day'
> END))::timestamp - --start date
> (CAST(NOW() - INTERVAL '1 day' AS DATE))::timestamp)-1) --end date
> * -1)) <= 30 THEN 1
> ELSE
> CEIL(((EXTRACT(DAY FROM
> ((CASE
> WHEN
> (SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
> WHERE
> WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) IS NULL THEN '2020-07-01'
> ELSE
> (SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
> WHERE
> WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM system."IMETA_ZINV_AP1_Invoice_data_TA_BW"" AS WEEK_NUMBER)) + interval '1 day'
> END))::timestamp - --start date
> (CAST(NOW() - INTERVAL '1 day' AS DATE))::timestamp)-1) --end date
> * -1)/30) --30 DAY INTERVALS UNLESS LESS
> END
> AS "Number of days"
>
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW”
seems like some of the characters look like double quotes, but it's
maybe not a double quote.
wiki: https://en.wikipedia.org/wiki/Quotation_mark.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Loops and Case Statements Involving Dates
2023-08-21 07:07 Loops and Case Statements Involving Dates Anthony Apollis <anthony.apollis@gmail.com>
@ 2023-08-21 11:28 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2 siblings, 0 replies; 4+ messages in thread
From: Peter J. Holzer @ 2023-08-21 11:28 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2023-08-21 09:07:00 +0200, Anthony Apollis wrote:
> Please review my code and make recommendations where needed. I have this code:
> ```
[complicated code snipped]
>
>
> I have re-written this code to make it less complex, still doing what
> it is supposed to do. I want you to review my re-written code(code
> must give me report up until yesterday):
> ```
> WITH MaxDateCTE AS (
> SELECT
> COALESCE(MAX(DISTINCT "CALDAY"), '2021-07-01') AS MaxDate
> FROM
> "system"."IMETA_ZTRNSPCST$F_Shipment_Cost_TA_BW"
> )
>
> SELECT
> CASE
> WHEN EXTRACT(DAY FROM (MaxDate + INTERVAL '1 day')::timestamp - (NOW() - INTERVAL '1 day')::timestamp) <= 30 THEN 1
> ELSE CEIL(EXTRACT(DAY FROM (MaxDate + INTERVAL '1 day')::timestamp - (NOW() - INTERVAL '1 day')::timestamp) / 30)
> END AS "Number of days"
> FROM
> MaxDateCTE;
So the intent is to compute how many "months" the maximum CALDAY is in
the future, with some minor twists:
* A "month" is always 30 days, not a calendar month.
* The difference is between the day after the given date and yesterday -
so it's shifted by one day (today + 30 days already counts as 2
months)
* the minimum is 1.
?
Then this can be simplified further:
* MAX(DISTINCT "CALDAY") can be simplified to just MAX("CALDAY").
* The CASE can be eliminated and replaced by
GREATEST(CEIL(EXTRACT(DAY FROM (MaxDate + INTERVAL '1 day')::timestamp - (NOW() - INTERVAL '1 day')::timestamp) / 30), 1)
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Loops and Case Statements Involving Dates
2023-08-21 07:07 Loops and Case Statements Involving Dates Anthony Apollis <anthony.apollis@gmail.com>
@ 2023-08-21 15:08 ` Ron <ronljohnsonjr@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Ron @ 2023-08-21 15:08 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Since this code is in a loop, consider using clock_timestamp() instead of
now(). It might not matter in this situation, though.
On 8/21/23 02:07, Anthony Apollis wrote:
> *Please review my code and make recommendations where needed. I have this
> code: *```
> NUMBER OF LOOPS FOR POSTGRESQL ETL:
> SELECT
> CASE
> WHEN (((EXTRACT(DAY FROM
> ((CASE
> WHEN
> (SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
> WHERE
> WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) IS NULL THEN '2020-07-01'
> ELSE
> (SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
> WHERE
> WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) + interval '1 day'
> END))::timestamp - --start date
> (CAST(NOW() - INTERVAL '1 day' AS DATE))::timestamp)-1) --end date
> * -1)) <= 30 THEN 1
> ELSE
> CEIL(((EXTRACT(DAY FROM
> ((CASE
> WHEN
> (SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
> WHERE
> WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) IS NULL THEN '2020-07-01'
> ELSE
> (SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
> WHERE
> WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM system."IMETA_ZINV_AP1_Invoice_data_TA_BW"" AS WEEK_NUMBER)) + interval '1 day'
> END))::timestamp - --start date
> (CAST(NOW() - INTERVAL '1 day' AS DATE))::timestamp)-1) --end date
> * -1)/30) --30 DAY INTERVALS UNLESS LESS
> END
> AS "Number of days"
> I have re-written this code to make it less complex, still doing what it is supposed to do. I want you to review my re-written code(code must give me report up until yesterday):
> ```
> WITH MaxDateCTE AS (
> SELECT
> COALESCE(MAX(DISTINCT "CALDAY"), '2021-07-01') AS MaxDate
> FROM
> "system"."IMETA_ZTRNSPCST$F_Shipment_Cost_TA_BW"
> )
>
> SELECT
> CASE
> WHEN EXTRACT(DAY FROM (MaxDate + INTERVAL '1 day')::timestamp - (NOW() - INTERVAL '1 day')::timestamp) <= 30 THEN 1
> ELSE CEIL(EXTRACT(DAY FROM (MaxDate + INTERVAL '1 day')::timestamp - (NOW() - INTERVAL '1 day')::timestamp) / 30)
> END AS "Number of days"
> FROM
> MaxDateCTE;
>
> **
> *Full Code can be found here:
> https://drive.google.com/file/d/1NaoaK0z3s3cfYilAdH4stJ1F6mq6Sc4n/view?usp=sharing*
>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-08-21 15:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-21 07:07 Loops and Case Statements Involving Dates Anthony Apollis <anthony.apollis@gmail.com>
2023-08-21 08:01 ` jian he <jian.universality@gmail.com>
2023-08-21 11:28 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-08-21 15:08 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox