PostgreSQL – Compare Dates in Rows and Find the Difference

postgresql

I would like to check if a day is missing during the current month in my result set.The table looks like this. 

date               balance
2017-06-01          404.66
2017-06-03          392.41
2017-06-07          295.41
2017-06-10          275.41
2017-06-13          155.24
2017-06-15          151.74
2017-06-20           11.15
2017-06-21           -5.15

Since the result set is always within a month, only the difference in days is needed. I need to check the following two cases.

  1. if the difference between dates is 1, then take the corresponding balance as it is,

  2. if the date difference is bigger than 1, then multiply the number of day difference by the previous balance.

EDIT: For example, the day difference between the first two rows is 2. My desired output is 2*404,66.
And then, the day difference between 2nd and 3rd row is 4. So 4*392.41.

Is there any short and efficient way of doing this?

I need to analyze more than 300.000 data.

Best Answer

I've used LAG function to get previous date and previous balance and a CASE statement to calculate the value.

create table t1(dt date, balance decimal(18,2));
insert into t1 values
('2017-06-01',          404.66),
('2017-06-03',          392.41),
('2017-06-07',          295.41),
('2017-06-10',          275.41),
('2017-06-13',          155.24),
('2017-06-15',          151.74),
('2017-06-20',           11.15),
('2017-06-21',           -5.15);

select 
  dt, 
  balance, 
  case when (dt - coalesce(lag(dt) over (order by dt), dt)::date)::int <= 1 then balance
       else (dt - coalesce(lag(dt) over (order by dt), dt)::date)::int * (lag(balance) over (order by dt))::decimal(18,2)
       end as result
from t1;

dt         | balance |  result
:--------- | ------: | ------:
2017-06-01 |  404.66 |  404.66
2017-06-03 |  392.41 |  809.32
2017-06-07 |  295.41 | 1569.64
2017-06-10 |  275.41 |  886.23
2017-06-13 |  155.24 |  826.23
2017-06-15 |  151.74 |  310.48
2017-06-20 |   11.15 |  758.70
2017-06-21 |   -5.15 |   -5.15

dbfiddle here

Related Question