Dynamic SQL exceeds 7500 chars

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SQL exceeds 7500 chars

Author

Topic

Raj_Mahendran
Starting Member

14 Posts

Posted - 2008-08-11 : 12:16:03

Hi,

I am creating an SP which inturn generates a dynamic SQL for reporting. Unfortunately, when i print the dynamic SQL only half of the SQL statements comeup. Could someone let me know how do I handle this?

Previously I was assigning the DSQL to a variable of varchar(8000) then realized I can also directly run the SQL using exec ('dynamic select.....'). To verify my SQL statements, I want to print the DSQL. So guru's help me out.

Environment: SQL 2000

If there is no way, then I need to split the SP which is time consuming.

Thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 12:28:49

1) got a lot of useless white-space?

2) Made use of short aliases for tables in joins?

3) Got rid of useless INNER / OUTER from JOIN and LEFT / RIGHT JOIN syntax.

Without posting your query, there's not much we can specifically advise.

Charlie.

-------------
Charlie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-11 : 12:39:27

Can you imagine the parse time on this?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 13:00:24

Post your current query if you need more help on this.

Raj_Mahendran
Starting Member

14 Posts

Posted - 2008-08-11 : 13:07:13

I know all of you might run away..

ALTER procedure [dbo].[GME_SUMRY_RPT_FNL]
(
@PC_Product_Group varchar(200),
@Zones varchar(50),
@Forecast_Month varchar(100),
@Forecast_Year varchar(5),
@Submission_Dt varchar(20)
)

AS
declare @rptWherelist varchar(8000)
declare @Wherelist1 varchar(8000)
declare @Wherelist2 varchar(8000)
declare @Wherelist3 varchar(8000)
declare @rptWherepart varchar(400)
declare @rptTouple varchar(100)
declare @rptwhere1 varchar(50)
declare @rptwhere2 varchar(50)
declare @rptfirst int
declare @rptZone varchar(20)
declare @rptWhereZone varchar(200)
declare @rptWhereZonePart varchar(200)
declare @ssql varchar(8000)
declare @prior_submission_dt varchar(20)

BEGIN

TRUNCATE TABLE [dbo].[TGME_DATE]

INSERT INTO [dbo].[TGME_DATE]
VALUES (cast(@Submission_Dt as datetime))

set @prior_submission_dt = convert(varchar(20),cast(@Submission_Dt as datetime)-7,101)
set @rptWherelist = ''
set @rptFirst=0
while @PC_Product_Group <> ''
begin

set @rptTouple = substring(@PC_Product_Group,1,charindex(',',@PC_Product_Group,1)-1)

set @rptWhere1 = substring(@rptTouple,1,charindex('|',@rptTouple,1)-1)

set @rptWhere2 = substring(@rptTouple,charindex('|',@rptTouple,1)+1,len(@rptTouple))

