From: | Anthony Apollis <anthony(dot)apollis(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Loops and Case Statements Involving Dates |
Date: | 2023-08-21 07:07:00 |
Message-ID: | CAJyMCY+UmcJX+nNQLj_McW-77o3zJ6CWuivUgnenm16acCYxHA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
*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>*
From | Date | Subject | |
---|---|---|---|
Next Message | jian he | 2023-08-21 08:01:25 | Re: Loops and Case Statements Involving Dates |
Previous Message | Rihad | 2023-08-21 05:31:21 | Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset? |