Stored Procedure taking too long 40 seconds

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

General SQL Server Forums

New to SQL Server Administration

Stored Procedure taking too long 40 seconds

Author

Topic

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-21 : 01:47:27

Hi All,

Thanks for reading for this post.

Please anyone it would be indeed a great help, if someone can help me in telling me a stored procedure is taking this very long.

What would be the recommended, troubleshooting method, what are the key points to start with.

Thanks,

AB

prett
Posting Yak Master

212 Posts

Posted - 2012-06-21 : 02:14:14

You should check the query plan for the stored procedure and find out why it is taking so long to execute.There are some chances that you have bad indexes or a poorly designed query. In stored procedure each query has to be optimized. There are some optimization tips for stored procedure, Please check it from here: http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-21 : 02:40:36

Thanks for the link, am going through it, in the meantime, please note that this stored procedure is with 1800 lines and as many variables upto 210..

would this be a problem to have 210 variables, all those variables are INT, FLOAT and varchar, mainly I could see a lot of INT

not a single tinyint nor smallint.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-06-21 : 03:07:49

quote:
Originally posted by benildusmuerling

Thanks for the link, am going through it, in the meantime, please note that this stored procedure is with 1800 lines and as many variables upto 210..

would this be a problem to have 210 variables, all those variables are INT, FLOAT and varchar, mainly I could see a lot of INT

not a single tinyint nor smallint.

One of the major reasons would be your sql select statements.

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-21 : 04:34:11

Hi Ahmeds08 and others,

Thanks for reading my post

Could you please elaborate on that, for me to have more findings, I see your answer a bit gereric.

Thanks,

AB

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-06-21 : 04:48:26

quote:
Originally posted by benildusmuerling

Hi Ahmeds08 and others,

Thanks for reading my post

Could you please elaborate on that, for me to have more findings, I see your answer a bit gereric.

Thanks,

AB

check the select statements,especially the where clause is applied on indexed columns or not,and excluding unneccessary columns in the select list,removing unneccessary joins.

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-21 : 10:23:37

Hi Ahemed and everyone,

Thanks for reading my post, what if the where clauses have been filtered fors colmns which are not indexed,

I cant see much of a joins here, but a table is there which is returning 300,000 number of rows, which predominantly being used by the select quesry around 10 times.

and then there is a cursor too.

By the way, I do not have to go an do an indexing for the primary key aint it, as the moment we create a primary key, it is indexed automatically, but I can significantly see, in this table there are so many primary

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-21 : 10:26:47

Hi Ahemed and everyone,

Thanks for reading my post, what if the where clauses have been filtered for columns which are not indexed,

I cant see much of a joins here, but a table is there which is returning 300,000 number of rows, which predominantly being used by the select quesry around 10 times.

and then there is a cursor too.

By the way, I do not have to go an do an indexing for the primary key aint it, as the moment we create a primary key, it is indexed automatically for that very field, but I can significantly see, in this table there are so many primary keys, which I believe are composite, and I cant see any non clustered indexes

The lines of code for this stored procedure is 1900 lines, and there are around 210 variables which consist of INT, varchar and float only.

Thanks,

AB

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-21 : 14:04:13

1)Are you able to post the Execution plan in text format?
2) Cursors is something to analyse.
3)Has it suddenly slowed down? or has it always been slow?
4)What's your maintenance plan?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-21 : 15:10:30

quote:
Originally posted by benildusmuerling

Thanks for the link, am going through it, in the meantime, please note that this stored procedure is with 1800 lines and as many variables upto 210..

would this be a problem to have 210 variables, all those variables are INT, FLOAT and varchar, mainly I could see a lot of INT

not a single tinyint nor smallint.

and

quote:

I cant see much of a joins here, but a table is there which is returning 300,000 number of rows, which predominantly being used by the select quesry around 10 times.

and then there is a cursor too.

By the way, I do not have to go an do an indexing for the primary key aint it, as the moment we create a primary key, it is indexed automatically, but I can significantly see, in this table there are so many primary

1800 lines of code with 210 variables. And a cursor...... oh my!

Here's the good news:
It's almost certain that whatever that sp does can be rewritten with less than half the complexity and with maybe many times the performance.....

And the bad news:
It doesn't sound like you or anyone else has a clue what that stored proc does.......

You could post it..... maybe someone will read through it....

Do you know what the sp is supposed to do?
Do you know which tables it interacts with?

Don't worry about the datatypes -- they are not the problem. The problem will be the logic and the cursor and the sargeability of the queries.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-21 : 20:16:27

The bad news is, there is no one who knows about the logic of the stored procedure here. can you help me in checking the indexes of the table, which is referred, is there anything that I could do with the indexes of the tables. for me to get a speedy stored proceure.

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-22 : 00:46:11

Hi Jack,

Further to the inputs that you have given, I would like to know about furthermore into detail

How would I do this -

1) Are you able to post the Execution plan in text format?
2) What's your maintenance plan?

please tell me how would I do the above 2.

About the other, yes this was been a query which have been always slowed down.

Thanks,

AB

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-22 : 00:55:11

Hi All,

Please be noted simultaneously this query has been written to PRINT statements also in the messages tab on top of getting the query result in the result window.

Is this is an issue when it comes to performance.

Thanks,

AB

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-22 : 03:32:53

Hire someone.

You aren't giving (or able) to post the code or determine where the problem is.

Why not post the code of the sp?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-22 : 11:43:25

The PRINT statement will adds cost . The amount of cost will depend on how often there is a PRINT statement

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

sidestepper
Starting Member

3 Posts

Posted - 2012-06-22 : 17:31:33

When I'm faced with a performance issue like this one - it's usually because what was a perfectly fine SP is now sifting through a lot more data - 300,000 rows is pretty nasty to iterate over more than once. The way I've solved these problems is to add to the number of steps, but reduce the amount of work in each step. Break it into smaller pieces, if you're joining across more than 3 tables, break it into smaller joins into temp tables. In your where conditions, start broad then get narrow - try to select the smallest amount of data possible per step by eliminating the most rows in one condition of a where. Also, yes, if you post the code to the SP, it will be a lot easier to see what your problem may be.

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-28 : 01:30:56

--here you go guys with the code, help me by going through it..

BEGIN
SET NOCOUNT ON

DECLARE @str varchar(8000)

IF @str = ''
PRINT 'NO DATA'
ELSE
BEGIN
DECLARE @1 INT
DECLARE @2 INT
DECLARE @3 INT
DECLARE @4 VARCHAR(20)
DECLARE @5 INT
DECLARE @6 INT
DECLARE @7 INT

Create table b
(
cmp INT,
bra INT,
sta INT,
cus VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS,
cus_num INT,
sec INT,
pro INT
)

