Thread: Loops and Case Statements Involving Dates

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.

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
THEN1 
>         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!"

Since this code is in a loop, consider using clock_timestamp() instead of now(). It might not matter in this situation, though.

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