# Postgres: compare the dates in rows and find the difference

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.

• "if the date difference is bigger than 0, then multiply the number of day difference by the previous balance." -- I can't make sense of that. Please provide an example. If I pick `2017-06-03 392.41` and `2017-06-07 295.41`, what would be the balances for `2017-06-04` and `2017-06-05`? Are you basically just trying to do a linear increase/decline for the missing dates, based on the values you have? Jun 22, 2017 at 8:35
• i just edited my question. sorry, I had a mistake, I need to check if the difference is bigger than 1, i.e. whether the days are consecutive or not. If not, then the balance needs to be multiplied by this difference
– esem
Jun 22, 2017 at 8:44

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

• i receive the following error when trying to apply to my table `SQL Error [42883]: ERROR: operator does not exist: interval = integer Hinweis: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 318 org.postgresql.util.PSQLException: ERROR: operator does not exist: interval = integer Hinweis: No operator matches the given name and argument type(s). You might need to add explicit type casts.`
– esem
Jun 22, 2017 at 9:41
• @Astie which error? Jun 22, 2017 at 9:41
• it should be =1, but anyway when I try >1 the same error appears
– esem
Jun 22, 2017 at 9:44
• I've change it to <= 1 Jun 22, 2017 at 9:44
• Have you changed `dt` by the name of your field? Jun 22, 2017 at 9:45