adding previous months actual sales SBO query

[ebie]

Dear all

we have a query to manage our budgets etc code below, we use SAP 9.3 64 bit

i need assistance to add the previous months actual sales into it but do not know how to go about it.

please see query below which i need to add the previous months actual sales in.

any assistance would be appreciated

CREATE TABLE #BUDGET (CARDCODE VARCHAR(100)

,CARDFNAME VARCHAR(200)

,BUDGET_SALES FLOAT

,BUDGET_GP FLOAT)

CREATE TABLE #SALES (CARDCODE VARCHAR(100)

,CARDFNAME VARCHAR(200)

,AC_Status VARCHAR(100)

,Available_Balance int

,SALES_EMP VARCHAR(100)

,ACT_SALES FLOAT

,ACT_GP FLOAT)

CREATE TABLE #SALES_TOTAL (CARDCODE VARCHAR(100)

,CARDFNAME VARCHAR(200)

,AC_Status VARCHAR(100)

,Available_Balance int

,SALES_EMP VARCHAR(50)

,ACT_SALES FLOAT

,ACT_GP FLOAT)

INSERT INTO #BUDGET

SELECT distinct BK_OFCT.CODE

,BK_OFCT.NAME

,SUM(CASE WHEN BK_FCT1.ITEMCODE = 'SALES BUDGET' THEN BK_FCT1.QUANTITY ELSE 0 END) AS SALES

,SUM(CASE WHEN BK_FCT1.ITEMCODE = 'GP BUDGET' THEN BK_FCT1.QUANTITY ELSE 0 END) AS GP

FROM FCT1 BK_FCT1

INNER JOIN OFCT BK_OFCT ON BK_FCT1.ABSID = BK_OFCT.ABSID

WHERE BK_FCT1.DATE >= [%0] AND BK_FCT1.DATE <= [%1]

GROUP BY BK_OFCT.CODE, BK_OFCT.NAME

INSERT INTO #SALES

SELECT distinct BK_OCRD.CARDCODE, BK_OCRD.CARDFNAME

, CASE

WHEN bk_ocrd.FrozenFor = 'Y' THEN '***Account On Hold***'

WHEN bk_ocrd.FrozenFor = 'N' THEN 'Active'

ELSE 'Error'

END AS 'AC_Status'

,max((bk_ocrd.Balance + bk_ocrd.dnotesbal + bk_ocrd.ordersbal) - bk_ocrd.CreditLine) as 'Available_Balance'

,BK_OSLP.SLPNAME AS SALES_EMP

,SUM(BK_OINV.DOCTOTAL- BK_OINV.VATSUM) as SALES

,SUM(BK_OINV.GROSPROFIT) AS GP

FROM OINV BK_OINV

INNER JOIN OCRD BK_OCRD ON BK_OINV.CARDCODE = BK_OCRD.CARDCODE

INNER JOIN OSLP BK_OSLP ON BK_OCRD.SLPCODE = BK_OSLP.SLPCODE

WHERE BK_OINV.DOCDATE >=[%0] AND BK_OINV.DOCDATE<=[%1]

GROUP BY (BK_OCRD.CARDCODE), BK_OCRD.CARDFNAME, BK_OSLP.SLPNAME, bk_ocrd.FrozenFor, bk_ocrd.Balance, bk_ocrd.dnotesbal, bk_ocrd.ordersbal, bk_ocrd.CreditLine

UNION ALL

SELECT distinct (bk_OCRD.CARDCODE), BK_OCRD.CARDFNAME

,CASE

WHEN bk_ocrd.FrozenFor = 'Y' THEN '***Account On Hold***'

WHEN bk_ocrd.FrozenFor = 'N' THEN 'Active'

ELSE 'Error'

END AS 'AC_Status'

,max((bk_ocrd.Balance + bk_ocrd.dnotesbal + bk_ocrd.ordersbal) - bk_ocrd.CreditLine) as 'Available_Balance'

,BK_OSLP.SLPNAME

,SUM(BK_ORIN.DOCTOTAL- BK_ORIN.VATSUM) * -1 as SALES

,SUM(BK_ORIN.GROSPROFIT) * -1 AS GP

FROM ORIN BK_ORIN

INNER JOIN OCRD BK_OCRD ON BK_ORIN.CARDCODE = BK_OCRD.CARDCODE

INNER JOIN OSLP BK_OSLP ON BK_OCRD.SLPCODE = BK_OSLP.SLPCODE

WHERE BK_ORIN.DOCDATE >=[%0] AND BK_ORIN.DOCDATE<=[%1]

GROUP BY BK_OCRD.CARDCODE, BK_OCRD.CARDFNAME, BK_OSLP.SLPNAME, bk_ocrd.FrozenFor, bk_ocrd.Balance, bk_ocrd.dnotesbal, bk_ocrd.ordersbal, bk_ocrd.CreditLine

