ALTER PROCEDURE [dbo].[GenYearlySummary]
@Year INT,
@State VARCHAR(8) = NULL,
@FWC1 VARCHAR(8) = NULL,
@FWC2 VARCHAR(8) = NULL,
@FWC3 VARCHAR(8) = NULL,
@FWC4 VARCHAR(8) = NULL,
@ContainerTypeCode VARCHAR(3) = NULL,
@ManagementMethodCode VARCHAR(5) = NULL,
@GenName VARCHAR(255) = NULL
AS
BEGIN
SET NOCOUNT ON;
;WITH LineMatch AS
(
SELECT
ID, -- unique ManifestDetail Line ID
GENERATOR_ID,
FEDERAL_WASTE_CODES,
CONTAINER_TYPE_CODE,
MANAGEMENT_METHOD_CODE,
QUANTITY_HAZ_KG
FROM dbo.vwManifestLines as l
WHERE SHIPPED_YEAR = @Year
AND (@State IS NULL OR GENERATOR_LOCATION_STATE = @State)
AND (@GenName IS NULL OR GENERATOR_NAME LIKE '%' + @GenName + '%')
AND (@ContainerTypeCode IS NULL OR CONTAINER_TYPE_CODE = @ContainerTypeCode)
AND (@ManagementMethodCode IS NULL OR MANAGEMENT_METHOD_CODE = @ManagementMethodCode)
AND (@FWC1 IS NULL OR EXISTS (SELECT 1 FROM dbo.vwManifestLinesFWC f WHERE f.ID = l.ID AND f.FederalWasteCode = @FWC1))
AND (@FWC2 IS NULL OR EXISTS (SELECT 1 FROM dbo.vwManifestLinesFWC f WHERE f.ID = l.ID AND f.FederalWasteCode = @FWC2))
AND (@FWC3 IS NULL OR EXISTS (SELECT 1 FROM dbo.vwManifestLinesFWC f WHERE f.ID = l.ID AND f.FederalWasteCode = @FWC3))
AND (@FWC4 IS NULL OR EXISTS (SELECT 1 FROM dbo.vwManifestLinesFWC f WHERE f.ID = l.ID AND f.FederalWasteCode = @FWC4))
),
Totals AS
(
SELECT
GENERATOR_ID,
FEDERAL_WASTE_CODES,
CONTAINER_TYPE_CODE,
MANAGEMENT_METHOD_CODE,
SUM(QUANTITY_HAZ_KG) AS TotalKg,
COUNT(*) AS ManifestLineCount
FROM LineMatch
GROUP BY
GENERATOR_ID,
FEDERAL_WASTE_CODES,
CONTAINER_TYPE_CODE,
MANAGEMENT_METHOD_CODE
)
SELECT
CAST(@Year AS SMALLINT) AS SHIPPED_YEAR,
t.FEDERAL_WASTE_CODES,
t.MANAGEMENT_METHOD_CODE,
t.CONTAINER_TYPE_CODE,
ISNULL(t.TotalKg,0) AS TotalKg,
t.ManifestLineCount,
t.GENERATOR_ID,
g.GENERATOR_NAME,
g.GENERATOR_LOCATION_STATE,
g.GENERATOR_LOCATION_STREET_NO,
g.GENERATOR_LOCATION_STREET1,
g.GENERATOR_LOCATION_CITY,
g.GENERATOR_LOCATION_ZIP
FROM Totals t
INNER JOIN dbo.tblGenerators g
ON t.GENERATOR_ID = g.GENERATOR_ID;
END
