HalfPintBoy - 1 year ago 89
SQL Question

# Calculating per day in SQL

I have an sql table like that:

``````Id    Date     Price
1  21.09.09     25
2  31.08.09     16
1  23.09.09     21
2  03.09.09     12
``````

So what I need is to get min and max date for each id and dif in days between them. It is kind of easy. Using SQLlite syntax:

``````  SELECT id,
min(date),
max(date),
julianday(max(date)) - julianday(min(date)) as dif
from table group by id
``````

Then the tricky one: how can I receive the price per day during this difference period. I mean something like this:

``````ID Date      PricePerDay
1  21.09.09      25
1  22.09.09       0
1  23.09.09      21
2  31.08.09      16
2  01.09.09       0
2  02.09.09       0
2  03.09.09      12
``````

I create a cte as you mentioned with calendar but dont know how to get the desired result:

``````WITH RECURSIVE
cnt(x) AS (
SELECT 0
UNION ALL
SELECT x+1 FROM cnt
LIMIT (SELECT ((julianday('2015-12-31') - julianday('2015-01-01')) + 1)))
SELECT date(julianday('2015-01-01'), '+' || x || ' days') as date FROM cnt
``````

p.s. If it will be in sqllite syntax-would be awesome!

You can use a recursive CTE to calculate all the days between the min date and max date. The rest is just a `left join` and some logic:

``````with recursive cte as (
select t.id, min(date) as thedate, max(date) as maxdate
from t
group by id
union all
select cte.id, date(thedate, '+1 day') as thedate, cte.maxdate
from cte
where cte.thedate < cte.maxdate
)
select cte.id, cte.date,
coalesce(t.price, 0) as PricePerDay
from cte left join
t
on cte.id = t.id and cte.thedate = t.date;
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download