UNION ALL

SELECT distinct BK_OCRD.CARDCODE, BK_OCRD.CARDFNAME

,CASE

WHEN bk_ocrd.FrozenFor = 'Y' THEN '***Account On Hold***'

WHEN bk_ocrd.FrozenFor = 'N' THEN 'Active'

ELSE 'Error'

END AS 'AC_Status'

,max((bk_ocrd.Balance + bk_ocrd.dnotesbal + bk_ocrd.ordersbal) - bk_ocrd.CreditLine) as 'Available_Balance'

,BK_OSLP.SLPNAME

,0

,0

FROM OCRD BK_OCRD

INNER JOIN OSLP BK_OSLP ON BK_OCRD.SLPCODE = BK_OSLP.SLPCODE

WHERE BK_OCRD.CARDTYPE = 'C'

Group by bk_ocrd.cardcode, BK_OCRD.CARDFNAME, bk_ocrd.FrozenFor, BK_OSLP.SLPNAME, bk_ocrd.FrozenFor, bk_ocrd.Balance, bk_ocrd.dnotesbal, bk_ocrd.ordersbal, bk_ocrd.CreditLine

INSERT INTO #SALES_TOTAL

SELECT distinct CARDCODE, CARDFNAME

,AC_Status

,max(Available_Balance)

,SALES_EMP

,SUM(ACT_SALES)

,SUM(ACT_GP)

FROM #SALES

GROUP BY CARDCODE, CARDFNAME, SALES_EMP, AC_Status, Available_Balance

SELECT distinct ISNULL(#BUDGET.CARDCODE,#SALES_TOTAL.CARDCODE) AS CARDCODE

,ISNULL(#BUDGET.CARDFNAME,#SALES_TOTAL.CARDFNAME) AS CARDFNAME

,#SALES_TOTAL.AC_Status

,max(isnull(#Sales_Total.Available_Balance,0)) as 'Available_Balance'

,SUM(ISNULL(#SALES_TOTAL.ACT_SALES,0)) AS ACT_SALES

,MAX(ISNULL(#BUDGET.BUDGET_SALES,0)) AS BUDGET_SALES

,SUM(ISNULL(#SALES_TOTAL.ACT_SALES,0)) - MAX(ISNULL(#BUDGET.BUDGET_SALES,0)) AS SALES_VARIANCE

,SUM(ISNULL(#SALES_TOTAL.ACT_GP,0)) AS ACT_GP

,MAX(ISNULL(#BUDGET.BUDGET_GP,0)) AS BUDGET_GP

,SUM(ISNULL(#SALES_TOTAL.ACT_GP,0)) - MAX(ISNULL(#BUDGET.BUDGET_GP,0)) AS GP_VARIANCE

,SUM(CASE WHEN ISNULL(#SALES_TOTAL.ACT_SALES,0) <> 0 THEN ISNULL(#SALES_TOTAL.ACT_GP,0)/ISNULL(#SALES_TOTAL.ACT_SALES,0) ELSE 0 END)*100 AS [ACT_GP%]

,MAX(CASE WHEN ISNULL(#BUDGET.BUDGET_SALES,0) <> 0 THEN ISNULL(#BUDGET.BUDGET_GP,0)/ISNULL(#BUDGET.BUDGET_SALES,0) ELSE 0 END)*100 AS [BUDGET_GP%]

,SUM((CASE WHEN ISNULL(#SALES_TOTAL.ACT_SALES,0) <> 0 THEN ISNULL(#SALES_TOTAL.ACT_GP,0)*100/ISNULL(#SALES_TOTAL.ACT_SALES,0) ELSE 0 END)) -

MAX((CASE WHEN ISNULL(#BUDGET.BUDGET_SALES,0) <> 0 THEN ISNULL(#BUDGET.BUDGET_GP,0)*100/ISNULL(#BUDGET.BUDGET_SALES,0) ELSE 0 END)) AS [GP%_VARIANCE]

FROM #SALES_TOTAL

LEFT OUTER JOIN #BUDGET ON #SALES_TOTAL.CARDCODE = #BUDGET.CARDCODE

WHERE #SALES_TOTAL.SALES_EMP = 'CISCO CORREIA' and (#SALES_TOTAL.ACT_SALES <> '0' or #BUDGET.BUDGET_SALES > '0')

GROUP BY ISNULL(#BUDGET.CARDCODE,#SALES_TOTAL.CARDCODE), ISNULL(#BUDGET.CARDFNAME,#SALES_TOTAL.CARDFNAME), #SALES_TOTAL.AC_Status , #Sales_Total.Available_Balance

ORDER BY ISNULL(#BUDGET.CARDFNAME,#SALES_TOTAL.CARDFNAME)

DROP TABLE #BUDGET

DROP TABLE #SALES

DROP TABLE #SALES_TOTAL