0
subscription_id purchase_date current_date plan_end_date date_diff
1111 01-04-2022 11-04-2022 14-04-2022 3
2222 02-04-2022 11-04-2022 09-04-2022 -2
3333 06-04-2022 11-04-2022 11-04-2022 0

I am looking for the above answer but not getting. It's coming different answer if I use lag and lead function.

CASE
        WHEN plan_end_date > cur_date THEN cur_date - LAG(plan_end_date) OVER (ORDER BY cur_date) 
        WHEN plan_end_date < cur_date THEN cur_date - LEAD(plan_end_date) OVER (ORDER BY cur_date)
        WHEN plan_end_date = cur_date THEN NULL
    END AS date_diff

here is the above condition I have used.

PiKs
  • 11
  • 3
  • `ORDER BY cur_date`, and cur_date seems to have always the same value? Maybe change it to `ORDER BY cur_date,plan_end_date` ? to sort on plan_end_date when cur_date has same value – Luuk Apr 11 '22 at 14:13
  • @Luuk The answer is still wrong. – PiKs Apr 11 '22 at 14:25
  • Can you provide more date? I created a [dbfiddle](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=263dbc7b7e59b580bbce9bdd8ea0b149) and there is not enough data to reproduce your problem. – Luuk Apr 11 '22 at 14:26
  • What are you trying to do with lead and lag? What is your original data, what is the exact problem? – S-Man Apr 11 '22 at 15:12
  • `SELECT cast(plan_end_date as date) -cast(cur_date as date)` , should be giving you the days difference. – Anand Sowmithiran Apr 11 '22 at 15:30
  • I don't get the impression you really want to look outside the current row. There's no need for `lead/lag()` if so. – shawnt00 Apr 11 '22 at 16:15
  • I need for 7 days date difference which gives today's date and +3 as well as -3 days. So I can pick any date as today's date to get both sides of the day over day cycle. – PiKs Apr 11 '22 at 16:19

1 Answers1

-1

SELECT cast(plan_end_date as date) -cast(cur_date as date) , should be giving you the difference in days.

Anand Sowmithiran
  • 2,591
  • 2
  • 10
  • 22