Search Postgresql Archives

Loops and Case Statements Involving Dates

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux