Home » SQL & PL/SQL » SQL & PL/SQL » innerjoin on variable date range
innerjoin on variable date range [message #642033] Mon, 31 August 2015 12:15 Go to next message
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 #642034 is a reply to message #642033] Mon, 31 August 2015 12:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: innerjoin on variable date range [message #642035 is a reply to message #642033] Mon, 31 August 2015 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data and search for a solution.

Re: innerjoin on variable date range [message #642056 is a reply to message #642033] Tue, 01 September 2015 06:39 Go to previous message
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

Previous Topic: Order by in Listagg
Next Topic: Data migration fromOracle to SFDC
Goto Forum:
  


Current Time: Thu Apr 18 04:00:55 CDT 2024