Complicated SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Complicated SQL Query

Author

Topic

tscofield
Starting Member

3 Posts

Posted - 2008-05-07 : 14:08:07

Please, I need help getting a 30 day average and a daily value in one row.
And to make it complicated there are 3 levels of categories that I need this data for.
Example:
Level 1= BUnit Level 2= Field Level 3 = well
A BUnit can have multiple fields and a field can have multiple wells.
BUnit is the top level and well is the lowest level.
I need a 30 day average for each BUnit, a 30 day average for each Field within a BUnit, and a 30 day average for each Well with in a Field that is within a BUnit.
Here is the output I want:
(30 day Average = 4/1/2008 – 4/30/2008)
Level_______oil amount_____date___Average____Variance
BUnit -A ________14 _____ 4/30/2008 ___ 15 _________-1
___Field 1 ________7 ______4/30/2008 ____6__________ 1
_____ Well a ______3_______4/30/2008 ____2__________1
_____ well b ______4_______4/30/2008_____4__________0
___Field 2_________7______4/30/2008_____9__________-2
______well a_______2______4/30/2008_____3__________0
______well b_______3______4/30/2008_____2__________1
______well c_______2______4/30/2008_____4__________-2
BUnit-B ___________20_____ 4/30/2008 ___ 17__________6
___Field 1_________12______4/30/2008 ____10__________2
______Well a_______5_______4/30/2008_____4__________1
______well b_______7_______4/30/2008_____6__________1
___Field 2________ 8______4/30/2008 ____7___________1
______well a_______2_______4/30/2008_____2__________0
______well b_______3_______4/30/2008_____1__________1
______well c_______3_______4/30/2008_____4__________-1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 14:15:29

How is your source data now?Plaese post some sample

tscofield
Starting Member

3 Posts

Posted - 2008-05-07 : 14:26:46

Here is code to get the data for a single day.

Declare @date as char(10)
set @date= '5/2/2008'

SELECT
rtrim(dbo.wells.userfield10) as BusinessUnit, RTrim(dbo.Leases.field) as Field, RTrim(dbo.wells.wellname) as WellName, dbo.Wells.ReportingNRI,
dbo.Well_Summary_Activity.Date as ActDate, dbo.wells.fieldNameID As FieldNameID,

dbo.Well_Summary_Activity.AllocOil AS AllocOil,
dbo.Well_Summary_Activity.AllocGas AS AllocGas,
(dbo.Well_Summary_Activity.AllocOil * 6 )+ dbo.Well_Summary_Activity.AllocGas as GrossActEquiv,

dbo.Well_Summary_Activity.AllocOil * dbo.Wells.ReportingNRI as NetOil,
dbo.Well_Summary_Activity.AllocGas * dbo.Wells.ReportingNRI AS NetGas,
(((dbo.well_summary_activity.AllocOil * dbo.wells.ReportingNRI) * 6) + (dbo.well_summary_activity.AllocGas * dbo.wells.ReportingNRI)) as NetEquivActual

FROM dbo.Wells INNER JOIN
dbo.Well_Summary_Activity ON dbo.Wells.WellID = dbo.Well_Summary_Activity.WellID INNER JOIN
dbo.Leases ON dbo.Wells.LeaseID = dbo.Leases.LeaseID INNER JOIN
dbo.Well_Summary ON dbo.Well_Summary_Activity.WellID = dbo.Well_Summary.WellID AND
dbo.Well_Summary_Activity.YearMonth = dbo.Well_Summary.YearMonth

WHERE (dbo.Well_Summary_Activity.Date = DATEADD(Day, - 2, @Date) ) and

(dbo.Leases.FacilityType = 'Lease') AND (dbo.Leases.ClientID <= 2) AND (NOT (dbo.Wells.UserField10 IS NULL))

tscofield
Starting Member

3 Posts

Posted - 2008-05-07 : 20:15:35

Let me simplify my quesion.
Below is a query that works perfect (in report services) to get 30 days of data along with a count to calculate the average. You’ll notice that I commented out one of two ‘where clauses’. The only difference between the ‘where clauses’ is the date range verses a single date. If I use the second ‘where clause’ and remove the ‘sum’ function, I get a single day of data then I have what I need for the other part of my report. I need all this data on one row for each well name.
Every time I try to add a nested select or a case to get data for a single date I get the error for not including it in the group clause or for returning more than one row. How can I get a single days data for each well included in the query below?????

Basically I need an aggregate query and a not aggregate query to work together to produce data on a row together for each well.
Like this: Business Unit
----Field
-----Well ----- NetEquiv data for 5/2/2008 ------ average NetEquiv for 30 days data

declare @date as char(10)
set @Date = '5/2/2008'

SELECT dbo.Wells.UserField10, dbo.Leases.Field, dbo.Wells.WellName, COUNT(DISTINCT dbo.Wells.WellName) AS WellCount,
SUM(dbo.Well_Summary_Activity.AllocOil * dbo.Wells.ReportingNRI * 6 + dbo.Well_Summary_Activity.AllocGas * dbo.Wells.ReportingNRI)
AS netEquivActual
FROM dbo.Wells INNER JOIN
dbo.Well_Summary_Activity ON dbo.Wells.WellID = dbo.Well_Summary_Activity.WellID INNER JOIN
dbo.Leases ON dbo.Wells.LeaseID = dbo.Leases.LeaseID INNER JOIN
dbo.Well_Summary ON dbo.Well_Summary_Activity.WellID = dbo.Well_Summary.WellID AND
dbo.Well_Summary_Activity.YearMonth = dbo.Well_Summary.YearMonth
WHERE (dbo.Well_Summary_Activity.Date >= DATEADD(Day, - 30, @Date)) AND (dbo.Well_Summary_Activity.Date <= DATEADD(Day, - 2, @Date)) AND
--WHERE (dbo.Well_Summary_Activity.Date = DATEADD(Day, - 2, @Date)) AND
(dbo.Leases.FacilityType = 'Lease') AND (dbo.Leases.ClientID <= 2) AND (NOT (dbo.Wells.UserField10 IS NULL))
GROUP BY dbo.Wells.UserField10, dbo.Leases.Field, dbo.Wells.WellName
ORDER BY dbo.Wells.UserField10, dbo.Leases.Field, dbo.Wells.WellName

Thanks

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-08 : 11:46:49

Why don't you seperate the queries and make one a derived table. Then join the first query with the derived table for your additional data.

This is a quick and dirty solution and may not be optimal, but I'm just glancing at the problem.

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources