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
riffrackAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
PortletPaulfreelancerCommented:
What version of SQL Server are you using?
(SQL 2012 has features that might help here: LAG() )
0
PortletPaulfreelancerCommented:
:) Jim, there is already an AllDates table LOL, but I get it...
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

riffrackAuthor Commented:
SQL Server 2008 R2
0
Louis01Commented:
--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

0
Harish VargheseProject LeaderCommented:
Option 1:
CREATE VIEW V_PRICEHISTORY
AS
SELECT T.Product, T.PriceDate, PH.PriceValue
From PriceHistory PH, 
		(	select P.Product, D.AllDates_Date PriceDate, MAX(H.PriceDate) EffectiveDate
			From Products P, AllDates D, PriceHistory H
			WHere P.Product = H.Product
			And D.AllDates_Date >= H.PriceDate
			Group by P.Product, D.AllDates_Date
			having MAX(H.PriceDate) is not Null
		) T
Where T.EffectiveDate = PH.PriceDate
And T.Product = PH.Product

Open in new window

Option 2: Create a separate table which will have all calendar dates for each product and their respective Effective Price Date, which you need to maitain on daily basis - insert calendar date and respective effective price date.
CREATE TABLE EffectivePriceDates (Product char(20), 
           AllDate datetime, 
           EffectivePriceDate datettime,
           PRIMARY KEY (Product, AllDate))

Open in new window

And create view as:
CREATE VIEW V_PriceHistory
As
SELECT D.AllDate as PriceDate, P.Product
		P.PriceValue
FROM EffectivePriceDates D, PriceHistory P
WHERE P.PriceDate = D.EffectivePriceDate
And P.Product = D.Product

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
riffrackAuthor Commented:
Thanks for your help harish!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.