Avatar of riffrack
riffrack asked on

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
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
riffrack

8/22/2022 - Mon
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...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
riffrack

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
riffrack

Thanks for your help harish!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.