[AccessD] Query not returning desired results
Kaup, Chester
Chester_Kaup at kindermorgan.com
Tue Dec 10 09:57:18 CST 2019
You make a valid point about my ugly SQL statement.
The issue is that the two Other Pattern values are being excluded but instead of excluding these values when the field Other_Pattern_as_Well_API Is Null is null the query is including all records where the field Other_Pattern_as_Well_API Is Null is null
Example of a valid records
Well_In_CO2PAT Well_in_CO2PAT_API ProductionMonth Other_Pattern Other_Pattern_as_Well Other_Pattern_as_Well_API 3mo_rbi SumOf3mo_rbi Factor_2
10-1 42415031090000 11/1/2019 PAT 10-1 10-1 4241503109 256117.4 256,117.40 1.00
10-10 42415304550000 11/1/2019 PAT 10-1 10-1 4241503109 256117.4 1,156,496.68 0.22
10-10 42415304550000 11/1/2019 PAT 10-1M 10-1M 4241535075 103812.816237479 1,156,496.68 0.09
10-10 42415304550000 11/1/2019 PAT 10-5A 10-5A 4241535062 457659.706363636 1,156,496.68 0.40
10-10 42415304550000 11/1/2019 PAT 11-5A 11-5A 4241534970 87307.7045454545 1,156,496.68 0.08
10-10 42415304550000 11/1/2019 PAT 11-5M 11-5M 4241535044 51235.5777634696 1,156,496.68 0.04
10-10 42415304550000 11/1/2019 PAT 11-8A 11-8A 4241534997 200363.48 1,156,496.68 0.17
10-10 42415304550000 11/1/2019 PAT 11-8B 11-8B 4241534998 0 1,156,496.68 0.00
10-10A 42415350480000 11/1/2019 PAT 10-1 10-1 4241503109 256117.4 1,156,496.68 0.22
10-10A 42415350480000 11/1/2019 PAT 10-1M 10-1M 4241535075 103812.816237479 1,156,496.68 0.09
10-10A 42415350480000 11/1/2019 PAT 10-5A 10-5A 4241535062 457659.706363636 1,156,496.68 0.40
10-10A 42415350480000 11/1/2019 PAT 11-5A 11-5A 4241534970 87307.7045454545 1,156,496.68 0.08
10-10A 42415350480000 11/1/2019 PAT 11-5M 11-5M 4241535044 51235.5777634696 1,156,496.68 0.04
10-10A 42415350480000 11/1/2019 PAT 11-8A 11-8A 4241534997 200363.48 1,156,496.68 0.17
10-10A 42415350480000 11/1/2019 PAT 11-8B 11-8B 4241534998 0 1,156,496.68 0.00
Example of invalid records
Well_In_CO2PAT Well_in_CO2PAT_API ProductionMonth Other_Pattern Other_Pattern_as_Well Other_Pattern_as_Well_API 3mo_rbi SumOf3mo_rbi Factor_2
28-10 42415305460000 11/1/2019 PAT 28-5A 28-5A 195497.44 1,691,433.78 0.12
28-14 42415306150000 11/1/2019 PAT 28-5A 28-5A 195497.44 2,055,450.20 0.10
28-17 42415324490000 11/1/2019 PAT 28-5A 28-5A 195497.44 1,344,883.87 0.15
28-18 42415345120000 11/1/2019 PAT 28-5A 28-5A 195497.44 1,137,363.88 0.17
28-5 42415030820000 11/1/2019 PAT 28-5A 28-5A 195497.44 390,994.88 0.50
28-5AL 42415345070001 11/1/2019 PAT 28-5A 28-5A 195497.44 390,994.88 0.50
28-5AS 42415345070000 11/1/2019 PAT 28-5A 28-5A 195497.44 390,994.88 0.50
28-9 42415304030000 11/1/2019 PAT 28-5A 28-5A 195497.44 1,393,984.50 0.14
28-9A 42415345110000 11/1/2019 PAT 28-5A 28-5A 195497.44 1,393,984.50 0.14
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Tuesday, December 10, 2019 9:22 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query not returning desired results
[This email message was received from the Internet and came from outside of Kinder Morgan]
I find your long space filled table and query names and copious bracketing together with long
run on lines makes all of your problem SQL hard to analyse. Suggest you get in the habit of
using aliases and only bracket where necessary.
I can analyse this much easier:
SELECT q3.Well_In_CO2PAT,
t3.Well_in_CO2PAT_API,
t3.ProductionMonth,
t3.Other_Pattern,
t3.Other_Pattern_as_Well,
t3.Other_Pattern_as_Well_API,
t3.3mo_rbi,
q3.SumOf3mo_rbi,
IIf(SumOf3mo_rbi=0,al.Factor_2,3mo_rbi/SumOf3mo_rbi) AS
Factor_2 INTO [tbl 3 Month Factor 2]
FROM [qry 3 Months Running Injection Data Totals] q3
INNER JOIN [tbl 3 Month Running Inj Data] t3
ON q3.Well_in_CO2PAT_API = t3.Well_in_CO2PAT_API
AND q3.ProductionMonth = t3.ProductionMonth
INNER JOIN Allocations al
ON t3.Well_in_CO2PAT_API = al.ChildPID
AND t3.Other_Pattern = al.PID
WHERE t3.Other_Pattern <> "PAT 28-5A"
And t3.Other_Pattern <>"PAT 56-6A"
AND t3.Other_Pattern_as_Well_API Is Null
AND al.Date=#11/1/1948#
GROUP BY q3.Well_In_CO2PAT, t3.Well_in_CO2PAT_API, t3.ProductionMonth,
t3.Other_Pattern, t3.Other_Pattern_as_Well, t3.Other_Pattern_as_Well_API, t3.3mo_rbi,
q3.SumOf3mo_rbi, IIf(SumOf3mo_rbi=0,al.Factor_2,3mo_rbi/SumOf3mo_rbi;
That said, I can't see anything wrong with that SQL. Can you tell us what data is being
included or excluded that should not be?
On 10 Dec 2019 at 14:51, Kaup, Chester wrote:
> Here is the whole query. Thanks for looking at this
>
> SELECT [qry 3 Months Running Injection Data Totals].Well_In_CO2PAT,
> [tbl 3 Month Running Inj Data].Well_in_CO2PAT_API, [tbl 3 Month
> Running Inj Data].ProductionMonth, [tbl 3 Month Running Inj
> Data].Other_Pattern, [tbl 3 Month Running Inj
> Data].Other_Pattern_as_Well, [tbl 3 Month Running Inj
> Data].Other_Pattern_as_Well_API, [tbl 3 Month Running Inj
> Data].[3mo_rbi], [qry 3 Months Running Injection Data
> Totals].SumOf3mo_rbi,
> IIf([SumOf3mo_rbi]=0,Allocations.Factor_2,[3mo_rbi]/[SumOf3mo_rbi]) AS
> Factor_2 INTO [tbl 3 Month Factor 2] FROM ([qry 3 Months Running
> Injection Data Totals] INNER JOIN [tbl 3 Month Running Inj Data] ON
> ([qry 3 Months Running Injection Data Totals].Well_in_CO2PAT_API =
> [tbl 3 Month Running Inj Data].Well_in_CO2PAT_API) AND ([qry 3 Months
> Running Injection Data Totals].ProductionMonth = [tbl 3 Month Running
> Inj Data].ProductionMonth)) INNER JOIN Allocations ON ([tbl 3 Month
> Running Inj Data].Well_in_CO2PAT_API = Allocations.ChildPID) AND ([tbl
> 3 Month Running Inj Data].Other_Pattern = Allocations.PID) WHERE [tbl
> 3 Month Running Inj Data].Other_Pattern<>"PAT 28-5A" And [tbl 3 Month
> Running Inj Data].Other_Pattern<>"PAT 56-6A" AND [tbl 3 Month Running
> Inj Data].Other_Pattern_as_Well_API Is Null AND
> Allocations.Date=#11/1/1948# GROUP BY [qry 3 Months Running Injection
> Data Totals].Well_In_CO2PAT, [tbl 3 Month Running Inj
> Data].Well_in_CO2PAT_API, [tbl 3 Month Running Inj
> Data].ProductionMonth, [tbl 3 Month Running Inj Data].Other_Pattern,
> [tbl 3 Month Running Inj Data].Other_Pattern_as_Well, [tbl 3 Month
> Running Inj Data].Other_Pattern_as_Well_API, [tbl 3 Month Running Inj
> Data].[3mo_rbi], [qry 3 Months Running Injection Data
> Totals].SumOf3mo_rbi,
> IIf([SumOf3mo_rbi]=0,Allocations.Factor_2,[3mo_rbi]/[SumOf3mo_rbi]);
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
More information about the AccessD
mailing list