Run30 has 55 wells that belong to it. Temp table #Today returns 55 records. Temp table #SevenDaysAgo returns 53 because 2 wells don't have records on that date. How can I adjust my query to assign a value of 0 for the 2 wells that did not have a record for 7 days ago? Because currently my results produce 53 records because of the missing. I need 55 records to return. select wellname, convert(varchar,date,101) as Date, gasvolume as SevenDaysAgoGas into #SevenDaysAgo from DataDailyHistory where RunCode = 'run30' And date = dateadd(day,datediff(day,7,GETDATE()),0) order by WellName select wellname, convert(varchar,DateTime,101) as Date, GasVolumePDay as TodayGas into #Today from DataHourlySnapshot where runcode = 'run30' order by WellName select y.*, x.SevenDaysAgoGas, (y.TodayGas-x.SevenDaysAgoGas) as Difference from #SevenDaysAgo x left outer join #Today y on y.WellName = x.WellName
sounds like a job for a tally table / date table, and joining your results with that...
If that's not enough of a clue, and nobody else answers, I'll take a look at writing an actual script later.
--edit--
if you're looking for missing records based on something other than just date, then you may also need to create base tables / lists of things that you are expecting, and join those as well.