Calculating per day in SQL

Question

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 need a Calendar table and `cross join` the list of unique `Id's` within the required period and then perform a `Left join` to get the result — Pரதீப், Sep 17 '16 at 09:20
I dont have a calendar table and I guess there should be a way to deal it without it — Keithx, Sep 17 '16 at 09:25
Generate calendar in fly, using recursive CTE, as http://stackoverflow.com/questions/4245714/select-numbers-between-a-range-1-to-100-in-sqlite — Mike, Sep 17 '16 at 09:56
`with() select B.id, B.date, count(1) from (SELECT id, date(julianday('2015-01-01'), '+' || x || ' days') as date FROM cnt, (select distinct ID from Table) A) B left join Table on Table.date=B.date and Table.id=B.id group by B.id, B.date` — Mike, Sep 17 '16 at 11:17
@ it works but how can it be done to show the price for each date if exists and if not-0. using Case? I mean to replace your count function — Keithx, Sep 17 '16 at 11:44

Gordon Linoff · Accepted Answer · 2016-09-17T12:08:31.757

3

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;

edited Sep 17 '16 at 12:08

answered Sep 17 '16 at 11:24

Gordon Linoff

1,242,037
58
646
786

suddenly, dont work. I remove your second alias (as mindate as thedate) but still dont work. What should I change? – Keithx Sep 17 '16 at 11:57

score 1 · Answer 2 · answered Sep 17 '16 at 14:43

One method is using a tally table.
To build a list of dates and join that with the table.

The date stamps in the DD.MM.YY format are first changed to the YYYY-MM-DD date format.
To make it possible to actually use them as a date in the SQL.
At the final select they are formatted back to the DD.MM.YY format.

First some test data:

create table testtable (Id int, [Date] varchar(8), Price int);

insert into testtable (Id,[Date],Price) values (1,'21.09.09',25);
insert into testtable (Id,[Date],Price) values (1,'23.09.09',21);
insert into testtable (Id,[Date],Price) values (2,'31.08.09',16);
insert into testtable (Id,[Date],Price) values (2,'03.09.09',12);

The SQL:

with Digits as (
   select 0 as n  
   union all select 1 
   union all select 2 
   union all select 3 
   union all select 4 
   union all select 5 
   union all select 6 
   union all select 7 
   union all select 8 
   union all select 9 
),
t as (
    select Id, 
    ('20'||substr([Date],7,2)||'-'||substr([Date],4,2)||'-'||substr([Date],1,2)) as [Date],
    Price
    from testtable
),
Dates as (
  select Id, date(MinDate,'+'||(d2.n*10+d1.n)||' days') as [Date]
  from (
    select Id, min([Date]) as MinDate, max([Date]) as MaxDate 
    from t 
    group by Id
  ) q
  join Digits d1 
  join Digits d2
  where date(MinDate,'+'||(d2.n*10+d1.n)||' days') <= MaxDate
)
select d.Id, 
(substr(d.[Date],9,2)||'.'||substr(d.[Date],6,2)||'.'||substr(d.[Date],3,2)) as [Date],  
coalesce(t.Price,0) as Price 
from Dates d
left join t on (d.Id = t.Id and d.[Date] = t.[Date])
order by d.Id, d.[Date];

The recursive SQL below was totally inspired by the excellent answer from Gordon Linoff.
And a recursive SQL is probably more performant for this anyway.
(He should get the 15 points for the accepted answer).
The difference in this version is that the datestamps are first formatted to YYYY-MM-DD.

with t as (
    select Id, 
    ('20'||substr([Date],7,2)||'-'||substr([Date],4,2)||'-'||substr([Date],1,2)) as [Date],
    Price
    from testtable
),
cte as (
  select Id, min([Date]) as [Date], max([Date]) as MaxDate from t
  group by Id
  union all
  select Id, date([Date], '+1 day'), MaxDate from cte
  where [Date] < MaxDate
)
select cte.Id, 
(substr(cte.[Date],9,2)||'.'||substr(cte.[Date],6,2)||'.'||substr(cte.[Date],3,2)) as [Date], 
coalesce(t.Price, 0) as PricePerDay
from cte
left join t
  on (cte.Id = t.Id and cte.[Date] = t.[Date])
order by cte.Id, cte.[Date];

Calculating per day in SQL

2 Answers2