Create table c
(
cmp INT,
bra INT,
sta INT,
cus VARCHAR(20),
cus_num INT,
sec INT,
pro INT,

edi DATETIME,

4wy INT,
4ty INT,
qtr_ly INT,
qtr_ty INT,
ytd_ly INT,
ytd_ty INT,
mat_ly INT,
mat_ty INT,

4wy_ly INT,
4wy_ty INT,
4QTR_ly INT,
4QTR_ty INT,
YTD_ly INT,
YTD_ty INT,
facing_MAT_LY INT,
facing_MAT_TY INT,

oo INT,
oa INT,
ob INT,
oc INT,
od INT,
oe INT,
of INT,
og INT
)

DECLARE @sql VARCHAR(1000)

SET @sql = 'INSERT INTO b
SELECT DISTINCT cmp, bra, sta, cus, cus_num, sec, pro FROM a WHERE 1=1'
IF ( @pCmp <>'' ) SET @sql = @sql + ' AND cmp IN (' + @pCmp + ')'
IF ( @pBra <>'' ) SET @sql = @sql + ' AND bra IN (' + @pBra + ')'
IF ( @pSta = '0' OR @pSta = '26' OR @pSta = '')
SET @sql = @sql + ''
ELSE
SET @sql = @sql + ' AND sta IN (' + @pSta + ')'

