CASE WHEN function & date function to change now() > to an assumption date

Question

WITH latest AS (
SELECT 
  DISTINCT customer_id,
  MAX(submitted_on) AS latest_order
FROM orders 
GROUP BY 1
),

AA AS (
SELECT 
  DISTINCT o.customer_id,
  latest.latest_order,
  now() - INTERVAL '91 days' AS reference_more_than_90D,
  now() - INTERVAL '31 days' AS reference_more_than_31D,
  now() - INTERVAL '30 days' AS reference_more_than_30D
FROM orders AS o
LEFT JOIN latest 
ON o.customer_id = latest.customer_id
GROUP BY 1,2,3,4,5
)

SELECT 
  DISTINCT o.customer_id,
  latest.latest_order,
  AA.reference_more_than_30D,
  AA.reference_more_than_31D,
  AA.reference_more_than_90D,
  CASE 
  WHEN latest.latest_order >= AA.reference_more_than_31D THEN 'r'
  WHEN latest.latest_order <= AA.reference_more_than_30D THEN 'a'
  ELSE 'l'
END AS status
FROM orders AS o
LEFT JOIN latest
ON  o.customer_id = latest.customer_id
LEFT JOIN AA
ON o.customer_id = AA.customer_id

With above this is the output

Seems that my CASE WHEN function isn't working right as 2021-04-29 is an older date than 2022-06-17/2022-06-16 and 2022-04-17 > The status should reflect 'l'
how should i change now() > to an assumption date like 2021-07-01 in this case ? Taking into consideration i only have 2021 & 2020 orders to look at

Thanks

Please update your question with sample data for all the tables and the result you get from your query when using that sample data — NickW, Jul 17 '22 at 13:15
that looks fine to me https://dbfiddle.uk/?rdbms=postgres_14&fiddle=061f0be9d1655c1e8fbd218c17c1ea98 maybe provide some more data, so that we can see your actual problem — nbk, Jul 17 '22 at 13:21
You seem to have an over enthusiastic attitude to `select distinct` - instead this is something you should only use carefully. You do NOT need `select distinct` in the "latest" cte. — Paul Maxwell, Jul 18 '22 at 05:06
sure thanks for the note . Im still very new to SQL hence thanks all for your guidance — Woofer, Jul 18 '22 at 09:43

score 0 · Answer 1 · edited Jul 18 '22 at 17:11

Seems that my CASE WHEN function isn't working right as 2021-04-29 is an older date than 2022-06-17/2022-06-16 and 2022-04-17 > The status should reflect 'l'.

I think you don't need to use those left joins. Please refer to the below script.

WITH latest AS (
SELECT 
  DISTINCT customer_id,
  MAX(submitted_on) AS latest_order
FROM orders 
GROUP BY 1
),

AA AS (
SELECT 
  DISTINCT customer_id,
  latest_order,
  now() - INTERVAL '91 days' AS reference_more_than_90D,
  now() - INTERVAL '31 days' AS reference_more_than_31D,
  now() - INTERVAL '30 days' AS reference_more_than_30D
FROM latest
GROUP BY 1,2,3,4,5
)

SELECT 
  DISTINCT customer_id,
  latest_order,
  reference_more_than_30D,
  reference_more_than_31D,
  reference_more_than_90D,
  CASE 
    WHEN latest_order >= reference_more_than_31D THEN 'r'
    WHEN latest_order <= reference_more_than_30D THEN 'a'
    ELSE 'l' END
  AS status
FROM AA

How should I change now() > to an assumption date like 2021-07-01 in this case ? Taking into consideration, I only have 2021 & 2020 orders to look at.

\set variable

hank=> \set name hank
hank=> \set time '2018-02-06 10:09:00'   
hank=>  select * from tb2 where c2=:'name' and c3>=:'time';
 c1 |  c2  |             c3             
----+------+----------------------------
  1 | hank | 2018-02-06 10:08:00.78750

How can I set now() to '2021-07-01' ? . I need to make an assumption of the current date to be '2021-07-01'.

CREATE OR REPLACE FUNCTION get_data (v_Date TIMESTAMP WITHOUT TIME ZONE) 
 RETURNS TABLE (
 latest_order TIMESTAMP WITHOUT TIME ZONE,
 reference_more_than_30D TIMESTAMP WITHOUT TIME ZONE,
 reference_more_than_31D TIMESTAMP WITHOUT TIME ZONE,
 reference_more_than_90D TIMESTAMP WITHOUT TIME ZONE
) 
AS $$
BEGIN
 RETURN QUERY SELECT v_Date, 
       v_Date - INTERVAL '30 days',
       v_Date - INTERVAL '31 days',
       v_Date - INTERVAL '91 days';
END; 
$$ LANGUAGE plpgsql;

select * from get_data('2021-07-01');

Thanks for your sharing . Yes indeed i do not need a left join . How can i set now() to '2021-07-01' ? . I need to make an assumption of the current date to be '2021-07-01' — Woofer, Jul 17 '22 at 15:14

Paul Maxwell · Accepted Answer · 2022-07-18T05:53:58.720

I would suggest avoiding the use of now() as this includes millisecond precision. Instead you probably only need date precision so you could use current_date instead. Plus you really don't need select distinct at all, nor do you need multiple joins etc.

WITH AA
AS (
    SELECT customer_id
        , CURRENT_DATE - INTERVAL '91 days' AS reference_more_than_90D
        , CURRENT_DATE - INTERVAL '31 days' AS reference_more_than_31D
        , CURRENT_DATE - INTERVAL '30 days' AS reference_more_than_30D
        , MAX(submitted_on) AS latest_order
    FROM orders
    GROUP BY customer_id
    )
SELECT AA.customer_id
    , AA.latest_order
    , AA.reference_more_than_30D
    , AA.reference_more_than_31D
    , AA.reference_more_than_90D
    , CASE 
        WHEN AA.latest_order >= AA.reference_more_than_31D
            THEN 'r'
        WHEN AA.latest_order <= AA.reference_more_than_30D
            THEN 'a'
        ELSE 'l'
        END AS STATUS
FROM AA

If you ned to use a preset date instead of current_date then just use a date literal instead e.g.

SELECT 
       customer_id
     , '2021-07-01'::timestamp - INTERVAL '91 days' AS reference_more_than_90D
     , '2021-07-01'::timestamp - INTERVAL '31 days' AS reference_more_than_31D
     , '2021-07-01'::timestamp - INTERVAL '30 days' AS reference_more_than_30D
     , MAX(submitted_on) AS latest_order
    FROM orders
    GROUP BY customer_id
    )
SELECT AA.customer_id
    , AA.latest_order
    , AA.reference_more_than_30D
    , AA.reference_more_than_31D
    , AA.reference_more_than_90D
    , CASE 
        WHEN AA.latest_order >= AA.reference_more_than_31D
            THEN 'r'
        WHEN AA.latest_order <= AA.reference_more_than_30D
            THEN 'a'
        ELSE 'l'
        END AS STATUS
FROM AA

see this db<>fiddle here

Thanks @Paul Maxwell for sharing . it works for my code . Many thanks — Woofer, Jul 18 '22 at 09:41

CASE WHEN function & date function to change now() > to an assumption date

2 Answers2