innerjoin on variable date range [message #642033] |
Mon, 31 August 2015 12:15 |
|
mattz
Messages: 1 Registered: August 2015 Location: US
|
Junior Member |
|
|
SQl Gurus,
There are four producing wells contributing to the total production on a platform . Wells A,B,C have metered data on them, which is monitored interminently (not metered everyday),
This Metered data as shown below in the excel (TABLE B) to decide each wells contribution to the total production ,except WELL D . So Modus operandi is to find the production numbers
for A , B & C based on the metered numbers, then deduce this off the total production value to get the production amount for D
Challenges - Metered data is not available everyday so when we try to join on date it gets tricky. This is where I Need help
I may not have the metered data everyday - So say for Eg, on 30th for WELL A I have metered data, and 31st I dont,, then production values for 31st will get the default metered value from 30th.
Attached is the excel model on what i have vs what i need to get.
Please help to form a query that can get the results on Table 3 as given in the attachment.
[Updated on: Mon, 31 August 2015 12:18] Report message to a moderator
|
|
|
|
|
Re: innerjoin on variable date range [message #642056 is a reply to message #642033] |
Tue, 01 September 2015 06:39 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Whilst I agree with Michel and Blackswan that it would be much more helpful to have an actual test case, your requirements sound like you need a bit of data densification with the partition join being the cleanest option. Then simply using the LAG function to get the previous day's production figure.
HTH
ETA: Ooops, meant to post an example
with t as (select 'A' well, date'2015-08-27' met_date, 100 amount from dual union all
select 'A' well, date'2015-08-28' met_date, 150 amount from dual union all
select 'A' well, date'2015-08-30' met_date, 200 amount from dual union all
select 'B' well, date'2015-08-27' met_date, 50 amount from dual union all
select 'B' well, date'2015-08-28' met_date, 80 amount from dual),
d as (select date'2015-08-27'+level-1 dt
from dual
connect by level<=3
)
select t.well
, d.dt
, t.amount
, nvl(t.amount, lag(amount) over (partition by t.well
order by d.dt)) amount
from d
left join t partition by (t.well)
on t.met_date = d.dt
order by 1, 2;
[Updated on: Tue, 01 September 2015 06:40] Report message to a moderator
|
|
|