Set @rptWherepart = '(V1.Profit_Center_CD =''' + @rptWhere1 + ''' and V1.Product_GRP_CD= ''' + @rptWhere2 + ''') '
Set @Wherelist1 = '(B.Profit_Center_CD =''' + @rptWhere1 + ''' and B.Product_GRP_CD= ''' + @rptWhere2 + ''') '
Set @Wherelist2 = '(F.Profit_Center_CD =''' + @rptWhere1 + ''' and F.Product_GRP_CD= ''' + @rptWhere2 + ''') '
Set @Wherelist3 = '(Profit_Center_CD =''' + @rptWhere1 + ''' and Product_GRP_CD= ''' + @rptWhere2 + ''') '

if @rptfirst = 0
begin
Set @rptWherelist = '(' + @rptWherepart
end
else
begin
Set @rptWherelist = @rptWherelist + ' or ' + @rptWherepart
end
set @PC_Product_Group = substring(@PC_Product_Group,charindex(',',@PC_Product_Group,1)+1,Len(@PC_Product_Group))
Set @rptfirst = @rptfirst + 1
end

set @rptWhereZone=') and v1.zone_CD in (' + @Zones + ')'

Set @rptWherelist = @rptWherelist + '' + @rptWhereZone + ' and V1.SUBMISSION_DT =''' + @Submission_Dt + ''' AND V2.SUBMISSION_DT =''' + convert(varchar(20),cast(@Submission_Dt as datetime)-7,101) + ''' and V1.Forecast_Mnth IN ( ' + @Forecast_Month + ') and V1.Forecast_Year= ' + @Forecast_Year

--DATA FOR CURRENT WEEK SUBMISSION DATE

--TRUNCATE TABLE ##TGME_CURR_WEEK_RPT

print
'

CREATE TABLE ##TGME_CRD_REGION_RPT
(
[ZONE_NM] [varchar](100) NULL,
[ZONE_CD] [varchar](5) NULL,
[CRD_REGION_CD] [varchar](5) NULL,
[CRD_REGION_NM] [varchar](100) NULL,
[SUBMISSION_DT] [datetime] NULL,
[BUDGET_GPW] [money] NULL,
[FORECAST_GPW] [money] NULL,
[BUDGET_NPW] [money] NULL,
[FORECAST_NPW] [money] NULL,
[GPW_BUDGET_VARIANCE] [money] NULL,
[GPW_BUDGET_VARIANCE_PCT] [numeric](18, 2) NULL,
[NPW_BUDGET_VARIANCE] [money] NULL,
[NPW_BUDGET_VARIANCE_PCT] [numeric](18, 2) NULL,
[PRIOR_FCAST_GPW_AM] [money] NULL,
[PRIOR_FCAST_GPW_VARIANCE] [money] NULL,
[PRIOR_FCAST_GPW_AM_PCT] [numeric](18, 2) NULL,
[PRIOR_FCAST_NPW_AM] [money] NULL,
[PRIOR_FCAST_NPW_VARIANCE] [money] NULL,
[PRIOR_FCAST_NPW_AM_PCT] [numeric](18, 2) NULL,
[NO_OF_ACCTS] [int] NULL,
[ENG_FEES] [money] NULL,
[SALES_PIPELINE_GPW] [money] NULL,
[SALES_PIPELINE_NPW] [money] NULL,
[ACTUALS_GPW_AMT] [money] NULL,
[PRIOR_ACTUALS_GPW_VARIANCE] [numeric](18, 2) NULL,
[PRIOR_ACTUALS_GPW_VARIANCE_PCT] [float] NULL,
[ACTUALS_NPW_AMT] [money] NULL,
[PRIOR_ACTUALS_NPW_VARIANCE] [numeric](18, 2) NULL,
[PRIOR_ACTUALS_NPW_VARIANCE_PCT] [float] NULL,
[AMT_FROM] [varchar](50)
)

CREATE TABLE ##TGME_CURR_WEEK_RPT
(
[ZONE_NM] [varchar](100) NULL,
[ZONE_CD] [varchar](5) NULL,
[CRD_REGION_CD] [varchar](5) NULL,
[CRD_REGION_NM] [varchar](100) NULL,
[SUBMISSION_DT] [datetime] NULL,
[FORECAST_MONTH] [varchar](20) NULL,
[FORECAST_YEAR] [int] NULL,
[PROFIT_CENTER_CD] [varchar](5) NULL,
[PROFIT_CENTER_NM] [varchar](100) NULL,
[PRODUCT_GRP_CD] [varchar](5) NULL,
[PRODUCT_GRP_NM] [varchar](100) NULL,
[BUDGET_GPW] [money] NULL,
[FORECAST_GPW] [money] NULL,
[BUDGET_NPW] [money] NULL,
[FORECAST_NPW] [money] NULL,
[NO_OF_ACCTS] [int] NULL,
[ENG_FEES] [money] NULL,
[SALES_PIPELINE_GPW] [money] NULL,
[SALES_PIPELINE_NPW] [money] NULL,
[ACTUALS_GPW_AMT] [money] NULL,
[PRIOR_ACTUALS_GPW_VARIANCE] [numeric](18, 2) NULL,
[PRIOR_ACTUALS_GPW_VARIANCE_PCT] [float] NULL,
[ACTUALS_NPW_AMT] [money] NULL,
[PRIOR_ACTUALS_NPW_VARIANCE] [numeric](18, 2) NULL,
[PRIOR_ACTUALS_NPW_VARIANCE_PCT] [float] NULL,
[AMT_FROM] [varchar](50)
)

CREATE TABLE ##TGME_PRIOR_WEEK_RPT
(
[ZONE_NM] [varchar](100) NULL,
[ZONE_CD] [varchar](5) NULL,
[CRD_REGION_CD] [varchar](5) NULL,
[CRD_REGION_NM] [varchar](100) NULL,
[SUBMISSION_DT] [datetime] NULL,
[FORECAST_MONTH] [varchar](20) NULL,
[FORECAST_YEAR] [int] NULL,
[PROFIT_CENTER_CD] [varchar](5) NULL,
[PROFIT_CENTER_NM] [varchar](100) NULL,
[PRODUCT_GRP_CD] [varchar](5) NULL,
[PRODUCT_GRP_NM] [varchar](100) NULL,
[BUDGET_GPW] [money] NULL,
[FORECAST_GPW] [money] NULL,
[BUDGET_NPW] [money] NULL,
[FORECAST_NPW] [money] NULL,
[ACTUALS_GPW_AMT] [money] NULL,
[ACTUALS_NPW_AMT] [money] NULL,
[AMT_FROM] [varchar](50)
)

INSERT INTO [##TGME_CURR_WEEK_RPT]

(
[ZONE_NM] ,
[ZONE_CD],
[CRD_REGION_CD] ,
[CRD_REGION_NM] ,
[SUBMISSION_DT] ,
[FORECAST_MONTH],
[FORECAST_YEAR] ,
[PROFIT_CENTER_CD],
[PROFIT_CENTER_NM],
[PRODUCT_GRP_CD],
[PRODUCT_GRP_NM],
[FORECAST_GPW],
[FORECAST_NPW]
)

SELECT Z.ZONE_NM, R.ZONE_CD, B.REGION_CD, R.REGION_NM, B.SUBMISSION_DT, B.MONTH_NM, B.YEAR_NO,
B.PROFIT_CENTER_CD, PC.PROFIT_CENTER_NM, B.PRODUCT_GRP_CD, PG.PRODUCT_GRP_NM, B.FORECAST_GPW, B.FORECAST_NPW

FROM

(SELECT F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT, SUM(A1.FORECAST_GPW) AS FORECAST_GPW, SUM(A1.FORECAST_NPW) AS FORECAST_NPW
FROM TGME_FORECASTS F
INNER JOIN
(SELECT FORECAST_ID, Credited_Region_CD, DIVISION_NO, SUM(GPW_AM) AS FORECAST_GPW,
SUM(NPW_AM) AS FORECAST_NPW FROM TGME_ACCOUNTS
WHERE PROBABILITY_TO_BIND_PCT >= .7
GROUP BY FORECAST_ID, Credited_Region_CD, DIVISION_NO) A1
ON F.FORECAST_ID = A1.FORECAST_ID
--PARAMETER
WHERE
F.SUBMISSION_DT = ''' + @Submission_Dt + ''' GROUP BY F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT
) B

INNER JOIN TGME_PROFIT_CENTER PC ON
B.PROFIT_CENTER_CD = PC.PROFIT_CENTER_CD

INNER JOIN dbo.TGME_PRODUCT_GRP PG ON
B.PRODUCT_GRP_CD = PG.PRODUCT_GRP_CD

INNER JOIN TGME_REGION R ON
R.REGION_CD = B.REGION_CD

INNER JOIN TGME_ZONE Z ON
R.ZONE_CD = Z.ZONE_CD

--PARAMETER WHERELIST1
WHERE
R.ZONE_CD IN ( ' + @Zones + ' ) AND ' + @WHERELIST1 + '

--UPDATE BUDGET GPW / NPW

UPDATE ##TGME_CURR_WEEK_RPT
SET
BUDGET_GPW = BGT.GPW,
BUDGET_NPW = BGT.NPW
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN TGME_BUDGET BGT
ON
BGT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
BGT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
BGT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
BGT.MONTH_NM = CWR.FORECAST_MONTH AND
BGT.YEAR_NO = CWR.FORECAST_YEAR

--CALCULATE FORECAST NPW/GPW IF ACTUALS ARE FOUND, THEN ITS ACTUALS, ELSE IF FORECAST ARE FOUND THEN FORECAST ELSE IF
--BUDGET IS FOUND THEN BUDGET AS PER THE ORDER.

--FIND BUDGET

UPDATE ##TGME_CURR_WEEK_RPT
SET
FORECAST_GPW = BGT.GPW,
FORECAST_NPW = BGT.NPW,
AMT_FROM = ''BUDGET''
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN TGME_BUDGET BGT
ON
BGT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
BGT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
BGT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
BGT.MONTH_NM = CWR.FORECAST_MONTH AND
BGT.YEAR_NO = CWR.FORECAST_YEAR

--FIND FORECAST AND OVER WRITE THE BUDGET

UPDATE ##TGME_CURR_WEEK_RPT
SET
FORECAST_GPW = C.FORECAST_GPW,
FORECAST_NPW = C.FORECAST_NPW,
AMT_FROM = ''FORECAST''
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN
(SELECT F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT, SUM(A1.FORECAST_GPW) AS FORECAST_GPW, SUM(A1.FORECAST_NPW) AS FORECAST_NPW
FROM TGME_FORECASTS F
INNER JOIN
(SELECT FORECAST_ID, Credited_Region_CD, DIVISION_NO, SUM(GPW_AM) AS FORECAST_GPW,
SUM(NPW_AM) AS FORECAST_NPW FROM TGME_ACCOUNTS
WHERE PROBABILITY_TO_BIND_PCT >= .7
GROUP BY FORECAST_ID, Credited_Region_CD, DIVISION_NO) A1
ON F.FORECAST_ID = A1.FORECAST_ID
--PARAMETER
WHERE
F.SUBMISSION_DT = ''' + @Submission_Dt + '''
GROUP BY F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT
) C
ON
C.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
C.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
C.REGION_CD = CWR.CRD_REGION_CD AND
C.MONTH_NM = CWR.FORECAST_MONTH AND
C.SUBMISSION_DT = CWR.SUBMISSION_DT AND
C.YEAR_NO = CWR.FORECAST_YEAR

--FIND ACTUALS AND OVER WRITE THE FORECAST

UPDATE ##TGME_CURR_WEEK_RPT
SET
FORECAST_GPW = ACT.GPW_AM,
FORECAST_NPW = ACT.NPW_AM,
AMT_FROM = ''ACTUALS''
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN TGME_ACTUALS ACT
ON
ACT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
ACT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
ACT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
ACT.MONTH_NM = CWR.FORECAST_MONTH AND
ACT.YEAR_NO = CWR.FORECAST_YEAR

--UPDATE NO OF ACCTS, ENG FEE, SALE PIPELINE GPW, SALES PIPELINE NPW

UPDATE ##TGME_CURR_WEEK_RPT
SET
NO_OF_ACCTS = D.NO_OF_ACCTS,
ENG_FEES = D.ENG_FEES,
SALES_PIPELINE_GPW = D.SALES_PIPELINE_GPW,
SALES_PIPELINE_NPW = D.SALES_PIPELINE_NPW
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN
(
SELECT
L1.PROFIT_CENTER_CD,
L1.SUBMISSION_DT,
L1.MONTH_NM, L1.YEAR_NO, L1.PRODUCT_GRP_CD, L1.CREDITED_REGION_CD, L1.DIVISION_NO,
SUM(L1.NO_OF_ACCTS) AS NO_OF_ACCTS, SUM(L1.ENG_FEE) AS ENG_FEES,
SUM(L1.SALES_PIPELINE_GPW) AS SALES_PIPELINE_GPW,
SUM(L1.SALES_PIPELINE_NPW) AS SALES_PIPELINE_NPW

FROM

(SELECT
F.PROFIT_CENTER_CD,
F.SUBMISSION_DT,
F.MONTH_NM, F.YEAR_NO, F.PRODUCT_GRP_CD, A1.CREDITED_REGION_CD, A1.DIVISION_NO,
A1.NO_OF_ACCTS,
A1.ENG_FEE,
A1.SALES_PIPELINE_GPW, A1.SALES_PIPELINE_NPW
FROM
(
SELECT * FROM TGME_FORECASTS
) F
INNER JOIN
(SELECT FORECAST_ID, Credited_Region_CD, DIVISION_NO, COUNT(*) AS NO_OF_ACCTS,
SUM(Eng_Fee_AM) AS ENG_FEE, SUM(GPW_AM) AS SALES_PIPELINE_GPW, SUM(NPW_AM) AS SALES_PIPELINE_NPW
FROM TGME_ACCOUNTS
WHERE PROBABILITY_TO_BIND_PCT < .7
GROUP BY FORECAST_ID, Credited_Region_CD, DIVISION_NO
) A1
ON F.FORECAST_ID = A1.FORECAST_ID
--PARAMETER WHERELIST2
WHERE
F.SUBMISSION_DT = ''' + @Submission_Dt + ''' AND ' + @WHERELIST2 + '
) L1
GROUP BY L1.PROFIT_CENTER_CD,
L1.SUBMISSION_DT,
L1.MONTH_NM, L1.YEAR_NO, L1.PRODUCT_GRP_CD, L1.CREDITED_REGION_CD, L1.DIVISION_NO
) D
ON
D.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
D.SUBMISSION_DT = CWR.SUBMISSION_DT AND
D.MONTH_NM = CWR.FORECAST_MONTH AND
D.YEAR_NO = CWR.FORECAST_YEAR AND
D.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
D.CREDITED_REGION_CD = CWR.CRD_REGION_CD
WHERE CWR.[AMT_FROM] = ''FORECAST''

UPDATE ##TGME_CURR_WEEK_RPT
SET
ACTUALS_GPW_AMT = E.ACTUALS_GPW_AM,
ACTUALS_NPW_AMT = E.ACTUALS_NPW_AM
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN
(
select [Profit_Center_CD]
,[Product_Grp_CD]
,CREDITED_REGION_CD
,[Division_NO]
,[Month_NM]
,[Year_NO]
,SUM(GPW_AM) AS ACTUALS_GPW_AM, SUM(NPW_AM) AS ACTUALS_NPW_AM from TGME_Actuals

--PARAMETER WHERELIST3
where ' + @WHERELIST3 + '
GROUP BY [Profit_Center_CD]
,[Product_Grp_CD]
,CREDITED_REGION_CD
,[Division_NO]
,[Month_NM]
,[Year_NO]
) E

ON
E.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
E.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
E.MONTH_NM = CWR.FORECAST_MONTH AND
E.YEAR_NO = CWR.FORECAST_YEAR-1 AND
E.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD

--PRIOR WEEK

INSERT INTO [##TGME_PRIOR_WEEK_RPT]

(
[ZONE_NM] ,
[ZONE_CD],
[CRD_REGION_CD] ,
[CRD_REGION_NM] ,
[SUBMISSION_DT] ,
[FORECAST_MONTH],
[FORECAST_YEAR] ,
[PROFIT_CENTER_CD],
[PROFIT_CENTER_NM],
[PRODUCT_GRP_CD],
[PRODUCT_GRP_NM],
[FORECAST_GPW],
[FORECAST_NPW]
)

SELECT Z.ZONE_NM, R.ZONE_CD, B.REGION_CD, R.REGION_NM, B.SUBMISSION_DT, B.MONTH_NM, B.YEAR_NO,
B.PROFIT_CENTER_CD, PC.PROFIT_CENTER_NM, B.PRODUCT_GRP_CD, PG.PRODUCT_GRP_NM, B.FORECAST_GPW, B.FORECAST_NPW

FROM

(SELECT F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT, SUM(A1.FORECAST_GPW) AS FORECAST_GPW, SUM(A1.FORECAST_NPW) AS FORECAST_NPW
FROM TGME_FORECASTS F
INNER JOIN
(SELECT FORECAST_ID, Credited_Region_CD, DIVISION_NO, SUM(GPW_AM) AS FORECAST_GPW,
SUM(NPW_AM) AS FORECAST_NPW FROM TGME_ACCOUNTS
WHERE PROBABILITY_TO_BIND_PCT >= .7
GROUP BY FORECAST_ID, Credited_Region_CD, DIVISION_NO) A1
ON F.FORECAST_ID = A1.FORECAST_ID
--PARAMETER
WHERE
F.SUBMISSION_DT = ''' + @prior_submission_dt + '''
GROUP BY F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT
) B

INNER JOIN TGME_PROFIT_CENTER PC ON
B.PROFIT_CENTER_CD = PC.PROFIT_CENTER_CD

INNER JOIN dbo.TGME_PRODUCT_GRP PG ON
B.PRODUCT_GRP_CD = PG.PRODUCT_GRP_CD

INNER JOIN TGME_REGION R ON
R.REGION_CD = B.REGION_CD

INNER JOIN TGME_ZONE Z ON
R.ZONE_CD = Z.ZONE_CD

--PARAMETER WHERELIST1
WHERE
R.ZONE_CD IN ( ' + @Zones + ' ) AND ' + @WHERELIST1 + '

--UPDATE BUDGET GPW / NPW

UPDATE ##TGME_PRIOR_WEEK_RPT
SET
BUDGET_GPW = BGT.GPW,
BUDGET_NPW = BGT.NPW
FROM ##TGME_PRIOR_WEEK_RPT CWR
INNER JOIN TGME_BUDGET BGT
ON
BGT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
BGT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
BGT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
BGT.MONTH_NM = CWR.FORECAST_MONTH AND
BGT.YEAR_NO = CWR.FORECAST_YEAR

--CALCULATE FORECAST NPW/GPW IF ACTUALS ARE FOUND, THEN ITS ACTUALS, ELSE IF FORECAST ARE FOUND THEN FORECAST ELSE IF
--BUDGET IS FOUND THEN BUDGET AS PER THE ORDER.

--FIND BUDGET

UPDATE ##TGME_PRIOR_WEEK_RPT
SET
FORECAST_GPW = BGT.GPW,
FORECAST_NPW = BGT.NPW,
AMT_FROM = ''BUDGET''
FROM ##TGME_PRIOR_WEEK_RPT CWR
INNER JOIN TGME_BUDGET BGT
ON
BGT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
BGT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
BGT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
BGT.MONTH_NM = CWR.FORECAST_MONTH AND
BGT.YEAR_NO = CWR.FORECAST_YEAR

--FIND FORECAST AND OVER WRITE THE BUDGET

UPDATE ##TGME_PRIOR_WEEK_RPT
SET
FORECAST_GPW = C.FORECAST_GPW,
FORECAST_NPW = C.FORECAST_NPW,
AMT_FROM = ''FORECAST''
FROM ##TGME_PRIOR_WEEK_RPT CWR
INNER JOIN
(SELECT F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT, SUM(A1.FORECAST_GPW) AS FORECAST_GPW, SUM(A1.FORECAST_NPW) AS FORECAST_NPW
FROM TGME_FORECASTS F
INNER JOIN
(SELECT FORECAST_ID, Credited_Region_CD, DIVISION_NO, SUM(GPW_AM) AS FORECAST_GPW,
SUM(NPW_AM) AS FORECAST_NPW FROM TGME_ACCOUNTS
WHERE PROBABILITY_TO_BIND_PCT >= .7
GROUP BY FORECAST_ID, Credited_Region_CD, DIVISION_NO) A1
ON F.FORECAST_ID = A1.FORECAST_ID
--PARAMETER
WHERE
F.SUBMISSION_DT = ''' + @prior_submission_dt + '''
GROUP BY F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT
) C
ON
C.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
C.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
C.REGION_CD = CWR.CRD_REGION_CD AND
C.MONTH_NM = CWR.FORECAST_MONTH AND
C.SUBMISSION_DT = CWR.SUBMISSION_DT AND
C.YEAR_NO = CWR.FORECAST_YEAR

--FIND ACTUALS AND OVER WRITE THE FORECAST

UPDATE ##TGME_PRIOR_WEEK_RPT
SET
FORECAST_GPW = ACT.GPW_AM,
FORECAST_NPW = ACT.NPW_AM,
AMT_FROM = ''ACTUALS''
FROM ##TGME_PRIOR_WEEK_RPT CWR
INNER JOIN TGME_ACTUALS ACT
ON
ACT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
ACT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
ACT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
ACT.MONTH_NM = CWR.FORECAST_MONTH AND
ACT.YEAR_NO = CWR.FORECAST_YEAR

--PRIOR WEEK ENDS

--CALCULATIONS FOR VARIANCES

INSERT INTO ##TGME_CRD_REGION_RPT (
[ZONE_NM], [ZONE_CD], [CRD_REGION_CD], [CRD_REGION_NM], [SUBMISSION_DT], [BUDGET_GPW], [FORECAST_GPW],
[BUDGET_NPW], [FORECAST_NPW], [NO_OF_ACCTS], [ENG_FEES], [SALES_PIPELINE_GPW], [SALES_PIPELINE_NPW],
[ACTUALS_GPW_AMT],[ACTUALS_NPW_AMT])
SELECT ZONE_NM, ZONE_CD, CRD_REGION_CD, CRD_REGION_NM, SUBMISSION_DT, SUM(BUDGET_GPW), SUM(FORECAST_GPW),
SUM(BUDGET_NPW), SUM(FORECAST_NPW), SUM(NO_OF_ACCTS), SUM(ENG_FEES), SUM(SALES_PIPELINE_GPW), SUM(SALES_PIPELINE_NPW),
SUM(ACTUALS_GPW_AMT), SUM(ACTUALS_NPW_AMT) FROM ##TGME_CURR_WEEK_RPT
GROUP BY ZONE_NM, ZONE_CD, CRD_REGION_CD, CRD_REGION_NM, SUBMISSION_DT

--CALCULATE GPW_BUDGET_VARIANCE, GPW_BUDGET_VARIANCE_PCT, NPW_BUDGET_VARIANCE, NPW_BUDGET_VARIANCE_PCT

UPDATE ##TGME_CRD_REGION_RPT
SET
GPW_BUDGET_VARIANCE = FORECAST_GPW - BUDGET_GPW,
GPW_BUDGET_VARIANCE_PCT = (FORECAST_GPW - BUDGET_GPW)/BUDGET_GPW,
NPW_BUDGET_VARIANCE = FORECAST_NPW - BUDGET_NPW,
NPW_BUDGET_VARIANCE_PCT = (FORECAST_NPW - BUDGET_NPW)/BUDGET_NPW

UPDATE ##TGME_CRD_REGION_RPT
SET
PRIOR_FCAST_GPW_AM = P.PRIOR_FORECAST_GPW,
PRIOR_FCAST_NPW_AM = P.PRIOR_FORECAST_NPW
FROM ##TGME_CRD_REGION_RPT R
INNER JOIN

(SELECT ZONE_NM, ZONE_CD, CRD_REGION_CD, CRD_REGION_NM, SUBMISSION_DT, SUM(BUDGET_GPW) AS PRIOR_BUDGET_GPW,
SUM(FORECAST_GPW) AS PRIOR_FORECAST_GPW,
SUM(BUDGET_NPW) AS PRIOR_BUDGET_NPW, SUM(FORECAST_NPW) AS PRIOR_FORECAST_NPW FROM ##TGME_PRIOR_WEEK_RPT
GROUP BY ZONE_NM, ZONE_CD, CRD_REGION_CD, CRD_REGION_NM, SUBMISSION_DT) P
ON
R.ZONE_CD = P.ZONE_CD AND
R.CRD_REGION_CD = P.CRD_REGION_CD

UPDATE ##TGME_CRD_REGION_RPT
SET
PRIOR_FCAST_GPW_VARIANCE = CASE WHEN PRIOR_FCAST_GPW_AM = 0 OR PRIOR_FCAST_GPW_AM IS NULL THEN FORECAST_GPW ELSE FORECAST_GPW - PRIOR_FCAST_GPW_AM END,
PRIOR_FCAST_NPW_VARIANCE = CASE WHEN PRIOR_FCAST_NPW_AM = 0 OR PRIOR_FCAST_NPW_AM IS NULL THEN FORECAST_NPW ELSE FORECAST_NPW - PRIOR_FCAST_NPW_AM END,
PRIOR_FCAST_GPW_AM_PCT = CASE WHEN PRIOR_FCAST_GPW_AM = 0 OR PRIOR_FCAST_GPW_AM IS NULL THEN 0 ELSE (FORECAST_GPW - PRIOR_FCAST_GPW_AM) / PRIOR_FCAST_GPW_AM END,
PRIOR_FCAST_NPW_AM_PCT = CASE WHEN PRIOR_FCAST_NPW_AM = 0 OR PRIOR_FCAST_NPW_AM IS NULL THEN 0 ELSE (FORECAST_NPW - PRIOR_FCAST_NPW_AM) / PRIOR_FCAST_NPW_AM END,
PRIOR_ACTUALS_GPW_VARIANCE = CASE WHEN ACTUALS_GPW_AMT = 0 OR ACTUALS_GPW_AMT IS NULL THEN FORECAST_GPW ELSE FORECAST_GPW - ACTUALS_GPW_AMT END,
PRIOR_ACTUALS_NPW_VARIANCE = CASE WHEN ACTUALS_NPW_AMT = 0 OR ACTUALS_NPW_AMT IS NULL THEN FORECAST_NPW ELSE FORECAST_NPW - ACTUALS_NPW_AMT END,
PRIOR_ACTUALS_GPW_VARIANCE_PCT = CASE WHEN ACTUALS_GPW_AMT = 0 OR ACTUALS_GPW_AMT IS NULL THEN 0 ELSE (FORECAST_GPW - ACTUALS_GPW_AMT) / ACTUALS_GPW_AMT END,
PRIOR_ACTUALS_NPW_VARIANCE_PCT = CASE WHEN ACTUALS_NPW_AMT = 0 OR ACTUALS_NPW_AMT IS NULL THEN 0 ELSE (FORECAST_NPW - ACTUALS_NPW_AMT) / ACTUALS_NPW_AMT END

--SELECT * FROM ##TGME_CURR_WEEK_RPT
--SELECT * FROM ##TGME_PRIOR_WEEK_RPT
SELECT * FROM ##TGME_CRD_REGION_RPT '

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 13:17:03

Can you explain why you're using dynamic sql for the second part of query? what are conditions under which your where list will vary?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 13:25:31

And I doubt that your query looks like that in Query Anaylser.

Can you wrap it in some

tags?

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 13:29:08

And I doubt that your query looks like that in Query Anaylser.

Can you wrap it in some

tags?

Off topic sugestion 1) Why are you using global temp tables for this - If you create a local temp table, all dynamic sql in your procedure will still be able to access it due to the scope. Otherwise there's no way 2 copies of this sp can run at the same time on the same server.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 13:31:35

Sorry "[" + "quote" + "]" tags.

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 13:43:19

quote:
Originally posted by Transact Charlie

Sorry "[" + "quote" + "]" tags.

-------------
Charlie

i also made that mistake once

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-11 : 14:03:21

Use code tags, not quote tags.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Raj_Mahendran
Starting Member

14 Posts

Posted - 2008-08-11 : 14:05:39

I could have used local temp tables instead of global temp tables. The functionality of the SP is simple. It fectches records with this week's submission date and last week submission date and then compares forecast value of this week with the forecast for the last week. So I created curr week table and prior week table and creating a final table. To avoid confusion and save time, Please dont deviate with the syntax of the SP. All I need is to print or execute the full dynamic SQL. When run individualy they work fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 14:12:16

Ok. Have you tried printing out length of dynamic string . what does it return?

SELECT LEN(@yoursqlstring)

Raj_Mahendran
Starting Member

14 Posts

Posted - 2008-08-11 : 14:32:17

LENGTH RETURNED 8000 but am sure its more than that.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-11 : 14:37:13

You could split the string up into multiple varchar(8000) variables, however you won't be able to execute them in the end. You are going to need to come up with a different solution.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Raj_Mahendran
Starting Member

14 Posts

Posted - 2008-08-11 : 15:43:55

Thank you all, I realized that one of the ways to handle exceeding dynamic SQL is to split the SQL into different SPs and use global temp tables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-11 : 15:45:55

Do you understand the pitfalls of global temporary tables? Will there ever be more than one person executing this code at any given time?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-12 : 12:03:00

quote:

You could split the string up into multiple varchar(8000) variables, however you won't be able to execute them in the end. You are going to need to come up with a different solution.

Hi Tkizer, Raj_Mahendran

I think there *is* a way to do this as sp_executeSql takes a NTEXT parameter. It's not very pretty though....

Check out
http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong

for more information. (and in fact I recommend this guide by Erland Sommarskog as vital dynamic sql reading for everyone.

Raj, I'd copy each block of dynamic sql into a text editor to find out exactly how long each is. I recommend notepad++ (search for this) as a fantastic code editor. Just select all the text and it will show you how many characters it is.

You might find that you have a lot of whitespace that you can remove.

Tkizer, Yeah I goofed up with the whole "[" + "quote" + "]" thing. Raj, I meant wrap your code in "[" + "code" + "]" <code> "[/" + "code" + "]" tags as this would preserve your formatting (which is a concern with dynamic sql).

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-12 : 12:06:02

[code]
ALTER procedure [dbo].[GME_SUMRY_RPT_FNL]
(
@PC_Product_Group varchar(200),
@Zones varchar(50),
@Forecast_Month varchar(100),
@Forecast_Year varchar(5),
@Submission_Dt varchar(20)
)

AS
declare @rptWherelist varchar(8000)
declare @Wherelist1 varchar(8000)
declare @Wherelist2 varchar(8000)
declare @Wherelist3 varchar(8000)
declare @rptWherepart varchar(400)
declare @rptTouple varchar(100)
declare @rptwhere1 varchar(50)
declare @rptwhere2 varchar(50)
declare @rptfirst int
declare @rptZone varchar(20)
declare @rptWhereZone varchar(200)
declare @rptWhereZonePart varchar(200)
declare @ssql varchar(8000)
declare @prior_submission_dt varchar(20)

BEGIN

TRUNCATE TABLE [dbo].[TGME_DATE]

INSERT INTO [dbo].[TGME_DATE]
VALUES (cast(@Submission_Dt as datetime))

set @prior_submission_dt = convert(varchar(20),cast(@Submission_Dt as datetime)-7,101)
set @rptWherelist = ''
set @rptFirst=0
while @PC_Product_Group <> ''
begin

set @rptTouple = substring(@PC_Product_Group,1,charindex(',',@PC_Product_Group,1)-1)

set @rptWhere1 = substring(@rptTouple,1,charindex('|',@rptTouple,1)-1)

set @rptWhere2 = substring(@rptTouple,charindex('|',@rptTouple,1)+1,len(@rptTouple))

Set @rptWherepart = '(V1.Profit_Center_CD =''' + @rptWhere1 + ''' and V1.Product_GRP_CD= ''' + @rptWhere2 + ''') '
Set @Wherelist1 = '(B.Profit_Center_CD =''' + @rptWhere1 + ''' and B.Product_GRP_CD= ''' + @rptWhere2 + ''') '
Set @Wherelist2 = '(F.Profit_Center_CD =''' + @rptWhere1 + ''' and F.Product_GRP_CD= ''' + @rptWhere2 + ''') '
Set @Wherelist3 = '(Profit_Center_CD =''' + @rptWhere1 + ''' and Product_GRP_CD= ''' + @rptWhere2 + ''') '

if @rptfirst = 0
begin
Set @rptWherelist = '(' + @rptWherepart
end
else
begin
Set @rptWherelist = @rptWherelist + ' or ' + @rptWherepart
end
set @PC_Product_Group = substring(@PC_Product_Group,charindex(',',@PC_Product_Group,1)+1,Len(@PC_Product_Group))
Set @rptfirst = @rptfirst + 1
end

set @rptWhereZone=') and v1.zone_CD in (' + @Zones + ')'

Set @rptWherelist = @rptWherelist + '' + @rptWhereZone + ' and V1.SUBMISSION_DT =''' + @Submission_Dt + ''' AND V2.SUBMISSION_DT =''' + convert(varchar(20),cast(@Submission_Dt as datetime)-7,101) + ''' and V1.Forecast_Mnth IN ( ' + @Forecast_Month + ') and V1.Forecast_Year= ' + @Forecast_Year

--DATA FOR CURRENT WEEK SUBMISSION DATE

--TRUNCATE TABLE ##TGME_CURR_WEEK_RPT

print
'

CREATE TABLE ##TGME_CRD_REGION_RPT
(
[ZONE_NM] [varchar](100) NULL,
[ZONE_CD] [varchar](5) NULL,
[CRD_REGION_CD] [varchar](5) NULL,
[CRD_REGION_NM] [varchar](100) NULL,
[SUBMISSION_DT] [datetime] NULL,
[BUDGET_GPW] [money] NULL,
[FORECAST_GPW] [money] NULL,
[BUDGET_NPW] [money] NULL,
[FORECAST_NPW] [money] NULL,
[GPW_BUDGET_VARIANCE] [money] NULL,
[GPW_BUDGET_VARIANCE_PCT] [numeric](18, 2) NULL,
[NPW_BUDGET_VARIANCE] [money] NULL,
[NPW_BUDGET_VARIANCE_PCT] [numeric](18, 2) NULL,
[PRIOR_FCAST_GPW_AM] [money] NULL,
[PRIOR_FCAST_GPW_VARIANCE] [money] NULL,
[PRIOR_FCAST_GPW_AM_PCT] [numeric](18, 2) NULL,
[PRIOR_FCAST_NPW_AM] [money] NULL,
[PRIOR_FCAST_NPW_VARIANCE] [money] NULL,
[PRIOR_FCAST_NPW_AM_PCT] [numeric](18, 2) NULL,
[NO_OF_ACCTS] [int] NULL,
[ENG_FEES] [money] NULL,
[SALES_PIPELINE_GPW] [money] NULL,
[SALES_PIPELINE_NPW] [money] NULL,
[ACTUALS_GPW_AMT] [money] NULL,
[PRIOR_ACTUALS_GPW_VARIANCE] [numeric](18, 2) NULL,
[PRIOR_ACTUALS_GPW_VARIANCE_PCT] [float] NULL,
[ACTUALS_NPW_AMT] [money] NULL,
[PRIOR_ACTUALS_NPW_VARIANCE] [numeric](18, 2) NULL,
[PRIOR_ACTUALS_NPW_VARIANCE_PCT] [float] NULL,
[AMT_FROM] [varchar](50)
)

CREATE TABLE ##TGME_CURR_WEEK_RPT
(
[ZONE_NM] [varchar](100) NULL,
[ZONE_CD] [varchar](5) NULL,
[CRD_REGION_CD] [varchar](5) NULL,
[CRD_REGION_NM] [varchar](100) NULL,
[SUBMISSION_DT] [datetime] NULL,
[FORECAST_MONTH] [varchar](20) NULL,
[FORECAST_YEAR] [int] NULL,
[PROFIT_CENTER_CD] [varchar](5) NULL,
[PROFIT_CENTER_NM] [varchar](100) NULL,
[PRODUCT_GRP_CD] [varchar](5) NULL,
[PRODUCT_GRP_NM] [varchar](100) NULL,
[BUDGET_GPW] [money] NULL,
[FORECAST_GPW] [money] NULL,
[BUDGET_NPW] [money] NULL,
[FORECAST_NPW] [money] NULL,
[NO_OF_ACCTS] [int] NULL,
[ENG_FEES] [money] NULL,
[SALES_PIPELINE_GPW] [money] NULL,
[SALES_PIPELINE_NPW] [money] NULL,
[ACTUALS_GPW_AMT] [money] NULL,
[PRIOR_ACTUALS_GPW_VARIANCE] [numeric](18, 2) NULL,
[PRIOR_ACTUALS_GPW_VARIANCE_PCT] [float] NULL,
[ACTUALS_NPW_AMT] [money] NULL,
[PRIOR_ACTUALS_NPW_VARIANCE] [numeric](18, 2) NULL,
[PRIOR_ACTUALS_NPW_VARIANCE_PCT] [float] NULL,
[AMT_FROM] [varchar](50)
)

CREATE TABLE ##TGME_PRIOR_WEEK_RPT
(
[ZONE_NM] [varchar](100) NULL,
[ZONE_CD] [varchar](5) NULL,
[CRD_REGION_CD] [varchar](5) NULL,
[CRD_REGION_NM] [varchar](100) NULL,
[SUBMISSION_DT] [datetime] NULL,
[FORECAST_MONTH] [varchar](20) NULL,
[FORECAST_YEAR] [int] NULL,
[PROFIT_CENTER_CD] [varchar](5) NULL,
[PROFIT_CENTER_NM] [varchar](100) NULL,
[PRODUCT_GRP_CD] [varchar](5) NULL,
[PRODUCT_GRP_NM] [varchar](100) NULL,
[BUDGET_GPW] [money] NULL,
[FORECAST_GPW] [money] NULL,
[BUDGET_NPW] [money] NULL,
[FORECAST_NPW] [money] NULL,
[ACTUALS_GPW_AMT] [money] NULL,
[ACTUALS_NPW_AMT] [money] NULL,
[AMT_FROM] [varchar](50)
)

INSERT INTO [##TGME_CURR_WEEK_RPT]

(
[ZONE_NM] ,
[ZONE_CD],
[CRD_REGION_CD] ,
[CRD_REGION_NM] ,
[SUBMISSION_DT] ,
[FORECAST_MONTH],
[FORECAST_YEAR] ,
[PROFIT_CENTER_CD],
[PROFIT_CENTER_NM],
[PRODUCT_GRP_CD],
[PRODUCT_GRP_NM],
[FORECAST_GPW],
[FORECAST_NPW]
)

SELECT Z.ZONE_NM, R.ZONE_CD, B.REGION_CD, R.REGION_NM, B.SUBMISSION_DT, B.MONTH_NM, B.YEAR_NO,
B.PROFIT_CENTER_CD, PC.PROFIT_CENTER_NM, B.PRODUCT_GRP_CD, PG.PRODUCT_GRP_NM, B.FORECAST_GPW, B.FORECAST_NPW

FROM

(SELECT F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT, SUM(A1.FORECAST_GPW) AS FORECAST_GPW, SUM(A1.FORECAST_NPW) AS FORECAST_NPW
FROM TGME_FORECASTS F
INNER JOIN
(SELECT FORECAST_ID, Credited_Region_CD, DIVISION_NO, SUM(GPW_AM) AS FORECAST_GPW,
SUM(NPW_AM) AS FORECAST_NPW FROM TGME_ACCOUNTS
WHERE PROBABILITY_TO_BIND_PCT >= .7
GROUP BY FORECAST_ID, Credited_Region_CD, DIVISION_NO) A1
ON F.FORECAST_ID = A1.FORECAST_ID
--PARAMETER
WHERE
F.SUBMISSION_DT = ''' + @Submission_Dt + ''' GROUP BY F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT
) B

INNER JOIN TGME_PROFIT_CENTER PC ON
B.PROFIT_CENTER_CD = PC.PROFIT_CENTER_CD

INNER JOIN dbo.TGME_PRODUCT_GRP PG ON
B.PRODUCT_GRP_CD = PG.PRODUCT_GRP_CD

INNER JOIN TGME_REGION R ON
R.REGION_CD = B.REGION_CD

INNER JOIN TGME_ZONE Z ON
R.ZONE_CD = Z.ZONE_CD

--PARAMETER WHERELIST1
WHERE
R.ZONE_CD IN ( ' + @Zones + ' ) AND ' + @WHERELIST1 + '

--UPDATE BUDGET GPW / NPW

UPDATE ##TGME_CURR_WEEK_RPT
SET
BUDGET_GPW = BGT.GPW,
BUDGET_NPW = BGT.NPW
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN TGME_BUDGET BGT
ON
BGT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
BGT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
BGT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
BGT.MONTH_NM = CWR.FORECAST_MONTH AND
BGT.YEAR_NO = CWR.FORECAST_YEAR

--CALCULATE FORECAST NPW/GPW IF ACTUALS ARE FOUND, THEN ITS ACTUALS, ELSE IF FORECAST ARE FOUND THEN FORECAST ELSE IF
--BUDGET IS FOUND THEN BUDGET AS PER THE ORDER.

--FIND BUDGET

UPDATE ##TGME_CURR_WEEK_RPT
SET
FORECAST_GPW = BGT.GPW,
FORECAST_NPW = BGT.NPW,
AMT_FROM = ''BUDGET''
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN TGME_BUDGET BGT
ON
BGT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
BGT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
BGT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
BGT.MONTH_NM = CWR.FORECAST_MONTH AND
BGT.YEAR_NO = CWR.FORECAST_YEAR

--FIND FORECAST AND OVER WRITE THE BUDGET

UPDATE ##TGME_CURR_WEEK_RPT
SET
FORECAST_GPW = C.FORECAST_GPW,
FORECAST_NPW = C.FORECAST_NPW,
AMT_FROM = ''FORECAST''
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN
(SELECT F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT, SUM(A1.FORECAST_GPW) AS FORECAST_GPW, SUM(A1.FORECAST_NPW) AS FORECAST_NPW
FROM TGME_FORECASTS F
INNER JOIN
(SELECT FORECAST_ID, Credited_Region_CD, DIVISION_NO, SUM(GPW_AM) AS FORECAST_GPW,
SUM(NPW_AM) AS FORECAST_NPW FROM TGME_ACCOUNTS
WHERE PROBABILITY_TO_BIND_PCT >= .7
GROUP BY FORECAST_ID, Credited_Region_CD, DIVISION_NO) A1
ON F.FORECAST_ID = A1.FORECAST_ID
--PARAMETER
WHERE
F.SUBMISSION_DT = ''' + @Submission_Dt + '''
GROUP BY F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT
) C
ON
C.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
C.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
C.REGION_CD = CWR.CRD_REGION_CD AND
C.MONTH_NM = CWR.FORECAST_MONTH AND
C.SUBMISSION_DT = CWR.SUBMISSION_DT AND
C.YEAR_NO = CWR.FORECAST_YEAR

--FIND ACTUALS AND OVER WRITE THE FORECAST

UPDATE ##TGME_CURR_WEEK_RPT
SET
FORECAST_GPW = ACT.GPW_AM,
FORECAST_NPW = ACT.NPW_AM,
AMT_FROM = ''ACTUALS''
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN TGME_ACTUALS ACT
ON
ACT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
ACT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
ACT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
ACT.MONTH_NM = CWR.FORECAST_MONTH AND
ACT.YEAR_NO = CWR.FORECAST_YEAR

--UPDATE NO OF ACCTS, ENG FEE, SALE PIPELINE GPW, SALES PIPELINE NPW

UPDATE ##TGME_CURR_WEEK_RPT
SET
NO_OF_ACCTS = D.NO_OF_ACCTS,
ENG_FEES = D.ENG_FEES,
SALES_PIPELINE_GPW = D.SALES_PIPELINE_GPW,
SALES_PIPELINE_NPW = D.SALES_PIPELINE_NPW
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN
(
SELECT
L1.PROFIT_CENTER_CD,
L1.SUBMISSION_DT,
L1.MONTH_NM, L1.YEAR_NO, L1.PRODUCT_GRP_CD, L1.CREDITED_REGION_CD, L1.DIVISION_NO,
SUM(L1.NO_OF_ACCTS) AS NO_OF_ACCTS, SUM(L1.ENG_FEE) AS ENG_FEES,
SUM(L1.SALES_PIPELINE_GPW) AS SALES_PIPELINE_GPW,
SUM(L1.SALES_PIPELINE_NPW) AS SALES_PIPELINE_NPW

FROM

(SELECT
F.PROFIT_CENTER_CD,
F.SUBMISSION_DT,
F.MONTH_NM, F.YEAR_NO, F.PRODUCT_GRP_CD, A1.CREDITED_REGION_CD, A1.DIVISION_NO,
A1.NO_OF_ACCTS,
A1.ENG_FEE,
A1.SALES_PIPELINE_GPW, A1.SALES_PIPELINE_NPW
FROM
(
SELECT * FROM TGME_FORECASTS
) F
INNER JOIN
(SELECT FORECAST_ID, Credited_Region_CD, DIVISION_NO, COUNT(*) AS NO_OF_ACCTS,
SUM(Eng_Fee_AM) AS ENG_FEE, SUM(GPW_AM) AS SALES_PIPELINE_GPW, SUM(NPW_AM) AS SALES_PIPELINE_NPW
FROM TGME_ACCOUNTS
WHERE PROBABILITY_TO_BIND_PCT < .7
GROUP BY FORECAST_ID, Credited_Region_CD, DIVISION_NO
) A1
ON F.FORECAST_ID = A1.FORECAST_ID
--PARAMETER WHERELIST2
WHERE
F.SUBMISSION_DT = ''' + @Submission_Dt + ''' AND ' + @WHERELIST2 + '
) L1
GROUP BY L1.PROFIT_CENTER_CD,
L1.SUBMISSION_DT,
L1.MONTH_NM, L1.YEAR_NO, L1.PRODUCT_GRP_CD, L1.CREDITED_REGION_CD, L1.DIVISION_NO
) D
ON
D.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
D.SUBMISSION_DT = CWR.SUBMISSION_DT AND
D.MONTH_NM = CWR.FORECAST_MONTH AND
D.YEAR_NO = CWR.FORECAST_YEAR AND
D.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
D.CREDITED_REGION_CD = CWR.CRD_REGION_CD
WHERE CWR.[AMT_FROM] = ''FORECAST''

UPDATE ##TGME_CURR_WEEK_RPT
SET
ACTUALS_GPW_AMT = E.ACTUALS_GPW_AM,
ACTUALS_NPW_AMT = E.ACTUALS_NPW_AM
FROM ##TGME_CURR_WEEK_RPT CWR
INNER JOIN
(
select [Profit_Center_CD]
,[Product_Grp_CD]
,CREDITED_REGION_CD
,[Division_NO]
,[Month_NM]
,[Year_NO]
,SUM(GPW_AM) AS ACTUALS_GPW_AM, SUM(NPW_AM) AS ACTUALS_NPW_AM from TGME_Actuals

--PARAMETER WHERELIST3
where ' + @WHERELIST3 + '
GROUP BY [Profit_Center_CD]
,[Product_Grp_CD]
,CREDITED_REGION_CD
,[Division_NO]
,[Month_NM]
,[Year_NO]
) E

ON
E.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
E.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
E.MONTH_NM = CWR.FORECAST_MONTH AND
E.YEAR_NO = CWR.FORECAST_YEAR-1 AND
E.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD

--PRIOR WEEK

INSERT INTO [##TGME_PRIOR_WEEK_RPT]

(
[ZONE_NM] ,
[ZONE_CD],
[CRD_REGION_CD] ,
[CRD_REGION_NM] ,
[SUBMISSION_DT] ,
[FORECAST_MONTH],
[FORECAST_YEAR] ,
[PROFIT_CENTER_CD],
[PROFIT_CENTER_NM],
[PRODUCT_GRP_CD],
[PRODUCT_GRP_NM],
[FORECAST_GPW],
[FORECAST_NPW]
)

SELECT Z.ZONE_NM, R.ZONE_CD, B.REGION_CD, R.REGION_NM, B.SUBMISSION_DT, B.MONTH_NM, B.YEAR_NO,
B.PROFIT_CENTER_CD, PC.PROFIT_CENTER_NM, B.PRODUCT_GRP_CD, PG.PRODUCT_GRP_NM, B.FORECAST_GPW, B.FORECAST_NPW

FROM

(SELECT F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT, SUM(A1.FORECAST_GPW) AS FORECAST_GPW, SUM(A1.FORECAST_NPW) AS FORECAST_NPW
FROM TGME_FORECASTS F
INNER JOIN
(SELECT FORECAST_ID, Credited_Region_CD, DIVISION_NO, SUM(GPW_AM) AS FORECAST_GPW,
SUM(NPW_AM) AS FORECAST_NPW FROM TGME_ACCOUNTS
WHERE PROBABILITY_TO_BIND_PCT >= .7
GROUP BY FORECAST_ID, Credited_Region_CD, DIVISION_NO) A1
ON F.FORECAST_ID = A1.FORECAST_ID
--PARAMETER
WHERE
F.SUBMISSION_DT = ''' + @prior_submission_dt + '''
GROUP BY F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT
) B

INNER JOIN TGME_PROFIT_CENTER PC ON
B.PROFIT_CENTER_CD = PC.PROFIT_CENTER_CD

INNER JOIN dbo.TGME_PRODUCT_GRP PG ON
B.PRODUCT_GRP_CD = PG.PRODUCT_GRP_CD

INNER JOIN TGME_REGION R ON
R.REGION_CD = B.REGION_CD

INNER JOIN TGME_ZONE Z ON
R.ZONE_CD = Z.ZONE_CD

--PARAMETER WHERELIST1
WHERE
R.ZONE_CD IN ( ' + @Zones + ' ) AND ' + @WHERELIST1 + '

--UPDATE BUDGET GPW / NPW

UPDATE ##TGME_PRIOR_WEEK_RPT
SET
BUDGET_GPW = BGT.GPW,
BUDGET_NPW = BGT.NPW
FROM ##TGME_PRIOR_WEEK_RPT CWR
INNER JOIN TGME_BUDGET BGT
ON
BGT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
BGT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
BGT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
BGT.MONTH_NM = CWR.FORECAST_MONTH AND
BGT.YEAR_NO = CWR.FORECAST_YEAR

--CALCULATE FORECAST NPW/GPW IF ACTUALS ARE FOUND, THEN ITS ACTUALS, ELSE IF FORECAST ARE FOUND THEN FORECAST ELSE IF
--BUDGET IS FOUND THEN BUDGET AS PER THE ORDER.

--FIND BUDGET

UPDATE ##TGME_PRIOR_WEEK_RPT
SET
FORECAST_GPW = BGT.GPW,
FORECAST_NPW = BGT.NPW,
AMT_FROM = ''BUDGET''
FROM ##TGME_PRIOR_WEEK_RPT CWR
INNER JOIN TGME_BUDGET BGT
ON
BGT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
BGT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
BGT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
BGT.MONTH_NM = CWR.FORECAST_MONTH AND
BGT.YEAR_NO = CWR.FORECAST_YEAR

--FIND FORECAST AND OVER WRITE THE BUDGET

UPDATE ##TGME_PRIOR_WEEK_RPT
SET
FORECAST_GPW = C.FORECAST_GPW,
FORECAST_NPW = C.FORECAST_NPW,
AMT_FROM = ''FORECAST''
FROM ##TGME_PRIOR_WEEK_RPT CWR
INNER JOIN
(SELECT F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT, SUM(A1.FORECAST_GPW) AS FORECAST_GPW, SUM(A1.FORECAST_NPW) AS FORECAST_NPW
FROM TGME_FORECASTS F
INNER JOIN
(SELECT FORECAST_ID, Credited_Region_CD, DIVISION_NO, SUM(GPW_AM) AS FORECAST_GPW,
SUM(NPW_AM) AS FORECAST_NPW FROM TGME_ACCOUNTS
WHERE PROBABILITY_TO_BIND_PCT >= .7
GROUP BY FORECAST_ID, Credited_Region_CD, DIVISION_NO) A1
ON F.FORECAST_ID = A1.FORECAST_ID
--PARAMETER
WHERE
F.SUBMISSION_DT = ''' + @prior_submission_dt + '''
GROUP BY F.PROFIT_CENTER_CD, F.PRODUCT_GRP_CD, F.REGION_CD,
F.MONTH_NM,
F.YEAR_NO, F.SUBMISSION_DT
) C
ON
C.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
C.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
C.REGION_CD = CWR.CRD_REGION_CD AND
C.MONTH_NM = CWR.FORECAST_MONTH AND
C.SUBMISSION_DT = CWR.SUBMISSION_DT AND
C.YEAR_NO = CWR.FORECAST_YEAR

--FIND ACTUALS AND OVER WRITE THE FORECAST

UPDATE ##TGME_PRIOR_WEEK_RPT
SET
FORECAST_GPW = ACT.GPW_AM,
FORECAST_NPW = ACT.NPW_AM,
AMT_FROM = ''ACTUALS''
FROM ##TGME_PRIOR_WEEK_RPT CWR
INNER JOIN TGME_ACTUALS ACT
ON
ACT.PROFIT_CENTER_CD = CWR.PROFIT_CENTER_CD AND
ACT.PRODUCT_GRP_CD = CWR.PRODUCT_GRP_CD AND
ACT.CREDITED_REGION_CD = CWR.CRD_REGION_CD AND
ACT.MONTH_NM = CWR.FORECAST_MONTH AND
ACT.YEAR_NO = CWR.FORECAST_YEAR

--PRIOR WEEK ENDS

--CALCULATIONS FOR VARIANCES

INSERT INTO ##TGME_CRD_REGION_RPT (
[ZONE_NM], [ZONE_CD], [CRD_REGION_CD], [CRD_REGION_NM], [SUBMISSION_DT], [BUDGET_GPW], [FORECAST_GPW],
[BUDGET_NPW], [FORECAST_NPW], [NO_OF_ACCTS], [ENG_FEES], [SALES_PIPELINE_GPW], [SALES_PIPELINE_NPW],
[ACTUALS_GPW_AMT],[ACTUALS_NPW_AMT])
SELECT ZONE_NM, ZONE_CD, CRD_REGION_CD, CRD_REGION_NM, SUBMISSION_DT, SUM(BUDGET_GPW), SUM(FORECAST_GPW),
SUM(BUDGET_NPW), SUM(FORECAST_NPW), SUM(NO_OF_ACCTS), SUM(ENG_FEES), SUM(SALES_PIPELINE_GPW), SUM(SALES_PIPELINE_NPW),
SUM(ACTUALS_GPW_AMT), SUM(ACTUALS_NPW_AMT) FROM ##TGME_CURR_WEEK_RPT
GROUP BY ZONE_NM, ZONE_CD, CRD_REGION_CD, CRD_REGION_NM, SUBMISSION_DT

--CALCULATE GPW_BUDGET_VARIANCE, GPW_BUDGET_VARIANCE_PCT, NPW_BUDGET_VARIANCE, NPW_BUDGET_VARIANCE_PCT

UPDATE ##TGME_CRD_REGION_RPT
SET
GPW_BUDGET_VARIANCE = FORECAST_GPW - BUDGET_GPW,
GPW_BUDGET_VARIANCE_PCT = (FORECAST_GPW - BUDGET_GPW)/BUDGET_GPW,
NPW_BUDGET_VARIANCE = FORECAST_NPW - BUDGET_NPW,
NPW_BUDGET_VARIANCE_PCT = (FORECAST_NPW - BUDGET_NPW)/BUDGET_NPW

UPDATE ##TGME_CRD_REGION_RPT
SET
PRIOR_FCAST_GPW_AM = P.PRIOR_FORECAST_GPW,
PRIOR_FCAST_NPW_AM = P.PRIOR_FORECAST_NPW
FROM ##TGME_CRD_REGION_RPT R
INNER JOIN

(SELECT ZONE_NM, ZONE_CD, CRD_REGION_CD, CRD_REGION_NM, SUBMISSION_DT, SUM(BUDGET_GPW) AS PRIOR_BUDGET_GPW,
SUM(FORECAST_GPW) AS PRIOR_FORECAST_GPW,
SUM(BUDGET_NPW) AS PRIOR_BUDGET_NPW, SUM(FORECAST_NPW) AS PRIOR_FORECAST_NPW FROM ##TGME_PRIOR_WEEK_RPT
GROUP BY ZONE_NM, ZONE_CD, CRD_REGION_CD, CRD_REGION_NM, SUBMISSION_DT) P
ON
R.ZONE_CD = P.ZONE_CD AND
R.CRD_REGION_CD = P.CRD_REGION_CD

UPDATE ##TGME_CRD_REGION_RPT
SET
PRIOR_FCAST_GPW_VARIANCE = CASE WHEN PRIOR_FCAST_GPW_AM = 0 OR PRIOR_FCAST_GPW_AM IS NULL THEN FORECAST_GPW ELSE FORECAST_GPW - PRIOR_FCAST_GPW_AM END,
PRIOR_FCAST_NPW_VARIANCE = CASE WHEN PRIOR_FCAST_NPW_AM = 0 OR PRIOR_FCAST_NPW_AM IS NULL THEN FORECAST_NPW ELSE FORECAST_NPW - PRIOR_FCAST_NPW_AM END,
PRIOR_FCAST_GPW_AM_PCT = CASE WHEN PRIOR_FCAST_GPW_AM = 0 OR PRIOR_FCAST_GPW_AM IS NULL THEN 0 ELSE (FORECAST_GPW - PRIOR_FCAST_GPW_AM) / PRIOR_FCAST_GPW_AM END,
PRIOR_FCAST_NPW_AM_PCT = CASE WHEN PRIOR_FCAST_NPW_AM = 0 OR PRIOR_FCAST_NPW_AM IS NULL THEN 0 ELSE (FORECAST_NPW - PRIOR_FCAST_NPW_AM) / PRIOR_FCAST_NPW_AM END,
PRIOR_ACTUALS_GPW_VARIANCE = CASE WHEN ACTUALS_GPW_AMT = 0 OR ACTUALS_GPW_AMT IS NULL THEN FORECAST_GPW ELSE FORECAST_GPW - ACTUALS_GPW_AMT END,
PRIOR_ACTUALS_NPW_VARIANCE = CASE WHEN ACTUALS_NPW_AMT = 0 OR ACTUALS_NPW_AMT IS NULL THEN FORECAST_NPW ELSE FORECAST_NPW - ACTUALS_NPW_AMT END,
PRIOR_ACTUALS_GPW_VARIANCE_PCT = CASE WHEN ACTUALS_GPW_AMT = 0 OR ACTUALS_GPW_AMT IS NULL THEN 0 ELSE (FORECAST_GPW - ACTUALS_GPW_AMT) / ACTUALS_GPW_AMT END,
PRIOR_ACTUALS_NPW_VARIANCE_PCT = CASE WHEN ACTUALS_NPW_AMT = 0 OR ACTUALS_NPW_AMT IS NULL THEN 0 ELSE (FORECAST_NPW - ACTUALS_NPW_AMT) / ACTUALS_NPW_AMT END

--SELECT * FROM ##TGME_CURR_WEEK_RPT
--SELECT * FROM ##TGME_PRIOR_WEEK_RPT
SELECT * FROM ##TGME_CRD_REGION_RPT '
[/code]

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-12 : 12:13:06

Raj this doesn't look too hopeless, basically you can easily split this up and execute each part in turn.

1) Remove the CREATE table from the dynamic sql and do it statically.
While you are at it turn then into local temp tables. There's no need to create them the way the way you are and your way has a lot of disadvantages.

2, 3, 4, 5, etc) Just do each INSERT block one at a time and execute them in sequence. You don't need to do everything at once. So do a


SET @sql = '
INSERT .......
SELECT .........
FROM .........
WHERE .........
'
EXEC (@sql)

for each *separate* INSERT block rather than trying to do them all at once. They will still get performed in the necessary order. (use EXEC sp_executeSql with NVARCHAR while you are at it as it caches the plan and you don't need a large character limit any more)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 13:24:25

quote:
Originally posted by Transact Charlie

Raj this doesn't look too hopeless, basically you can easily split this up and execute each part in turn.

1) Remove the CREATE table from the dynamic sql and do it statically.
While you are at it turn then into local temp tables. There's no need to create them the way the way you are and your way has a lot of disadvantages.

2, 3, 4, 5, etc) Just do each INSERT block one at a time and execute them in sequence. You don't need to do everything at once. So do a

SET @sql = '
INSERT .......
SELECT .........
FROM .........
WHERE .........
'
EXEC (@sql)

for each *separate* INSERT block rather than trying to do them all at once. They will still get performed in the necessary order. (use EXEC sp_executeSql with NVARCHAR while you are at it as it caches the plan and you don't need a large character limit any more)

Wow that was great effort Charlie

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources