From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Loops and Case Statements Involving Dates |
Date: | 2023-08-21 15:08:27 |
Message-ID: | 67cf2c16-7f53-ab0a-03b8-bd86bbfc6e0c@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Edoardo Panfili | 2023-08-21 15:27:20 | The same prepared query yield "-1" the first six times and then "-1.0" |
Previous Message | Ron | 2023-08-21 14:47:05 | Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset? |