pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Loops and Case Statements Involving Dates
Date: Mon, 21 Aug 2023 10:08:27 -0500
Message-ID: <67cf2c16-7f53-ab0a-03b8-bd86bbfc6e0c@gmail.com> (raw)
In-Reply-To: <CAJyMCY+UmcJX+nNQLj_McW-77o3zJ6CWuivUgnenm16acCYxHA@mail.gmail.com>
References: <CAJyMCY+UmcJX+nNQLj_McW-77o3zJ6CWuivUgnenm16acCYxHA@mail.gmail.com>

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.

view thread (4+ messages)

Message-ID: <67cf2c16-7f53-ab0a-03b8-bd86bbfc6e0c@gmail.com>
Permalink:  ../67cf2c16-7f53-ab0a-03b8-bd86bbfc6e0c@gmail.com/
Also on:    postgresql.org/message-id/67cf2c16-7f53-ab0a-03b8-bd86bbfc6e0c@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: Loops and Case Statements Involving Dates
  In-Reply-To: <67cf2c16-7f53-ab0a-03b8-bd86bbfc6e0c@gmail.com>
  References: <CAJyMCY+UmcJX+nNQLj_McW-77o3zJ6CWuivUgnenm16acCYxHA@mail.gmail.com> <67cf2c16-7f53-ab0a-03b8-bd86bbfc6e0c@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox