calculate sum based on value of other row in another column

Question

I am trying to figure how can I calculate the number of days,the customer did not eat any candy. Assuming that the Customer eats 1 candy/day.
If customer purchases more candy, it gets added to previous stock
Eg.

Day Candy Puchased
0         30
40        30
65        30
110       30
125       40
170       30

Answer here is 20.

Meaning on 0th day, customer brought 30 candies and his next purchase was on 40th day so he did not get to eat any candy between 30th to 39th day, also in the same way he did not eat any candy between 100th to 109th day.

Can anyone help me to write the query. I think I have got the wrong logic in my query.

select sum(curr.candy_purchased-(nxt.day-curr.day)) as diff 
from candies as curr 
left join candies as nxt 
on nxt.day=(select min(day) from candies where day > curr.day) 

Answer 1

Nice question.

Check my answer and also try with different sample data. and please,if with different sample data it is not working then let me know.

declare @t table([Day] int, CandyPuchased int)
insert into @t
values (0, 30),(40,30),(65, 30)
,(110, 30),(125,40),(170,30)
select * from @t

;With CTE as
(
select *,ROW_NUMBER()over(order by [day])rn from @t
)
,CTE1 as
(
select [day],[CandyPuchased],rn from CTE c where rn=1
union all
select a.[Day],case when a.Day-b.Day<b.CandyPuchased 
then a.CandyPuchased+(b.CandyPuchased-(a.Day-b.Day)) 
else a.CandyPuchased end CandyPuchased
,a.rn from cte A 
inner join CTE B on a.rn=b.rn+1

)
--select * from CTE1
select sum(case when a.Day-b.Day>b.CandyPuchased 
then (a.Day-b.Day)-b.CandyPuchased else 0 end)[CandylessDays]  
from CTE1 A
inner join CTE1 b on a.rn=b.rn+1

Answer 2

You need a recursive CTE

First I need create a row_id so I use row_number

Now I need the base case for recursion.

• Day: Mean how many day has pass. (0 from db)
• PrevD: Is the Prev day amount so you can calculate Day (start at 0)
• Candy Puchased: How many cadies bought (30 from db)
• Remaining: How many candies left after eating (start at 0)
• NotEat: How many days couldnt eat candy (start at 0)
• Level: Recursion Level (start at 0)

Recursion Case

• Day, PrevD, Candy Puchased are easy
• Remaining: if I eat more than I have then 0
• NotEat: Keep adding the diffence when doesnt have candy.

SQL Fiddle Demo

WITH Candy as (
     SELECT
        ROW_NUMBER() over (order by [Day]) as rn,
        *
     FROM Table1
   ), EatCandy ([Day], [PrevD], [Candy Puchased], [Remaining], [NotEat], [Level]) as (
      SELECT [Day], 0 as [PrevD], [Candy Puchased], [Candy Puchased] as [Remaining], 0 as [NotEat], 1 as [Level]
      FROM Candy
      WHERE rn = 1
      UNION ALL
      SELECT c.[Day] - ec.[PrevD], 
             c.[Day],
             c.[Candy Puchased], 
             c.[Candy Puchased] + 
             IIF((c.[Day] - ec.[PrevD]) > ec.[Remaining], 0, ec.[Remaining] - (c.[Day] - ec.[PrevD])),
             ec.[NotEat] + 
             IIF((c.[Day] - ec.[PrevD]) > ec.[Remaining], (c.[Day] - ec.[PrevD]) - ec.[Remaining], 0),
             ec.[Level] + 1
      FROM Candy c
      JOIN EatCandy ec
        ON c.rn = ec.[level] + 1
    )
    select * from EatCandy

OUTPUT

| Day | PrevD | Candy Puchased | Remaining | NotEat | Level |
|-----|-------|----------------|-----------|--------|-------|
|   0 |     0 |             30 |        30 |      0 |     1 |
|  40 |    40 |             30 |        30 |     10 |     2 |
|  25 |    65 |             30 |        35 |     10 |     3 |
|  45 |   110 |             30 |        30 |     20 |     4 |
|  15 |   125 |             40 |        55 |     20 |     5 |
|  45 |   170 |             30 |        40 |     20 |     6 |

Just add SELECT MAX(NotEat) over the last query

Answer 3

If you just need the result at the end of the series, you don't really need that join.

select max(days) --The highest day in the table (convert these to int first)
- (sum(candies)  --Total candies purchased
- (select top 1 candies from #a order by days desc)) --Minus the candies purchased on the last day
from MyTable

If you need this as a sort of running total, try over:

select *, sum(candies) over (order by days) as TotalCandies
from MyTable
order by days desc