CODEWITHSUNDEEP
sqlsql-serverouter-joincross-apply
laventnc
laventnc

Reputation: 307

SQL join for filling missing values

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

Answers (1)

Joel Coehoorn
Joel Coehoorn

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:

http://sqlfiddle.com/#!18/bebc4/4/0

Upvotes: 2

Related Questions