IF ( @pCus <>'' ) SET @sql = @sql + ' AND cus IN (''' + @pCus + ''')'
IF ( @pcus_num <>'' ) SET @sql = @sql + ' AND cus_num IN (' + @pcus_num + ')'
IF ( @pSec<>'' ) SET @sql = @sql + ' AND sec IN (' + @pSec+ ')'

EXEC(@sql)
--====================================================

-- This year 4 weeks
DECLARE @dist INT
DECLARE @dist1 INT
DECLARE @dist2 INT
DECLARE @dist3 INT
DECLARE @dist4 INT
DECLARE @dist5 INT
DECLARE @dist6 INT
DECLARE @dist7 INT
DECLARE @dist8 INT
DECLARE @dist9 INT
DECLARE @dist10 INT
DECLARE @dist11 INT
DECLARE @dist12 INT
DECLARE @dist13 INT
DECLARE @dist14 INT
DECLARE @dist15 INT

DECLARE @dist16 DATETIME
DECLARE @dist17 DATETIME
DECLARE @dist18 DATETIME
DECLARE @dist19 DATETIME

SET @dist16 = @pDate
SET @dist17 = dbo.fn_getAnyDateInTheWeek_forGivenDate(@dist16, 1)

SET @dist19 = CONVERT(DATETIME, @dist17, 102)
SET @dist18 = DATEADD(d, -21, @dist19)

PRINT '@dist18: ' + CONVERT(VARCHAR(10), @dist18, 102)
PRINT '@dist19: ' + CONVERT(VARCHAR(10), @dist19, 102)
PRINT ''

-- Last year 4 weeks
DECLARE @points1 INT
DECLARE @points2 INT
DECLARE @points3 INT
DECLARE @points4 INT
DECLARE @points5 INT
DECLARE @points6 INT
DECLARE @points7 INT
DECLARE @points8 INT
DECLARE @points9 INT
DECLARE @points10 INT
DECLARE @points11 INT
DECLARE @points12 INT
DECLARE @points13 INT
DECLARE @points14 INT
DECLARE @points15 INT
DECLARE @points16 INT

DECLARE @points17 DATETIME
DECLARE @points18 DATETIME
DECLARE @points19 DATETIME
DECLARE @points20 DATETIME

SET @points17 = DATEADD(yy, -1, @pDate)
SET @points18 = dbo.fn_getAnyDateInTheWeek_forGivenDate(@points17, 1)

SET @points20 = @points18
SET @points19 = DATEADD(d, -21, @points20)

PRINT '@points19: ' + CONVERT(VARCHAR(10), @points19, 102)
PRINT '@points20: ' + CONVERT(VARCHAR(10), @points20, 102)
PRINT ''

--This Quater
DECLARE @01 FLOAT
DECLARE @02 FLOAT
DECLARE @03 INT
DECLARE @04 FLOAT
DECLARE @05 INT
DECLARE @06 INT
DECLARE @07 INT
DECLARE @08 INT
DECLARE @09 INT
DECLARE @10 INT
DECLARE @11 INT
DECLARE @12 INT
DECLARE @13 INT
DECLARE @14 INT
DECLARE @15 INT
DECLARE @16 INT

DECLARE @17 DATETIME
DECLARE @18 DATETIME

SET @18 = CONVERT(VARCHAR(10), @pDate, 101)
SET @17 = DATEADD(qq, -1, @18)

PRINT '@17: ' + CONVERT(VARCHAR(10), @17, 102)
PRINT '@18: ' + CONVERT(VARCHAR(10), @18, 102)
PRINT ''

--last year Quater
DECLARE @q1 FLOAT
DECLARE @q2 FLOAT
DECLARE @q3 INT
DECLARE @q4 FLOAT
DECLARE @q5 INT
DECLARE @q6 INT
DECLARE @q7 INT
DECLARE @q8 INT
DECLARE @q9 INT
DECLARE @q10 INT
DECLARE @q11 INT
DECLARE @q12 INT
DECLARE @q13 INT
DECLARE @q14 INT
DECLARE @q15 INT
DECLARE @q16 INT

DECLARE @q17 DATETIME
DECLARE @q18 DATETIME

SET @q18 = CONVERT(VARCHAR(10), DATEADD(yy, -1, @pDate), 101)
SET @q17 = DATEADD(qq, -1, @q18)

PRINT '@q17: ' + CONVERT(VARCHAR(10), @q17, 102)
PRINT '@q18: ' + CONVERT(VARCHAR(10), @q18, 102)
PRINT ''

--This year latest
DECLARE @aaaaa FLOAT
DECLARE @aaddddd FLOAT
DECLARE @thi INT
DECLARE @that FLOAT
DECLARE @ant INT
DECLARE @and INT
DECLARE @aone INT
DECLARE @atwo INT
DECLARE @333 INT
DECLARE @334 INT
DECLARE @336 INT
DECLARE @337 INT
DECLARE @338 INT
DECLARE @339 INT
DECLARE @340 INT
DECLARE @341 INT

DECLARE @345 DATETIME
DECLARE @349 DATETIME

SELECT @349 = CONVERT(VARCHAR(10), getdate(), 101)
SELECT @345 = dateadd(yy, -1, @349)

PRINT '@345: ' + CONVERT(VARCHAR(10), @345, 102)
PRINT '@349: ' + CONVERT(VARCHAR(10), @349, 102)
PRINT ''

DECLARE @55 FLOAT
DECLARE @56 FLOAT
DECLARE @57 INT
DECLARE @58 FLOAT
DECLARE @59 INT
DECLARE @60 INT
DECLARE @61 INT
DECLARE @62 INT
DECLARE @63 INT
DECLARE @64 INT
DECLARE @65 INT
DECLARE @66 INT
DECLARE @67 INT
DECLARE @68 INT
DECLARE @69 INT
DECLARE @70 INT

DECLARE @71 DATETIME
DECLARE @72 DATETIME

SELECT @72 = CONVERT(VARCHAR(10), DATEADD(yy, -1, getdate()), 101)
SELECT @71 = DATEADD(yy, -1, @72)

PRINT '@71: ' + CONVERT(VARCHAR(10), @71, 102)
PRINT '@72: ' + CONVERT(VARCHAR(10), @72, 102)
PRINT ''

--This year MAT
DECLARE @as FLOAT
DECLARE @asaa FLOAT
DECLARE @bs INT
DECLARE @cs FLOAT
DECLARE @ds INT
DECLARE @es INT
DECLARE @fs INT
DECLARE @gs INT
DECLARE @hs INT
DECLARE @is INT
DECLARE @ks INT
DECLARE @kl INT
DECLARE @pokl INT
DECLARE @kll INT
DECLARE @lokp INT
DECLARE @liuj INT

DECLARE @thyg DATETIME
DECLARE @ijuh DATETIME

SELECT @ijuh = CONVERT(VARCHAR(10), @pDate, 101)
SELECT @thyg = dateadd(yy, -1, @ijuh)

PRINT '@thyg: ' + CONVERT(VARCHAR(10), @thyg, 102)
PRINT '@ijuh: ' + CONVERT(VARCHAR(10), @ijuh, 102)
PRINT ''

DECLARE @0101 FLOAT
DECLARE @0102 FLOAT
DECLARE @0103 INT
DECLARE @0104 FLOAT
DECLARE @0105 INT
DECLARE @0106 INT
DECLARE @0107 INT
DECLARE @0108 INT
DECLARE @0109 INT
DECLARE @0110 INT
DECLARE @0111 INT
DECLARE @0112 INT
DECLARE @0113 INT
DECLARE @0114 INT
DECLARE @0115 INT
DECLARE @0116 INT

DECLARE @0117 DATETIME
DECLARE @0118 DATETIME
DECLARE @0119 DATETIME

SELECT @0118 = CONVERT(VARCHAR(10), DATEADD(yy, -1, @pDate), 101)
SELECT @0117 = dateadd(yy, -1, @0118)

PRINT '@0117: ' + CONVERT(VARCHAR(10), @0117, 102)
PRINT '@0118: ' + CONVERT(VARCHAR(10), @0118, 102)

--=======================================================END DECLARATION=============================================================
SET @dist = 0
SET @dist1 = 0
SET @dist3 = 0
SET @wC = 0

SET @points1 = 0
SET @points2 = 0
SET @points4 = 0
SET @wC = 0

SET @01 = 0
SET @02 = 0
SET @04 = 0
SET @wC = 0

SET @q1 = 0
SET @q2 = 0
SET @q4 = 0
SET @wC = 0

SET @aaaaa = 0
SET @aaddddd = 0
SET @that = 0
SET @wC = 0

SET @55 = 0
SET @56 = 0
SET @58 = 0
SET @wC = 0

SET @as = 0
SET @asaa = 0
SET @cs = 0
SET @wC = 0

SET @0101 = 0
SET @0102 = 0
SET @0104 = 0
SET @wC = 0

DECLARE @counter INT
SET @counter = 0
--open and retrive data from the cursor
DECLARE d CURSOR FOR
SELECT cmp, bra, sta, cus, cus_num, sec, pro
FROM b

--open and retrive data from the cursor
OPEN d
FETCH NEXT FROM d INTO @1, @2, @3, @4, @5, @6, @7

WHILE (@@FETCH_STATUS = 0 )
BEGIN
SET @counter = @counter + 1
--This year 4 weeks------------------------------------------------------------------------------------------------------------------

SET @dist = 0
SET @dist1 = 0
SET @dist3 = 0
SET @wC = 0

select @dist = CASE WHEN (sum(dist_graded) + sum(distraded)) IS NULL THEN 0 ELSE (sum(disded) + sum(disaded)) END
, @dist1= CASE WHEN sum(tl_facing) IS NULL THEN 0 ELSE sum(toacing) END
, @dist3 = CASE WHEN sum(disgraded) IS NULL THEN 0 ELSE sum(dt_graded) END
FROM a a
WHERE 1=1
AND a.cmp = @1
AND a.bra = @2
AND a.sta = @3
AND a.cus = @4
AND a.cus_num = @5
AND a.sec = @6
AND a.pro = @7
AND history_data_date = @dist19

SET @points1 = 0
SET @points2 = 0
SET @points4 = 0
SET @wC = 0

SELECT @points1 = CASE WHEN (sum(did) + sum(dised)) IS NULL THEN 0 ELSE (sum(distraded) + sum(distraded)) END
, @points2= CASE WHEN sum(totalacing) IS NULL THEN 0 ELSE sum(totang) END
, @points4 = CASE WHEN sum(disraded) IS NULL THEN 0 ELSE sum(distded) END
FROM a a
WHERE 1=1
AND a.cmp = @1
AND a.bra = @2
AND a.sta = @3
AND a.cus = @4
AND a.cus_num = @5
AND a.sec = @6
AND a.pro = @7
AND history_data_date = @points20

SET @01 = 0
SET @02 = 0
SET @04 = 0
SET @wC = 0

select @01 = CASE WHEN (sum(disraded) + sum(distgraded)) IS NULL THEN 0 ELSE (sum(disted) + sum(distraded)) END
, @02= CASE WHEN sum(totalng) IS NULL THEN 0 ELSE sum(totalcing) END
, @04 = CASE WHEN sum(distgraded) IS NULL THEN 0 ELSE sum(distgraded) END
, @wC = COUNT(DISTINCT historydate)
FROM a a
WHERE 1=1
AND a.cmp = @1
AND a.bra = @2
AND a.sta = @3
AND a.cus = @4
AND a.cus_num = @5
AND a.sec = @6
AND a.pro = @7
AND history_data_date <= @18
AND history_data_date > @17

IF @wC = 0
BEGIN
SET @01 = 0
SET @02 = 0
SET @04 = 0
END
ELSE
BEGIN
SET @01 = (@01 / @wC)
SET @02 = (@02 / @wC)
SET @04 = (@04 / @wC)
END

SET @q1 = 0
SET @q2 = 0
SET @q4 = 0
SET @wC = 0

SELECT @q1 = CASE WHEN (sum(draded) + sum(daded)) IS NULL THEN 0 ELSE (sum(disaded) + sum(disaded)) END
, @q2= CASE WHEN sum(tocing) IS NULL THEN 0 ELSE sum(totcing) END
, @q4 = CASE WHEN sum(draded) IS NULL THEN 0 ELSE sum(distded) END
, @wC = COUNT(DISTINCT history_data_date)
FROM a a
WHERE 1=1
AND a.cmp = @1
AND a.bra = @2
AND a.sta = @3
AND a.cus = @4
AND a.cus_num = @5
AND a.sec = @6
AND a.pro = @7
AND history_data_date <= @q18
AND history_data_date > @q17
GROUP BY a.cmp

IF @wC = 0
BEGIN
SET @q1 = 0
SET @q2 = 0
SET @q4 = 0
END
ELSE
BEGIN
SET @q1 = (@q1 / @wC)
SET @q2 = (@q2 / @wC)
SET @q4 = (@q4 / @wC)
END

SET @aaaaa = 0
SET @aaddddd = 0
SET @that = 0
SET @wC = 0

SELECT @aaaaa = CASE WHEN (sum(disded) + sum(disaded)) IS NULL THEN 0 ELSE (sum(disted) + sum(disaded)) END
, @aaddddd= CASE WHEN sum(totaing) IS NULL THEN 0 ELSE sum(totang) END
, @that = CASE WHEN sum(disraded) IS NULL THEN 0 ELSE sum(distraded) END
, @wC = COUNT(DISTINCT histdate)
FROM a a
WHERE 1=1
AND a.cmp = @1
AND a.bra = @2
AND a.sta = @3
AND a.cus = @4
AND a.cus_num = @5
AND a.sec = @6
AND a.pro = @7
AND history_data_date <= @349
AND history_data_date > @345

IF @wC = 0
BEGIN
SET @aaaaa = 0
SET @aaddddd = 0
SET @that = 0
END
ELSE
BEGIN
SET @aaaaa = (@aaaaa / @wC)
SET @aaddddd = (@aaddddd / @wC)
SET @that = (@that / @wC)
END

SET @55 = 0
SET @56 = 0
SET @58 = 0
SET @wC = 0

select @55 = CASE WHEN (sum(dist_graded) + sum(distaded)) IS NULL THEN 0 ELSE (sum(disaded) + sum(disaded)) END
, @56= CASE WHEN sum(totag) IS NULL THEN 0 ELSE sum(totcing) END
, @58 = CASE WHEN sum(disaded) IS NULL THEN 0 ELSE sum(disraded) END
, @wC = COUNT(DISTINCT histordate)
FROM a a
WHERE 1=1
AND a.cmp = @1
AND a.bra = @2
AND a.sta = @3
AND a.cus = @4
AND a.cus_num = @5
AND a.sec = @6
AND a.pro = @7
AND history_data_date <= @72
AND history_data_date > @71

IF @wC = 0
BEGIN
SET @55 = 0
SET @56 = 0
SET @58 = 0
END
ELSE
BEGIN
SET @55 = (@55 / @wC)
SET @56 = (@56 / @wC)
SET @58 = (@58 / @wC)
END

SET @as = 0
SET @asaa = 0
SET @cs = 0
SET @wC = 0

SELECT @as = CASE WHEN (sum(dist) + sum(disgraded)) IS NULL THEN 0 ELSE (sum(disded) + sum(distaded)) END
, @asaa = CASE WHEN sum(totcing) IS NULL THEN 0 ELSE sum(totacing) END
, @cs = CASE WHEN sum(distnaded) IS NULL THEN 0 ELSE sum(distraded) END
, @wC = COUNT(DISTINCT histordate)
FROM a a
WHERE 1=1
AND a.cmp = @1
AND a.bra = @2
AND a.sta = @3
AND a.cus = @4
AND a.cus_num = @5
AND a.sec = @6
AND a.pro = @7
AND history_data_date <= @ijuh
AND history_data_date > @thyg

IF @wC = 0
BEGIN
SET @as = 0
SET @asaa = 0
SET @cs = 0
END
ELSE
BEGIN
SET @as = (@as / @wC)
SET @asaa = (@asaa / @wC)
SET @cs = (@cs / @wC)
END

SET @0101 = 0
SET @0102 = 0
SET @0104 = 0
SET @wC = 0

SELECT @0101 = CASE WHEN (sum(disd) + sum(diraded)) IS NULL THEN 0 ELSE (sum(distraded) + sum(distnotaded)) END
, @0102 = CASE WHEN sum(totng) IS NULL THEN 0 ELSE sum(tofacing) END
, @0104 = CASE WHEN sum(graded) IS NULL THEN 0 ELSE sum(distnotgraded) END
, @wC = COUNT(DISTINCT histota_date)
FROM a a
WHERE 1=1
AND a.cmp = @1
AND a.bra = @2
AND a.sta = @3
AND a.cus = @4
AND a.cus_num = @5
AND a.sec = @6
AND a.pro = @7
AND history_data_date <= @0118
AND history_data_date > @0117

IF @wC = 0
BEGIN
SET @0101 = 0
SET @0102 = 0
SET @0104 = 0
END
ELSE
BEGIN
SET @0101 = (@0101 / @wC)
SET @0102 = (@0102 / @wC)
SET @0104 = (@0104 / @wC)
END

INSERT INTO c (
cmp
, bra
, sta
, cus
, cus_num
, sec
, pro

, edi

, sdsd
, dsd
, ds
, asd
, adaasd
, d
, sda
, sdaadsa

, 4
, 4sd
, 4asd
, adaasdasd
, asdd
, asd
, dada
, asda

, oo
, oa
, ob
, oc
, od
, oe
, of
, og)
VALUES (
@1
, @2
, @3
, @4
, @5
, @6
, @7

, @pDate

, @points1
, @dist
, ROUND(@q1, 0)
, ROUND(@01, 0)
, ROUND(@55, 0)
, ROUND(@aaaaa, 0)
, ROUND(@0101, 0)
, ROUND(@as, 0)

, @points2
, @dist1
, ROUND(@q2, 0)
, ROUND(@02, 0)
, ROUND(@56, 0)
, ROUND(@aaddddd, 0)
, ROUND(@0102, 0)
, ROUND(@asaa, 0)

, @points4
, @dist3
, ROUND(@q4, 0)
, ROUND(@04, 0)
, ROUND(@58, 0)
, ROUND(@that, 0)
, ROUND(@0104, 0)
, ROUND(@cs, 0)
)

FETCH NEXT FROM d INTO @1, @2, @3, @4,@5, @6, @7
END

CLOSE d
DEALLOCATE d

PRINT '-@Counter = ' + CAST(@Counter AS VARCHAR(10))

SET @points1 = 0
SET @dist = 0
SET @q1 = 0
SET @01 = 0
SET @55 = 0
SET @aaaaa = 0
SET @0101 = 0
SET @as = 0

SET @points2 = 0
SET @dist1 = 0
SET @q2 = 0
SET @02 = 0
SET @56 = 0
SET @aaddddd = 0
SET @0102 = 0
SET @asaa = 0

SET @points4 = 0
SET @dist3 = 0
SET @q4 = 0
SET @04 = 0
SET @58 = 0
SET @that = 0
SET @0104 = 0
SET @cs = 0

SELECT @points1 = SUM(4d)
, @dist = SUM(4d)
, @q1 = SUM(d)
, @01 = SUM(d)
, @55 = SUM(d)
, @aaaaa = SUM(d)
, @0101 = SUM(d)
, @as = SUM(d)

, @points2 = SUM(4dd)
, @dist1 = SUM(4d)
, @q2 = SUM(4zz)
, @02 = SUM(facin)
, @56 = SUM(YTD_ly)
, @aaddddd = SUM(YTD_ty)
, @0102 = SUM(d)
, @asaa = SUM(f)

, @points4 = SUM(oo)
, @dist3 = SUM(oa)
, @q4 = SUM(ob)
, @04 = SUM(oc)
, @58 = SUM(od)
, @that = SUM(oe)
, @0104 = SUM(of)
, @cs = SUM(og)
FROM c

DROP TABLE c

PRINT 'Issues'
PRINT '================='

SET @dist2 = 0
SET @dist4 = 0
SET @dist5 = 0
SET @dist6 = 0
SET @dist7 = 0
SET @dist8 = 0
SET @dist9 = 0
SET @dist10 = 0
SET @dist11 = 0
SET @dist12 = 0
SET @dist13 = 0
SET @dist14 = 0
SET @dist15 = 0

select @dist2 = CASE WHEN sum(s) IS NULL THEN 0 ELSE sum(a) END
, @dist5= CASE WHEN sum(d) IS NULL THEN 0 ELSE sum(a) END
, @dist6 = CASE WHEN sum(c) IS NULL THEN 0 ELSE sum(s) END
, @dist7 = CASE WHEN sum(v) IS NULL THEN 0 ELSE sum(d) END
, @dist8 = CASE WHEN sum(f) IS NULL THEN 0 ELSE sum(f) END
, @dist9 = CASE WHEN sum(g) IS NULL THEN 0 ELSE sum(f) END
, @dist10 = CASE WHEN sum(h) IS NULL THEN 0 ELSE sum(g) END
, @dist11 = CASE WHEN sum(j) IS NULL THEN 0 ELSE sum(h) END
, @dist12 = CASE WHEN sum(j) IS NULL THEN 0 ELSE sum(i) END
, @dist13 = CASE WHEN sum(k) IS NULL THEN 0 ELSE sum(j) END
FROM a a
WHERE 1=1
AND cmp IN (SELECT DISTINCT cmp FROM b)
AND bra IN (SELECT DISTINCT bra FROM b)
AND sta IN (SELECT DISTINCT sta FROM b)
AND cus IN (SELECT DISTINCT cus FROM b)
AND cus_num IN (SELECT DISTINCT cus_num FROM b)
AND sec IN (SELECT DISTINCT sec FROM b)
AND history_data_date <= @dist19
AND history_data_date >= @dist18
GROUP BY a.cmp

PRINT ' - Issues'
PRINT '================='

SET @points3 = 0
SET @points5 = 0
SET @points6 = 0
SET @points7 = 0
SET @points8 = 0
SET @points9 = 0
SET @points10 = 0
SET @points11 = 0
SET @points12 = 0
SET @points13 = 0
SET @points14 = 0
SET @points15 = 0
SET @points16 = 0

select @points3 = CASE WHEN sum(tS) IS NULL THEN 0 ELSE sum(a) END
, @points6= CASE WHEN sum(e) IS NULL THEN 0 ELSE sum(s) END
, @points7 = CASE WHEN sum(t) IS NULL THEN 0 ELSE sum(d) END
, @points8 = CASE WHEN sum(r) IS NULL THEN 0 ELSE sum(f) END
, @points9 = CASE WHEN sum(g) IS NULL THEN 0 ELSE sum(g) END
, @points10 = CASE WHEN sum(b) IS NULL THEN 0 ELSE sum(g) END
, @points11 = CASE WHEN sum(n) IS NULL THEN 0 ELSE sum(z) END
, @points12 = CASE WHEN sum(j) IS NULL THEN 0 ELSE sum(x) END
, @points13 = CASE WHEN sum(e) IS NULL THEN 0 ELSE sum(c) END
, @points14 = CASE WHEN sum(q) IS NULL THEN 0 ELSE sum(v) END
FROM a a
AND cmp IN (SELECT DISTINCT cmp FROM b)
AND bra IN (SELECT DISTINCT bra FROM b)
AND sta IN (SELECT DISTINCT sta FROM b)
AND cus IN (SELECT DISTINCT cus FROM b)
AND cus_num IN (SELECT DISTINCT cus_num FROM b)
AND sec IN (SELECT DISTINCT sec FROM b)
AND history_data_date <= @points20
AND history_data_date >= @points19
GROUP BY a.cmp

SELECT * INTO e FROM
(
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from annananna WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @points20
AND history_data_date >= @points19
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from mannnar WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @points20
AND history_data_date >= @points19
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from japaida WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @points20
AND history_data_date >= @points19
GROUP BY cmp, sta, cus, cus_num
) AS promoNonComply_4W_LY

SELECT @points5 = (CASE WHEN sum(totNonComply) IS NULL THEN 0 ELSE sum(totNonComply) END)
FROM (
SELECT DISTINCT e.cmp, e.sta, e.cus
, e.cus_num, totNonComply
FROM e INNER JOIN (SELECT DISTINCT cmp, bra, sta, cus, cus_num, sec FROM b) b ON
e.cmp = b.cmp
AND e.sta = b.sta
AND e.cus = b.cus
AND e.cus_num = b.cus_num
) AS promTemp

DROP TABLE e

PRINT 'zzzz'
PRINT '================'

SET @03 = 0
SET @05 = 0
SET @06 = 0
SET @07 = 0
SET @08 = 0
SET @09 = 0
SET @10 = 0
SET @11 = 0
SET @12 = 0
SET @13 = 0
SET @14 = 0
SET @15 = 0
SET @16 = 0

select @03 = CASE WHEN sum(a) IS NULL THEN 0 ELSE sum(a) END
, @06= CASE WHEN sum(sa) IS NULL THEN 0 ELSE sum(sa) END
, @07 = CASE WHEN sum(asa) IS NULL THEN 0 ELSE sum(asa) END
, @08 = CASE WHEN sum(ssd) IS NULL THEN 0 ELSE sum(ssd) END
, @09 = CASE WHEN sum(sa) IS NULL THEN 0 ELSE sum(sa) END
, @10 = CASE WHEN sum(as) IS NULL THEN 0 ELSE sum(asaasa) END
, @11 = CASE WHEN sum(as) IS NULL THEN 0 ELSE sum(as) END
, @12 = CASE WHEN sum(ss) IS NULL THEN 0 ELSE sum(ss) END
, @13 = CASE WHEN sum(ss) IS NULL THEN 0 ELSE sum(ss) END
, @14 = CASE WHEN sum(f) IS NULL THEN 0 ELSE sum(d) END
FROM a a

WHERE 1=1
AND cmp IN (SELECT DISTINCT cmp FROM b)
AND bra IN (SELECT DISTINCT bra FROM b)
AND sta IN (SELECT DISTINCT sta FROM b)
AND cus IN (SELECT DISTINCT cus FROM b)
AND cus_num IN (SELECT DISTINCT cus_num FROM b)
AND sec IN (SELECT DISTINCT sec FROM b)
AND history_data_date <= @18
AND history_data_date >= @17
GROUP BY a.cmp

SELECT * INTO f FROM
(
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from annananna WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @18
AND history_data_date >= @17
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from mannnar WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @18
AND history_data_date >= @17
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from japaida WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @18
AND history_data_date >= @17
GROUP BY cmp, sta, cus, cus_num
) AS promoNonComply_Q_TY

SELECT @05 = (CASE WHEN sum(totNonComply) IS NULL THEN 0 ELSE sum(totNonComply) END)
FROM (
SELECT DISTINCT f.cmp, f.sta, f.cus
, f.cus_num, totNonComply
FROM f INNER JOIN (SELECT DISTINCT cmp, bra, sta, cus, cus_num, sec FROM b) b ON
f.cmp = b.cmp
AND f.sta = b.sta
AND f.cus = b.cus
AND f.cus_num = b.cus_num
) AS promTemp

DROP TABLE f

PRINT 'Laaaa'
PRINT '================'

SET @q3 = 0
SET @q5 = 0
SET @q6 = 0
SET @q7 = 0
SET @q8 = 0
SET @q9 = 0
SET @q10 = 0
SET @q11 = 0
SET @q12 = 0
SET @q13 = 0
SET @q14 = 0
SET @q15 = 0
SET @q16 = 0

select @q3 = CASE WHEN sum(a) IS NULL THEN 0 ELSE sum(a) END
, @q6= CASE WHEN sum(sa) IS NULL THEN 0 ELSE sum(sa) END
, @q7 = CASE WHEN sum(asa) IS NULL THEN 0 ELSE sum(asa) END
, @q8 = CASE WHEN sum(ssd) IS NULL THEN 0 ELSE sum(ssd) END
, @q9 = CASE WHEN sum(sa) IS NULL THEN 0 ELSE sum(sa) END
, @q10 = CASE WHEN sum(ss) IS NULL THEN 0 ELSE sum(sdsds) END
, @q11 = CASE WHEN sum(sds) IS NULL THEN 0 ELSE sum(sdsds) END
, @q12 = CASE WHEN sum(sdd) IS NULL THEN 0 ELSE sum(sdsds) END
, @q13 = CASE WHEN sum(sds) IS NULL THEN 0 ELSE sum(sdsd) END
, @q14 = CASE WHEN sum(sdsd) IS NULL THEN 0 ELSE sum(sdsd) END
FROM a a
WHERE 1=1
AND cmp IN (SELECT DISTINCT cmp FROM b)
AND bra IN (SELECT DISTINCT bra FROM b)
AND sta IN (SELECT DISTINCT sta FROM b)
AND cus IN (SELECT DISTINCT cus FROM b)
AND cus_num IN (SELECT DISTINCT cus_num FROM b)
AND sec IN (SELECT DISTINCT sec FROM b)
AND history_data_date <= @q18
AND history_data_date >= @q17
GROUP BY a.cmp

SELECT * INTO g FROM
(
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from annananna WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @q18
AND history_data_date >= @q17
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from mannnar WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @q18
AND history_data_date >= @q17
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from japaida WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @q18
AND history_data_date >= @q17
GROUP BY cmp, sta, cus, cus_num
) AS promoNonComply_Q_LY

SELECT @q5 = (CASE WHEN sum(totNonComply) IS NULL THEN 0 ELSE sum(totNonComply) END)
FROM (
SELECT DISTINCT g.cmp, g.sta, g.cus
, g.cus_num, totNonComply
FROM g INNER JOIN (SELECT DISTINCT cmp, bra, sta, cus, cus_num, sec FROM b) b ON
g.cmp = b.cmp
AND g.sta = b.sta
AND g.cus = b.cus
AND g.cus_num = b.cus_num
) AS promTemp

DROP TABLE g

PRINT 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
PRINT '================'

SET @thi = 0
SET @ant = 0
SET @and = 0
SET @aone = 0
SET @atwo = 0
SET @333 = 0
SET @334 = 0
SET @336 = 0
SET @337 = 0
SET @338 = 0
SET @339 = 0
SET @340 = 0
SET @341 = 0

select @thi = CASE WHEN sum(a) IS NULL THEN 0 ELSE sum(a) END
, @and= CASE WHEN sum(sa) IS NULL THEN 0 ELSE sum(sa) END
, @aone = CASE WHEN sum(asa) IS NULL THEN 0 ELSE sum(asa) END
, @atwo = CASE WHEN sum(ssd) IS NULL THEN 0 ELSE sum(ssd) END
, @333 = CASE WHEN sum(sa) IS NULL THEN 0 ELSE sum(sa) END
, @334 = CASE WHEN sum(ff) IS NULL THEN 0 ELSE sum(ff) END
, @336 = CASE WHEN sum(ff) IS NULL THEN 0 ELSE sum(dd) END
, @337 = CASE WHEN sum(ff) IS NULL THEN 0 ELSE sum(oo) END
, @338 = CASE WHEN sum(ff) IS NULL THEN 0 ELSE sum(ff) END
, @339 = CASE WHEN sum(ef) IS NULL THEN 0 ELSE sum(l) END
FROM a a

WHERE 1=1
AND cmp IN (SELECT DISTINCT cmp FROM b)
AND bra IN (SELECT DISTINCT bra FROM b)
AND sta IN (SELECT DISTINCT sta FROM b)
AND cus IN (SELECT DISTINCT cus FROM b)
AND cus_num IN (SELECT DISTINCT cus_num FROM b)
AND sec IN (SELECT DISTINCT sec FROM b)
AND history_data_date <= @349
AND history_data_date >= @345
GROUP BY a.cmp

SELECT * INTO h FROM
(
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from annananna WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @349
AND history_data_date >= @345
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from mannnar WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @349
AND history_data_date >= @345
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from japaida WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @349
AND history_data_date >= @345
GROUP BY cmp, sta, cus, cus_num
) AS promoNonComply_TY

SELECT @ant = (CASE WHEN sum(totNonComply) IS NULL THEN 0 ELSE sum(totNonComply) END)
FROM (
SELECT DISTINCT h.cmp, h.sta, h.cus
, h.cus_num, totNonComply
FROM h INNER JOIN (SELECT DISTINCT cmp, bra, sta, cus, cus_num, sec FROM b) b ON
h.cmp = b.cmp
AND h.sta = b.sta
AND h.cus = b.cus
AND h.cus_num = b.cus_num
) AS promTemp

DROP TABLE h

PRINT 'qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq'
PRINT '================'

SET @57 = 0
SET @59 = 0
SET @60 = 0
SET @61 = 0
SET @62 = 0
SET @63 = 0
SET @64 = 0
SET @65 = 0
SET @66 = 0
SET @67 = 0
SET @68 = 0
SET @69 = 0
SET @70 = 0

select @57 = CASE WHEN sum(1) IS NULL THEN 0 ELSE sum(a) END
, @60= CASE WHEN sum(2) IS NULL THEN 0 ELSE sum(sa) END
, @61 = CASE WHEN sum(3) IS NULL THEN 0 ELSE sum(asa) END
, @62 = CASE WHEN sum(4) IS NULL THEN 0 ELSE sum(ssd) END
, @63 = CASE WHEN sum(5) IS NULL THEN 0 ELSE sum(sa) END
, @64 = CASE WHEN sum(56) IS NULL THEN 0 ELSE sum(rr) END
, @65 = CASE WHEN sum(6) IS NULL THEN 0 ELSE sum(tt) END
, @66 = CASE WHEN sum(7) IS NULL THEN 0 ELSE sum(yy) END
, @67 = CASE WHEN sum(8) IS NULL THEN 0 ELSE sum(rr) END
, @68 = CASE WHEN sum(9) IS NULL THEN 0 ELSE sum(ee) END
FROM a a

WHERE 1=1
AND cmp IN (SELECT DISTINCT cmp FROM b)
AND bra IN (SELECT DISTINCT bra FROM b)
AND sta IN (SELECT DISTINCT sta FROM b)
AND cus IN (SELECT DISTINCT cus FROM b)
AND cus_num IN (SELECT DISTINCT cus_num FROM b)
AND sec IN (SELECT DISTINCT sec FROM b)
AND history_data_date <= @72
AND history_data_date >= @71
GROUP BY a.cmp

SELECT * INTO i FROM
(
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from annananna WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @72
AND history_data_date >= @71
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from mannnar WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @72
AND history_data_date >= @71
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from japaida WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @72
AND history_data_date >= @71
GROUP BY cmp, sta, cus, cus_num
) AS promoNonComply_LY

SELECT @59 = (CASE WHEN sum(totNonComply) IS NULL THEN 0 ELSE sum(totNonComply) END)
FROM (
SELECT DISTINCT i.cmp, i.sta, i.cus
, i.cus_num, totNonComply
FROM i INNER JOIN (SELECT DISTINCT cmp, bra, sta, cus, cus_num, sec FROM b) b ON
i.cmp = b.cmp
AND i.sta = b.sta
AND i.cus = b.cus
AND i.cus_num = b.cus_num
) AS promTemp

DROP TABLE i

PRINT 'sasaasaaaaaaaaaaaaaaaaaaaaa'
PRINT '=============='

SET @bs = 0
SET @ds = 0
SET @es = 0
SET @fs = 0
SET @gs = 0
SET @hs = 0
SET @is = 0
SET @ks = 0
SET @kl = 0
SET @pokl = 0
SET @kll = 0
SET @lokp = 0
SET @liuj = 0

select @bs = CASE WHEN sum(a) IS NULL THEN 0 ELSE sum(a) END
, @es= CASE WHEN sum(sa) IS NULL THEN 0 ELSE sum(sa) END
, @fs = CASE WHEN sum(asa) IS NULL THEN 0 ELSE sum(asa) END
, @gs = CASE WHEN sum(ssd) IS NULL THEN 0 ELSE sum(ssd) END
, @hs = CASE WHEN sum(sa) IS NULL THEN 0 ELSE sum(sa) END
, @is = CASE WHEN sum(as) IS NULL THEN 0 ELSE sum(sd) END
, @ks = CASE WHEN sum(ds) IS NULL THEN 0 ELSE sum(sd) END
, @kl = CASE WHEN sum(ds) IS NULL THEN 0 ELSE sum(sd) END
, @pokl = CASE WHEN sum(ds) IS NULL THEN 0 ELSE sum(sd) END
, @kll = CASE WHEN sum(ds) IS NULL THEN 0 ELSE sum(ds) END
FROM a a
WHERE 1=1
AND cmp IN (SELECT DISTINCT cmp FROM b)
AND bra IN (SELECT DISTINCT bra FROM b)
AND sta IN (SELECT DISTINCT sta FROM b)
AND cus IN (SELECT DISTINCT cus FROM b)
AND cus_num IN (SELECT DISTINCT cus_num FROM b)
AND sec IN (SELECT DISTINCT sec FROM b)
AND history_data_date <= @ijuh
AND history_data_date >= @thyg
GROUP BY a.cmp

SELECT * INTO j FROM
(
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from annananna WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @ijuh
AND history_data_date >= @thyg
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from mannnar WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @ijuh
AND history_data_date >= @thyg
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from japaida WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @ijuh
AND history_data_date >= @thyg
GROUP BY cmp, sta, cus, cus_num
) AS promoNonComply_MAT_TY

SELECT @ds = (CASE WHEN sum(totNonComply) IS NULL THEN 0 ELSE sum(totNonComply) END)
FROM (
SELECT DISTINCT j.cmp, j.sta, j.cus
, j.cus_num, totNonComply
FROM j INNER JOIN (SELECT DISTINCT cmp, bra, sta, cus, cus_num, sec FROM b) b ON
j.cmp = b.cmp
AND j.sta = b.sta
AND j.cus = b.cus
AND j.cus_num = b.cus_num
) AS promTemp

DROP TABLE j

PRINT 'LAST YEAR MAT - Issues'
PRINT '============='

SET @0103 = 0
SET @0105 = 0
SET @0106 = 0
SET @0107 = 0
SET @0108 = 0
SET @0109 = 0
SET @0110 = 0
SET @0111 = 0
SET @0112 = 0
SET @0113 = 0
SET @0114 = 0
SET @0115 = 0
SET @0116 = 0

select @0103 = CASE WHEN sum(S) IS NULL THEN 0 ELSE sum(a) END
, @0106= CASE WHEN sum(dddd) IS NULL THEN 0 ELSE sum(sa) END
, @0107 = CASE WHEN sum(ddddd) IS NULL THEN 0 ELSE sum(asa) END
, @0108 = CASE WHEN sum(d) IS NULL THEN 0 ELSE sum(ssd) END
, @0109 = CASE WHEN sum(www) IS NULL THEN 0 ELSE sum(sa) END
, @0110 = CASE WHEN sum(tt) IS NULL THEN 0 ELSE sum(efe) END
, @0111 = CASE WHEN sum(yy) IS NULL THEN 0 ELSE sum(eee) END
, @0112 = CASE WHEN sum(uu) IS NULL THEN 0 ELSE sum(fe) END
, @0113 = CASE WHEN sum(uu) IS NULL THEN 0 ELSE sum(fef) END
, @0114 = CASE WHEN sum(uuu) IS NULL THEN 0 ELSE sum(efr) END
FROM a a
WHERE 1=1
AND cmp IN (SELECT DISTINCT cmp FROM b)
AND bra IN (SELECT DISTINCT bra FROM b)
AND sta IN (SELECT DISTINCT sta FROM b)
AND cus IN (SELECT DISTINCT cus FROM b)
AND cus_num IN (SELECT DISTINCT cus_num FROM b)
AND sec IN (SELECT DISTINCT sec FROM b)
AND history_data_date <= @0118
AND history_data_date >= @0117
GROUP BY a.cmp

SELECT * INTO k FROM
(
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from annananna WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @0118
AND history_data_date >= @0117
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from mannnar WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @0118
AND history_data_date >= @0117
GROUP BY cmp, sta, cus, cus_num
UNION
select cmp, sta, cus, cus_num, count(prm_comply) AS totNonComply from japaida WHERE 1=1
AND prm_comply = 'NO'
AND history_data_date <= @0118
AND history_data_date >= @0117
GROUP BY cmp, sta, cus, cus_num
) AS promoNonComply_MAT_LY

SELECT @0105 = (CASE WHEN sum(totNonComply) IS NULL THEN 0 ELSE sum(totNonComply) END)
FROM (
SELECT DISTINCT k.cmp, k.sta, k.cus
, k.cus_num, totNonComply
FROM k INNER JOIN (SELECT DISTINCT cmp, bra, sta, cus, cus_num, sec FROM b) b ON
k.cmp = b.cmp
AND k.sta = b.sta
AND k.cus = b.cus
AND k.cus_num = b.cus_num
) AS promTemp

DROP TABLE k

DECLARE @cmp_name VARCHAR(100)
DECLARE @sta_name VARCHAR(200)

SELECT @cmp_name = COALESCE(@cmp_name + ', ', '') + cmp_name FROM tbl_companies WHERE cmp IN (@pCmp)

IF @pSta = '0' OR @pSta = ''
SET @sta_name = 'ALL'
ELSE IF @pSta = '26'
SELECT @sta_name = 'NATIONAL'
ELSE
SELECT @sta_name = COALESCE(@sta_name + ', ', '') + sta_shortname FROM tbl_states WHERE sta IN (SELECT * FROM Ret_IntValsToin(@pSta))

DROP TABLE b

Create table l
(
cmp INT,
cmp_name VARCHAR(100),
sta_name VARCHAR(200),
edi DATETIME,
item_no INT,
item_name VARCHAR(100),
fourWeeks_LY INT,
fourWeeks_TY INT,
QTR_LY INT,
QTR_TY INT,
YTD_LY INT,
YTD_TY INT,
MAT_LY INT,
MAT_TY INT
)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 1, 'this', @points1, @dist, @q1, @01, @55, @aaaaa, @0101, @as)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 2, 'iss', @points2, @dist1, @q2, @02, @56, @aaddddd, @0102, @asaa)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 3, 'tooo', @points3, @dist2, @q3, @03, @57, @thi, @0103, @bs)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 4, 'tiyyy', @points4, @dist3, @q4, @04, @58, @that, @0104, @cs)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 5, 'osssss', @points5, @dist4, @q5, @05, @59, @ant, @0105, @ds)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 6, 'Barca', @points6, @dist5, @q6, @06, @60, @and, @0106, @es)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 7, 'Real', @points7, @dist6, @q7, @07, @61, @aone, @0107, @fs)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 8, 'Mad', @points8, @dist7, @q8, @08, @62, @atwo, @0108, @gs)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 9, 'Buenos', @points9, @dist8, @q9, @09, @63, @333, @0109, @hs)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 10, 'Leon', @points10, @dist9, @q10, @10, @64, @334, @0110, @is)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 11, 'ged', @points11, @dist10, @q11, @11, @65, @336, @0111, @ks)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 12, 'ate', @points12, @dist11, @q12, @12, @66, @337, @0112, @kl)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 13, 'Left', @points13, @dist12, @q13, @13, @67, @338, @0113, @pokl)

INSERT INTO l (cmp, cmp_name, sta_name, edi, item_no, item_name, fourWeeks_LY, fourWeeks_TY, QTR_LY, QTR_TY, YTD_LY, YTD_TY, MAT_LY, MAT_TY)
VALUES (@pCmp, @cmp_name, @sta_name, @pDate, 14, 'ssing', @points14, @dist13, @q14, @14, @68, @339, @0114, @kll)

SELECT * FROM l

DROP TABLE l
END

END

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-28 : 01:37:23

1)You're mixxing DDL and DML statement - example : CREATRE TABLE and select statements. This adds recompiles. Can you separate this out.?
2) Cursors - can you make this more efficient
3)Using functions on the INSERT statements - can you avoid
4)avoid the PRINT statements

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-28 : 02:23:04

Hi Jack,

Thanks a million for sending me this post.

1) mixind DDL and DML statement means, I did not understand, can you explain it in more detail.
2) Cursor - now here can I replace this with - while exists (SELECT * FROM temptable)
3) can you expalin this as well - functions on the insert means
4) I will remove the PRINT statements

Thanks,

AB

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-28 : 04:36:01

you have a pretty bad anti-pattern going on there. Inserting stuff into permanent tables just to then select something out of them and then dropping them.

Temp tables would be better. Better yet would be to simply select the required output without storing the intermediate values somewhere. Derived tables would probably replace half of this..

If no-one knows what the code is supposed to do then re-factoring it is going to be challenging.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-28 : 11:37:50

Mixed DDL and DML - means Data Definition Lanaguage , such as tables and Data Markup Language such as SELECT, INSERT statements.The problem is caused by interleaving DML and DDL - focusing on one after the other. It isn't a guarantee there will always be a recompile.

In your code your mixing DDL followed by DML, Try and place your DDL at the top.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources