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.

2
  • 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, 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

1 Answer 1

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

6
  • 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?
    – McNets
    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
    – McNets
    Jun 22, 2017 at 9:44
  • Have you changed dt by the name of your field?
    – McNets
    Jun 22, 2017 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.