CODEWITHSUNDEEP
sqlsqliteaggregate
Keithx
Keithx

Reputation: 3148

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!

Upvotes: 1

Views: 138

Answers (2)

LukStorms
LukStorms

Reputation: 29657

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];

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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;

Upvotes: 3

Related Questions