Re: Loops and Case Statements Involving Dates - Mailing list pgsql-general

From Ron
Subject Re: Loops and Case Statements Involving Dates
Date
Msg-id 67cf2c16-7f53-ab0a-03b8-bd86bbfc6e0c@gmail.com
Whole thread Raw
In response to Loops and Case Statements Involving Dates  (Anthony Apollis <anthony.apollis@gmail.com>)
List 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.

pgsql-general by date:

Previous
From: Ron
Date:
Subject: Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?
Next
From: Edoardo Panfili
Date:
Subject: The same prepared query yield "-1" the first six times and then "-1.0"