Re: Need to add 4 buckets to this Stored Proc -- MIDRANGE-L

Hi Adam,

I've not done any work with datetime function on iSeries DB2 but i'd hazard
a guess that you need to make a change something like the following:
--- SET STARTDATES
SET @STARTDATE1 = DateAdd (m , *-13 *, @EndDate +1)
SET @STARTDATE2 = DateAdd (m ,* -14*, @EndDate +1 )
SET @STARTDATE3 = DateAdd (m , *-15*, @EndDate +1 )
SET @STARTDATE4 = DateAdd (m , *-16*, @EndDate +1 )

Or is 13 and on not recognised as valid 'month' selections?

In which case you might need to experiment with subtracting a year from the
@STARTDATE1 and then using the DateAdd(m, -1...)

Good luck

Paul

2009/7/25 Adam West <adamster@xxxxxxxxx>

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.