question

bstorhaug avatar image
bstorhaug asked

Assign value 0 for missing records.

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


querytemporary-tableresultsrecord
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·

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.

1 Like 1 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.