Complex query

I have two tables
Daily_Production (Tracks daily production data)
pdate,totalOil
1-Sep-05,30
2-Sep-05,28
'
'
3-Oct-05,30
4-Oct-05,35
Well_test (Welltest happens at any random date)
wellid,testdate,Oil
A,25-Aug-05,2
A,5-Sep-05,3
A,3-Oct-05,2
B,28-Aug-05,4
B,7-Sep-05,5
B,2-Oct-05,5
C,30-Aug-05,1
C,10-Sep-05,1
C,1-Oct-05,2
I want data in this format
ddate TotalOil
-- ddate is daily date which i can take from Daily_Production
-- Total Oil is the sum Oil from last tests on Wells A,B & C on or
before ddate
eg
ddate TotalOil
10-Sep-2005, 9
9= 3 ->from test on well A on 5-Sep-05 (It should not consider the test
oof 25-Aug-05)
+
5-> test on well B on 7-Sep-05
+
1-> test on well C on 10-Sep-05
i.e 3+5+1=9
Basically if my ddate is 10-Sep-2005 then I want the sum of Oil from
the last welltest done on wells A,B,C and <= ddate.
I hope my question is clear.>> I hope my question is clear.
Not exactly. Post your table structures, sample data & expected results.
Read www.aspfaq.com/5006 for details on how you can provide this without any
ambiguity.
Anith|||I believe this will work if I understand the problem correctly:
select pdate,
totaloild = (select sum(totaloil) from
(select max(oil) as totaloil from welltest where testdate <= pdate group by
wellid) as sub)
from dailyprod
JR
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:1128518309.256857.182330@.g47g2000cwa.googlegroups.com...
>I have two tables
> Daily_Production (Tracks daily production data)
> pdate,totalOil
> 1-Sep-05,30
> 2-Sep-05,28
> '
> '
> 3-Oct-05,30
> 4-Oct-05,35
> Well_test (Welltest happens at any random date)
> wellid,testdate,Oil
> A,25-Aug-05,2
> A,5-Sep-05,3
> A,3-Oct-05,2
> B,28-Aug-05,4
> B,7-Sep-05,5
> B,2-Oct-05,5
> C,30-Aug-05,1
> C,10-Sep-05,1
> C,1-Oct-05,2
> I want data in this format
> ddate TotalOil
> -- ddate is daily date which i can take from Daily_Production
> -- Total Oil is the sum Oil from last tests on Wells A,B & C on or
> before ddate
> eg
> ddate TotalOil
> 10-Sep-2005, 9
> 9= 3 ->from test on well A on 5-Sep-05 (It should not consider the test
> oof 25-Aug-05)
> +
> 5-> test on well B on 7-Sep-05
> +
> 1-> test on well C on 10-Sep-05
> i.e 3+5+1=9
>
> Basically if my ddate is 10-Sep-2005 then I want the sum of Oil from
> the last welltest done on wells A,B,C and <= ddate.
> I hope my question is clear.
>|||Thanks Jim.
But i think my question was not clear.
For each date in the TotalProduction table i want the total oil (i.e
sum of oil from A+B+C) from Welltest Table. But calculate the total it
should sum the oil from wells A,B,C ONLY from their last wellTest data
(read record) before the Production date.
Let me Modify the table data to make it more clear
Daily_Production (Tracks daily production data)
pdate,totalOil
1-Sep-05,30
2-Sep-05,28
3-Sep-05,50
'
'
3-Oct-05,30
4-Oct-05,35
Well_test (Welltest happens at any random date)
wellid,testdate,Oil
A, 2-Sep-05, 2
A, 3-Sep-05, 3
A, 5-Sep-05, 2
B, 2-Sep-05, 4
B, 4-Sep-05, 5
C, 1-Sep-05, 3
C, 2-Sep-05, 1
C, 4-Sep-05, 2
My output should be
DDate OIL
3-Sep-05 8 (3 [well A,testdate 3-Sep]+ 4 [well B, 3-Sep]+ 1 [Well C,
2-Sep]
4-Sep-05 12 (3 [well A,testdate 3-Sep]+ 5 [well B, 4-Sep]+ 4 [Well C,
4-Sep]
5-Sep-05 11 (2 [well A,testdate 5-Sep]+ 5 [well B, 4-Sep]+ 4 [Well C,
4-Sep]
i HOPE NOW IT IS CLEAR
Jim Ross wrote:
> I believe this will work if I understand the problem correctly:
> select pdate,
> totaloild = (select sum(totaloil) from
> (select max(oil) as totaloil from welltest where testdate <= pdate group b
y
> wellid) as sub)
> from dailyprod
> JR
> "Pradeep" <agarwalp@.eeism.com> wrote in message
> news:1128518309.256857.182330@.g47g2000cwa.googlegroups.com...|||Pradeep,
I have used your sample data (from 2nd post).
select p_date, sum(oil) totoil
from Well_test WT, Daily_Production DP
where (test_date = p_date or test_date = (select max(test_date) from
Well_test WTS where WTS.wellid = WT.wellid and test_date <= p_date))
group by p_date
will return the following result:
p_date totoil
--- --
2005-09-01 00:00:00.000 3
2005-09-02 00:00:00.000 7
2005-09-03 00:00:00.000 8
2005-10-03 00:00:00.000 9
2005-10-04 00:00:00.000 9
If you just want to get for single date
SELECT max(test_date), sum(oil)
FROM Well_test WT, (select wellid WID, max(test_date) TDT
from Well_test
where test_date <= @.singledt
group by wellid) DT
WHERE WT.wellid = DT.WID
AND WT.test_date = DT.TDT
===========
Let me know this helped you.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
My guess would that you need a table of test ranges
CREATE TABLE Tests
(test_start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
test_end_date DATETIME,
CHECK (test_start_date < test_end_date),
well_id CHAR(1) NOT NULL
REFERENCES Wells(well_id)
ON UPDATE CASCADE,
test_result INTEGER DEFAULT 0 NOT NULL);
A nuill endding date means that the test has not be done yet. When it
is, insert the results based on the date range and open up a new test.|||>> A nuill endding date means that the test has not be done yet. When it
A completed test may be different from a uncompleted one. The attributes
results and completion time are applicable only for completed tests which
suggests they are distinct entity types. Why club them together in a single
table with NULLs?
Anith|||On 5 Oct 2005 06:18:29 -0700, Pradeep wrote:

>I have two tables
>Daily_Production (Tracks daily production data)
>pdate,totalOil
>1-Sep-05,30
>2-Sep-05,28
>'
>'
>3-Oct-05,30
>4-Oct-05,35
>Well_test (Welltest happens at any random date)
>wellid,testdate,Oil
>A,25-Aug-05,2
>A,5-Sep-05,3
>A,3-Oct-05,2
>B,28-Aug-05,4
>B,7-Sep-05,5
>B,2-Oct-05,5
>C,30-Aug-05,1
>C,10-Sep-05,1
>C,1-Oct-05,2
>I want data in this format
>ddate TotalOil
>-- ddate is daily date which i can take from Daily_Production
>-- Total Oil is the sum Oil from last tests on Wells A,B & C on or
>before ddate
>eg
>ddate TotalOil
>10-Sep-2005, 9
>9= 3 ->from test on well A on 5-Sep-05 (It should not consider the test
>oof 25-Aug-05)
>+
>5-> test on well B on 7-Sep-05
>+
>1-> test on well C on 10-Sep-05
>i.e 3+5+1=9
>
>Basically if my ddate is 10-Sep-2005 then I want the sum of Oil from
>the last welltest done on wells A,B,C and <= ddate.
>I hope my question is clear.
Hi Pradeep,
Here's my (untested!!!) attempt. If the results are not as expected,
then please post CREATE TABLE and INSERT statments, as indicated at
www.aspfaq.com/5006.
SELECT dp.pdate, SUM(wt.Oil)
FROM Daily_Production AS dp
CROSS JOIN Well_test AS wt
WHERE wt.testdate <= dp.pdate
AND NOT EXISTS
(SELECT *
FROM Well_test AS wt2
WHERE wt2.wellid = wt.wellid
AND wt2.testdate > wt.testdate
AND wt2.testdate <= dp.pdate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> A completed test may be different from a uncompleted one.<<
When you have no specs, you do the best you can. But ask how it could
be different? I assume that an incompleted test become a completed
test at some point (how it completes is another question).
My assumption is that when a test is not finished, there are default
values should as zero for the stat work; or that you would construct a
VIEW that drops out the tests still in progress.
Because they are the same entities. Because the NULL represent an
actual missing value. Why split them into two tables? You then have
to move a test result from uncompleted tests to completed tests in
spite of the fact that they are BOTH tests.|||On 8 Oct 2005 18:46:54 -0700, --CELKO-- wrote:

> My assumption is that when a test is not finished, there are default
> values should as zero for the stat work; or that you would construct a
> VIEW that drops out the tests still in progress.
"... should as zero for the stat work"? That seems like a German sentence
construction.