troubleshooting Question

Loading missing rows in SQL temp table

Avatar of countrymeister
countrymeister asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
1 Comment1 Solution343 ViewsLast Modified:
I have two temp tables

#tmpTableDates
(BusDate datetime)

#tmpTablePrices

(Code int (PK)
Busdate datetime,
Prices decimal(28, 8)
)

If I do not find the prices for a given date I have to use the previous days prices.
I get all the codes between the lowest date and the highest date.
In this example lowest date is 05/01/2013 and highest is 07/05/2013
Also the minimum date can have null values since there is nothing to go back to
I only need to return dates that I asked for and the relevant prices.

Lets say the data is as follow

#tmpTableDates
05/01/2013
06/01/2013
07/05/2013

#tmpTablePrices
Code 1 , 05/31/2013 , 5
Code 1, 07/01/2013,  3
Code 1 , 07/05/2013 , 2

My final result set should look like this, as 06/01 has no prices, take the previous days price.

Code 1 , 05/01/2013, NULL
Code 1 , 06/01/2013, 5
Code 1 , 07/05/2013, 2
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
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 1 Comment.
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