troubleshooting Question

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

Avatar of riffrack
riffrack asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
7 Comments1 Solution459 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros