-- File: Queries.SQL
-- Auth: OGSYS\Will Darnell
-- Date: 11-APR-2005
--=================================================================================================

-- Ex01 - Simple Table Dump
-- Results: ALL Owners
SELECT * FROM Owner
---------------------------------------------------------------------------------------------------

-- Ex02 - Table Dump with Columns Specified
-- Results: The columns Code, Name, Print_1099 and FID for ALL Owners
SELECT Code, [Name], Print_1099, FID FROM Owner
---------------------------------------------------------------------------------------------------

-- Ex03 - Example of WHERE Clause (Equality)
-- Results: List of Owners who will receive a 1099
SELECT Code, [Name], Print_1099, FID
FROM Owner
WHERE
	Print_1099 = 'Y'
---------------------------------------------------------------------------------------------------

-- Ex04 - Example of WHERE Clause (Inequality)
-- Results: List of Owners who have a non-blank FID
SELECT Code, [Name], Print_1099, FID
FROM Owner
WHERE
	 FID != ''
	-- FID <> ''
---------------------------------------------------------------------------------------------------

-- Ex05 - Example of WHERE Clause with more than one qualifier, bound by AND
-- Results: List of Owners who will get a 1099 and have a blank FID
SELECT Code, [Name], Print_1099, FID
FROM Owner
WHERE
	FID = '' AND
	Print_1099 = 'Y'
---------------------------------------------------------------------------------------------------

-- Ex06 - Example of WHERE Clause with more than one qualifier, bound by OR
-- Results: List of Vendors that are designated as receieving 1099 with OTHER INCOME or ROYALTIES
SELECT *
FROM Vendor
WHERE
	Payment_Type = 'OTHER INCOME' OR
	Payment_Type = 'ROYALTIES'
---------------------------------------------------------------------------------------------------

-- Ex07 - Example of WHERE Clause with IN clause
-- Results: Same as Ex06
SELECT *
FROM Vendor
WHERE
	Payment_Type IN ('OTHER INCOME', 'ROYALTIES')
---------------------------------------------------------------------------------------------------

-- Ex08 - Example of proper syntax while dealing with all three major data types
-- Results: All Journal Entries for Jul 29th for Main Account 101 that have a negative net value
SELECT * 
FROM GL_Detail 
WHERE
	Main_Account = '  101' AND
	Journal_Date = '29-JUL-1999 00:00:00' AND
	Net_Value < 0
---------------------------------------------------------------------------------------------------	

-- Ex09 - Example of querying for values that are NULL 
-- Results: All Unclosed Journal Entries
SELECT *
FROM GL_Detail
WHERE
	Accrual_Date IS NULL
---------------------------------------------------------------------------------------------------

-- Ex10 - Example of querying for values that are NOT NULL
-- Results: All Closed Jorunal Entries
SELECT *
FROM GL_Detail
WHERE
	Accrual_Date IS NOT NULL
---------------------------------------------------------------------------------------------------

-- Ex11 - Example of inline mathematics
-- Results: Owner Revenue Detail and Price Per Unit for Well TX3000
SELECT *, Net_Value / Net_Volume AS Price
FROM Owner_Revenue_Detail
WHERE
	Well_Code = 'TX3000'
---------------------------------------------------------------------------------------------------

-- Ex12 - Example of Divide by Zero Error
-- Results: Error!
SELECT *, Net_Value / Net_Volume AS Price
FROM Owner_Revenue_Detail
---------------------------------------------------------------------------------------------------

-- Ex13 - One way to handle Divide by Zero
-- Results: Owner Revenue Detail and Price for ALL entries
SELECT	*,
	Price = 
	CASE Net_Volume
		WHEN 0 THEN 0
		ELSE Net_Value / Net_Volume
	END
FROM Owner_Revenue_Detail
---------------------------------------------------------------------------------------------------

-- Ex14 - Ordering Results with the ORDER BY Clause
-- Results: All Owner Revenue Detail sorted by Production Year and Production Month
SELECT *
FROM Owner_Revenue_Detail
ORDER BY
	Production_Year, Production_Month
---------------------------------------------------------------------------------------------------

-- Ex15 - Ordering Results with the ORDER BY Clause, Specifying Sort Order
-- Results: All Owner Revenue Detail sorted in DESCENDING order
SELECT *
FROM Owner_Revenue_Detail
ORDER BY
	Production_Year DESC, Production_Month DESC
---------------------------------------------------------------------------------------------------

-- Ex16 - Example of the SUM Aggregate Function
-- Results: Total Activity (Journal Basis) for Co:100, Account 101/2 on Jan 25th 1999
SELECT SUM(Net_Value) AS Total
FROM GL_Detail WHERE
	Company_Code = '         100' AND
	Main_Account = '  101' AND
	Sub_Account = '  2' AND
	Journal_Date = '25-JAN-1999 00:00:00'
---------------------------------------------------------------------------------------------------

-- Ex17 - Example of the SUM Aggregate Function when used with a GROUP BY Clause
-- Results: By Account, Sub_Account total of Activity for Company 100 on 25-Jan-1999
SELECT Main_Account, Sub_Account, SUM(Net_Value) AS Total
FROM GL_Detail
WHERE
	Company_Code = '         100' AND
	Journal_Date = '25-JAN-1999 00:00:00'
GROUP BY Main_Account, Sub_Account
---------------------------------------------------------------------------------------------------

-- Ex18 - Example of the COUNT Aggregate Function
-- Results: Number of records in the GL_Detail Table
SELECT COUNT(*) 
FROM GL_Detail
---------------------------------------------------------------------------------------------------

-- Ex19 - Example of an INNER JOIN between the Account and GL_Detail tables
-- Results: All G/L entries joind with the account table
SELECT * 
FROM Account ACCT INNER JOIN GL_Detail GLD ON
	ACCT.Main_Account = GLD.Main_Account AND
	ACCT.Sub_Account = GLD.Sub_Account
---------------------------------------------------------------------------------------------------

-- Ex20 - Example of a LEFT OUTER JOIN between the Owner and GL_Detail tables
-- Results: All G/L entries joined with the Owner table
SELECT *
FROM GL_Detail GLD LEFT OUTER JOIN Owner OW ON
	GLD.Entity_Type = 'O' AND
	GLD.Entity_Code = OW.Code
---------------------------------------------------------------------------------------------------

-- Ex21 - Example of a Trial Balance in SQL
-- Results: Same as a 212-01 report for Journal Basis, Net Values only
SELECT
	GLB.Main_Account, GLB.Sub_Account, ACCT.[Name], SUM(Month_12_Ending_Value) AS Amount
FROM GL_Balance GLB INNER JOIN Account ACCT ON
	GLB.Main_Account = ACCT.Main_Account AND
	GLB.Sub_Account = ACCT.Sub_Account
WHERE
	GLB.Company_Code = '         100' AND
	Basis = 'J' AND
	Fiscal_Year = 1999 AND
	Has_Net_Amounts = 'Y'
GROUP BY GLB.Main_Account, GLB.Sub_Account, ACCT.Name
ORDER BY GLB.Main_Account, GLB.Sub_Account
---------------------------------------------------------------------------------------------------

-- Ex22 - Example of a Trial Balance in SQL
-- Results: Same as a 212-01 report for Journal Basis, Values AND Volumes
DECLARE @COMP CHAR(12)	SET @COMP = '         200'
DECLARE @BASIS CHAR	SET @BASIS = 'J'
DECLARE @FYEAR INTEGER	SET @FYEAR = 1999
DECLARE @NETA CHAR	SET @NETA = 'Y'
SELECT
	ACCT.Main_Account, ACCT.Sub_Account, ACCT.[Name],
	QVolumes.Volume, QValues.Amount
FROM Account ACCT LEFT OUTER JOIN
(SELECT GLB.Main_Account, GLB.Sub_Account, SUM(Month_12_Ending_Value) AS Amount
 FROM GL_Balance GLB
 WHERE
	GLB.Company_Code = @COMP AND
	GLB.Basis = @BASIS AND
	GLB.Fiscal_Year = @FYEAR AND
	GLB.Has_Net_Amounts = @NETA
 GROUP BY GLB.Main_Account, GLB.Sub_Account) AS QValues ON
	ACCT.Main_Account = QValues.Main_Account AND
	ACCT.Sub_Account = QValues.Sub_Account
LEFT OUTER JOIN
(SELECT GLV.Main_Account, GLV.Sub_Account, SUM(Month_12_Ending_Volume) AS Volume
 FROM GL_Balance_Volume GLV
 WHERE
	GLV.Company_Code = @COMP AND
	GLV.Basis = @BASIS AND
	GLV.Fiscal_Year = @FYEAR AND
	GLV.Has_Net_Amounts = @NETA
 GROUP BY GLV.Main_Account, GLV.Sub_Account) AS QVolumes ON
	ACCT.Main_Account = QVolumes.Main_Account AND
	ACCT.Sub_Account = QVolumes.Sub_Account
WHERE
	QValues.Amount != 0 OR QVolumes.Volume != 0
ORDER BY ACCT.Main_Account, ACCT.Sub_Account
---------------------------------------------------------------------------------------------------