RE: Need to add 4 buckets to this Stored Proc

This looks like TSQL syntax, so midrange-L is not exactly the appropriate
forum for it.

In any case, why not create a wrapper stored procedure that calls this SP
twice in succession? 2nd call would simply subtract 1 year from the input
start and end dates.
Stored procedure can return more than result set.
It seems like that would be the least work.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Adam West
Sent: Friday, July 24, 2009 3:53 PM
To: Midrange Systems Technical Discussion
Subject: Need to add 4 buckets to this Stored Proc

HI I am having difficulty making an important addition to this Stored
Procedure. The following code created 4 buckets for sales sum for a current
passed parameter month, and then for 3 months prior to this passed month.
Now, they would like to see the same 4 months for one year prior.
CREATE PROCEDURE [dbo].[spu_CRSS_Sectionly]
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@Customer VARCHAR(20)
AS

SET NOCOUNT ON

DECLARE @STARTDATE1 DATETIME
DECLARE @STARTDATE2 DATETIME
DECLARE @STARTDATE3 DATETIME
DECLARE @STARTDATE4 DATETIME

--- SET STARTDATES
SET @STARTDATE1 = DateAdd (m , -1 , @EndDate +1)
SET @STARTDATE2 = DateAdd (m , -2 , @EndDate +1 )
SET @STARTDATE3 = DateAdd (m , -3 , @EndDate +1 )
SET @STARTDATE4 = DateAdd (m , -4 , @EndDate +1 )

SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' AS source
INTO #temp

FROM sop10200 AS SOP10200
INNER JOIN
iv00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN
sop10100 AS SOP10100
ON SOP10200.SOPTYPE = SOP10100.SOPTYPE
AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE
INNER JOIN
iv40600 AS IV40600
ON IV00101.ITMGEDSC = IV40600.USCATVAL
INNER JOIN
iv40600 AS CATS
ON IV00101.USCATVLS_2 = CATS.USCATVAL
INNER JOIN
rm00101 AS RM00101
ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR
WHERE (DOCDATE BETWEEN @StartDate4 AND @EndDate)
OR
(DOCDATE BETWEEN @StartDate AND @EndDate)
UNION ALL
SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,
SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' AS source
FROM sop30300 AS SOP30300
LEFT OUTER JOIN
iv00101 AS IV00101
ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN
sop30200 AS SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE
AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
LEFT OUTER JOIN
iv40600 AS IV40600
ON IV00101.ITMGEDSC = IV40600.USCATVAL
LEFT OUTER JOIN
iv40600 AS CATS
ON IV00101.USCATVLS_2 = CATS.USCATVAL
INNER JOIN
rm00101 AS RM00101
ON SOP30200.CUSTNMBR = RM00101.CUSTNMBR
WHERE (DOCDATE BETWEEN @StartDate4 AND @EndDate)
OR
(DOCDATE BETWEEN @StartDate AND @EndDate)


SELECT
CustName,
ItemDesc,
CAST(OXTNDPRC AS FLOAT) AS OXTNDPRC,
CAST(Quantity AS FLOAT) AS QUANTITY,
DOCDATE,
SOURCE,
ITEMNMBR,
CPRCSTNM,
CAST(0.0 AS FLOAT) AS BILLED,
CAST(0.0 AS FLOAT) AS BILLED1,
CAST(0.0 AS FLOAT) AS BILLED2,
CAST(0.0 AS FLOAT) AS BILLED3,
CAST(0.0 AS FLOAT) AS BILLED4,
CAST(0.0 AS FLOAT) AS CENSUS,
CAST(0.0 AS FLOAT) AS CENSUS1,
CAST(0.0 AS FLOAT) AS CENSUS2,
CAST(0.0 AS FLOAT) AS CENSUS3,
CAST(0.0 AS FLOAT) AS CENSUS4,
CAST(0.0 AS FLOAT) AS PPD1,
CAST(0.0 AS FLOAT) AS PPD2,
CAST(0.0 AS FLOAT) AS PPD3,
CAST(0.0 AS FLOAT) AS PPD4,
CAST(0.0 AS FLOAT) AS QUOTED,
CAST(0.0 AS FLOAT) AS QUOTED1,
CAST(0.0 AS FLOAT) AS QUOTED2,
CAST(0.0 AS FLOAT) AS QUOTED3,
CAST(0.0 AS FLOAT) AS QUOTED4,
CAST(0.0 AS FLOAT) AS LTOT
INTO #FinalReport
FROM #temp
WHERE (CPRCSTNM = @Customer) OR (@Customer IS NULL) OR (@Customer='')


UPDATE #FinalReport
SET BILLED = CASE WHEN SOURCE = 'History' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET BILLED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN BILLED
ELSE 0 END,
BILLED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN
BILLED ELSE 0 END,
BILLED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN
BILLED ELSE 0 END,
BILLED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN
BILLED ELSE 0 END

UPDATE #FinalReport
SET CENSUS = CASE WHEN SOURCE='History' THEN Quantity ELSE 0 END

UPDATE #FinalReport
SET CENSUS1 = CASE WHEN DOCDATE BETWEEN @StartDate1 AND @EndDate THEN CENSUS
ELSE 0 END,
CENSUS2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN
CENSUS ELSE 0 END,
CENSUS3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN
CENSUS ELSE 0 END,
CENSUS4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN
CENSUS ELSE 0 END

UPDATE #FinalReport
SET QUOTED = CASE WHEN SOURCE = 'current' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET QUOTED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN QUOTED
ELSE 0 END,
QUOTED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN
QUOTED ELSE 0 END,
QUOTED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN
QUOTED ELSE 0 END,
QUOTED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN
QUOTED ELSE 0 END

--- SUMMARY FRO PPD Calculation
SELECT CUSTNAME,
CPRCSTNM,
MAX(CENSUS1) AS CENSUS1,
MAX(BILLED1) AS BILLED1,
MAX(CENSUS2) AS CENSUS2,
MAX(BILLED2) AS BILLED2,
MAX(CENSUS3) AS CENSUS3,
MAX(BILLED3) AS BILLED3,
MAX(CENSUS4) AS CENSUS4,
MAX(BILLED4) AS BILLED4,
SUM(QUOTED1) AS QUOTED1,
SUM(QUOTED2) AS QUOTED2,
SUM(QUOTED3) AS QUOTED3,
SUM(QUOTED4) AS QUOTED4,
CAST(0 AS FLOAT) AS PPD1,
CAST(0 AS FLOAT) AS PPD2,
CAST(0 AS FLOAT) AS PPD3,
CAST(0 AS FLOAT) AS PPD4,
CAST(0 AS FLOAT) AS LTOT
INTO #SUMMARY
FROM #FinalReport
GROUP BY
CUSTNAME, CPRCSTNM

UPDATE #SUMMARY
SET PPD1 = CASE WHEN CENSUS1 = 0 THEN 0 ELSE BILLED1/CENSUS1 END

UPDATE #SUMMARY
SET PPD2 = CASE WHEN CENSUS2 = 0 THEN 0 ELSE BILLED2/CENSUS2 END

UPDATE #SUMMARY
SET PPD3 = CASE WHEN CENSUS3 = 0 THEN 0 ELSE BILLED3/CENSUS3 END

UPDATE #SUMMARY
SET PPD4 = CASE WHEN CENSUS4=0 THEN 0 ELSE BILLED4/CENSUS4 END

UPDATE #SUMMARY
SET LTOT = QUOTED1 + QUOTED2 + QUOTED3 + QUOTED4 + BILLED1 + BILLED2 +
BILLED3 + BILLED4

SELECT * FROM #SUMMARY

DROP TABLE #temp
DROP TABLE #FinalReport
DROP TABLE #SUMMARY
GO