Fill values for every day of the year and add a group by clause

Currently we have data which contains data for every weekday, however some days don't have any values. We need to fill a table containing a value for every day of the year. In the case of a missing date, the table needs to show the last available data. Each product should have a record for each day from LaunchDate upto & including today.

We have a date table which contains every days date from 1970 to 2030. This table is called AllDates

For example we have a view called PriceHistory, which joins with a product table:
Date Value Product ProductLaunchDate
2010-02-02 0.8 Cheese 2010-02-02
2010-02-03 1.0 Cheese 2010-02-02
2010-02-04 1.1 Cheese 2010-02-02
2010-02-04 55.6 Salad 2010-02-04
2010-02-05 1.8 Cheese 2010-02-02
2010-02-08 2.5 Cheese 2010-02-02
2010-02-09 55.9 Salad 2010-02-04
2010-02-11 3.3 Cheese 2010-02-02

Should become:
Date Value Product
2010-02-02 0.8 Cheese
2010-02-03 1.0 Cheese
2010-02-04 1.1 Cheese
2010-02-04 55.6 Salad
2010-02-05 1.8 Cheese
2010-02-05 55.6 Salad
2010-02-06 1.8 Cheese
2010-02-06 55.6 Salad
2010-02-07 1.8 Cheese
2010-02-07 55.6 Salad
2010-02-08 2.5 Cheese
2010-02-08 55.6 Salad
2010-02-09 2.5 Cheese
2010-02-09 55.9 Salad
2010-02-10 2.5 Cheese
2010-02-10 55.9 Salad
2010-02-11 3.3 Cheese
2010-02-11 55.9 Salad
2010-02-12 3.3 Cheese
2010-02-13 55.9 Salad
...
2013-09-05 3.3 Cheese
2013-09-05 55.9 Salad

Jim Horn

>We need to fill a table containing a value for every day of the year.
What a coincidence.

>In the case of a missing date, the table needs to show the last available data
That'll take awhile to code, so can't help you quick on that one.

PortletPaul

What version of SQL Server are you using?
(SQL 2012 has features that might help here: LAG() )

PortletPaul

:) Jim, there is already an AllDates table LOL, but I get it...

$Avatar of riffrack$

riffrack

ASKER

SQL Server 2008 R2

Louis01

--Prepare test data
declare @PriceHistory table ([Date] Date, [Value] decimal(19,2), Product varchar(50), ProductLaunchDate date);
insert into @PriceHistory values ('2010-02-02',0.8,'Cheese','2010-02-02');
insert into @PriceHistory values ('2010-02-03',1.0,'Cheese','2010-02-02');
insert into @PriceHistory values ('2010-02-04',1.1,'Cheese','2010-02-02');
insert into @PriceHistory values ('2010-02-04',55.6,'Salad','2010-02-04');
insert into @PriceHistory values ('2010-02-05',1.8,'Cheese','2010-02-02');
insert into @PriceHistory values ('2010-02-08',2.5,'Cheese','2010-02-02');
insert into @PriceHistory values ('2010-02-09',55.9,'Salad','2010-02-04');
insert into @PriceHistory values ('2010-02-11',3.3,'Cheese','2010-02-02');

--Do the work
declare @StartDate date = '02-Feb-2010';
declare @EndDate date = getdate();

with Calendar as
    (
    select @StartDate as [Date]
    union All
    select DateAdd(d,1,[Date])
      from Calendar
     where [Date] < @EndDate
    )
select c.[Date], ph1.Value, ph1.Product
  from Calendar c
        cross join @PriceHistory ph1
 where c.[Date] >= ph1.[Date]
   and c.[Date] < IsNull((select MIN(ph2.[Date] )
                             from @PriceHistory ph2
                           where ph2.Product = ph1.Product
                             and ph2.[Date] > ph1.[Date]), getdate())
 order by c.[Date], ph1.Product
option (maxrecursion 0);

Open in new window

ASKER CERTIFIED SOLUTION

Harish Varghese

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

$Avatar of riffrack$

riffrack

ASKER

Thanks for your help harish!