1

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.

  • 1
    "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? – Philᵀᴹ Jun 22 '17 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 '17 at 8:44
0

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

|improve this answer|||||
  • 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 '17 at 9:41
  • @Astie which error? – McNets Jun 22 '17 at 9:41
  • it should be =1, but anyway when I try >1 the same error appears – esem Jun 22 '17 at 9:44
  • I've change it to <= 1 – McNets Jun 22 '17 at 9:44
  • Have you changed dt by the name of your field? – McNets Jun 22 '17 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.