Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qY6WF-000nJ4-6W for pgsql-general@arkaria.postgresql.org; Mon, 21 Aug 2023 15:08:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qY6WD-007Htn-VN for pgsql-general@arkaria.postgresql.org; Mon, 21 Aug 2023 15:08:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qY6WD-007Hso-75 for pgsql-general@lists.postgresql.org; Mon, 21 Aug 2023 15:08:37 +0000 Received: from mail-yw1-x112a.google.com ([2607:f8b0:4864:20::112a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qY6W5-000Adc-E5 for pgsql-general@lists.postgresql.org; Mon, 21 Aug 2023 15:08:35 +0000 Received: by mail-yw1-x112a.google.com with SMTP id 00721157ae682-5922b96c5fcso7682757b3.0 for ; Mon, 21 Aug 2023 08:08:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692630508; x=1693235308; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=qs3WH+KzntsxOte+dp4ek6OHIBGdXeHiRU+uXQfxozw=; b=O23QQjDmynP+j01s+T2um3r79UjMbJSnW22ZHWxFaiCJke7+3p8kiD426CE4pKOIa7 Vd3AQ807so28puWe5s4MxjJQp2z8dOtt3SO7ifBQ9CgMrNLccGSP9ndzU+XsjmSZNxXV cgonSUSPWiYgTfJffc3oQQrMi2wI1k+t34BSdb/KpoVnJmsfhNOJvJHBWkmosZZTUtKl EeIxz7KCZYPvTi93BPwt3KcnwdVm3jilrlXa8KkLTUh7/X65PfP2Q012q3EGp4WH9aOY HEnulYgzzPTH3s0DsFpZsLasa54JGF177TAqo8e6P99zJt8rWhdaCjRFM0lL4nTdh5if l7kw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692630508; x=1693235308; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=qs3WH+KzntsxOte+dp4ek6OHIBGdXeHiRU+uXQfxozw=; b=IDZpRhVUn/Pe7a1TeaeDE+pxqPJJroPJk+uQcK5puSsE8HC8aalpgQ/3EHrfVD3ERL uxPzVfqqhGxXpVT+FcSMwpG540xcxJMYtPHak5DB1KgHnulQdCGs8QuhKx8yRTFXZzeW q5RCWuosg2zgYn32+W8y83ydGZSXeo9f3i+giGDQmzcAGpMyGdv32T0hmWS3mqPKocYV QRyC7sIDS0GPhe4FMkeHlXmoCJ6RaDrc+zxZBkRiocXDcyfo0+87fo5jMVAn5Tj4fnj1 wFjMtfC2gh4hWpfvw5HnhIC1e0HUdo33JRMk9I7+TNk3k5+D3ej05jF9xLu0eUP1DLKm iZEw== X-Gm-Message-State: AOJu0YytvQ06KMXEl+baehJfL+n8eW76M//dkNCULS+re4+x+/mFsqtY XcQVpIdSNtB4ohpxq+Xf7f+jkbYpUEU= X-Google-Smtp-Source: AGHT+IElLd/Y5SzXcUDqpFMV10RBo1BV3hbMhcilmYRsK61PQgkH2nI+JVngad+EZGwl5UBpMl4AQg== X-Received: by 2002:a81:6cd5:0:b0:54f:ba89:225d with SMTP id h204-20020a816cd5000000b0054fba89225dmr6907386ywc.19.1692630508339; Mon, 21 Aug 2023 08:08:28 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:6806:eeb6:3a6:c040? ([2600:1700:bd2c:b810:6806:eeb6:3a6:c040]) by smtp.googlemail.com with ESMTPSA id d15-20020a814f0f000000b00584554be59dsm2266489ywb.85.2023.08.21.08.08.27 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 21 Aug 2023 08:08:27 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------dhB6100TNNhu2290tlUMC9vP" Message-ID: <67cf2c16-7f53-ab0a-03b8-bd86bbfc6e0c@gmail.com> Date: Mon, 21 Aug 2023 10:08:27 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Loops and Case Statements Involving Dates Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------dhB6100TNNhu2290tlUMC9vP Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------dhB6100TNNhu2290tlUMC9vP Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------dhB6100TNNhu2290tlUMC9vP--