Reputation: 307
I have a table of historical data (#Records); at the end of a sproc I need to fill the data against a table of dates (dbo.MasterDates
). For the following simplified example, I'll simply use City as the identifier for a unique series of data.
tempdb.dbo.#Records:
Date | City | Value |
---|---|---|
2021-06-04 | LA | 10.5 |
2021-06-04 | NYC | 11.2 |
2021-06-05 | LA | 9.2 |
2021-06-06 | NYC | 8.1 |
dbo.Dates:
Date | Year | Month | Day |
---|---|---|---|
2021-06-04 | 2021 | 6 | 4 |
2021-06-05 | 2021 | 6 | 5 |
2021-06-06 | 2021 | 6 | 6 |
If the data was filtered so there was only one series being retrieved (i.e., only data for NYC), filling would be a simple select from dbo.Dates and a left outer join
on #Records. However, I need to fill such that there is a record for each day, for each unique City.
e.g. (filled records bolded)
Date | City | Value |
---|---|---|
2021-06-04 | LA | 10.5 |
2021-06-04 | NYC | 11.2 |
2021-06-05 | LA | 9.2 |
2021-06-05 | NYC | 0.0 |
2021-06-06 | LA | 0.0 |
2021-06-06 | NYC | 8.1 |
My first attempt was to create a version of the dbo.Dates with records for each unique city, and then use this to fill the #Records table:
insert into #FilledDates (Date, City)
select
dates.Date, City
from Dates d
cross apply
(
select distinct City from #Records
) r
select
d.Date, d.City, isnull(r.Value, 0)
from #FilledDates d
left outer join #Records r on r.Date = d.Date and r.City = d.City
This works fine; however, given that the actual use case is much more complex, I'm curious if there is a better way to achieve the desired result. The performance is my main concern... It seems like I should be able to combine the cross apply & join steps, however I'm still a bit new to cross apply.
Upvotes: 0
Views: 1504
Reputation: 416179
Create a projection with all the possible city/date combinations, and then join to that:
WITH Projection AS (
SELECT City, [Date]
FROM Dates
CROSS JOIN (SELECT DISTINCT City FROM Records) c
)
SELECT p.*, coalesce(r.[Value], 0.0) as [Value]
FROM Projection p
LEFT JOIN #Records r ON r.[Date] = p.[Date] AND r.City = p.City
ORDER BY [Date], City
See it work here:
Upvotes: 2