SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Indexing.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CSK
Constraint Violating Yak Guru

USA
489 Posts

Posted - 05/25/2006 :  00:37:29  Show Profile  Send CSK a Yahoo! Message  Reply with Quote
Dear All.
Here i am using the temp table. But i can't take a decission put an index for this table. i am updating some what , here i present that.
please suggest me the index for the table #tmp_one

CREATE TABLE #tmp_one
( sno smallint null,
h01_emp_num EMPNUM null,
amount varchar(255) null,
head varchar(255) null,
elhead varchar(255) null,
total varchar(255) null,
page_total cmn_pamt null,
element varchar(8) null,
tempamount cmn_pamt null,
amountlen smallint null,
stramount varchar(15) null,
sumstr varchar(15) null
)

*********************************************************************************************

UPDATE #tmp_one
SET head = SPACE(@extractlength - 8) + 'EARNINGS'
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = 0
WHERE sno = @sno

UPDATE #tmp_one
SET amount = RTRIM('')
, head = RTRIM('')
, elhead = RTRIM('')
, total = RTRIM('')
, element = RTRIM('')
, stramount = RTRIM('')
, sumstr = RTRIM('')
WHERE sno = @sno


UPDATE #tmp_one
SET head = SPACE(@extractlength - 5) + 'TOTAL'
WHERE sno = @sno

UPDATE #tmp_one
SET elhead = SPACE(@extractlength - 9) + 'CAR. OVER'
WHERE sno = @sno

UPDATE #tmp_one
SET amount = STUFF(STR(#t4.page_total, @extractlength, @pamt),@extractlength - 3 - @pamt,0,',')
FROM #tmp_four #t4
WHERE #t4.sno = @sno
AND #tmp_one.H01_Emp_Num = #t4.H01_Emp_Num
AND DATALENGTH(LTRIM(STR(#t4.page_total,10,2))) > 4 + @pamt
AND DATALENGTH(LTRIM(STR(#t4.page_total,10,2))) < 8 + @pamt
AND #tmp_one.sno = #t4.sno

UPDATE #tmp_one
SET amount = STR(#t4.page_total, @extractlength - 2, @pamt)
FROM #tmp_four #t4
WHERE #t4.sno = @sno
AND #tmp_one.H01_Emp_Num = #t4.H01_Emp_Num
AND #tmp_one.sno = #t4.sno

UPDATE #tmp_one
SET amount = STUFF(amount,1,0,' ')
WHERE DATALENGTH(LTRIM(amount)) < 5 + @pamt
AND #tmp_one.sno = @sno

UPDATE #tmp_one
SET amount = STUFF(amount,@extractlength - 5 - @pamt,0,',')
WHERE DATALENGTH(LTRIM(amount)) > 4 + @pamt
AND #tmp_one.sno = @sno

UPDATE #tmp_one
SET amount = STUFF(amount,1,0,' ')
WHERE DATALENGTH(LTRIM(amount)) > 4 + @pamt
AND DATALENGTH(LTRIM(amount)) < 9 + @pamt
AND #tmp_one.sno = @sno

UPDATE #tmp_one
SET amount = STUFF(amount,@extractlength - 8 - @pamt,0,',')
WHERE DATALENGTH(LTRIM(amount)) > 8 + @pamt
AND #tmp_one.sno = @sno

SELECT @noofcol = @noofcol + 1

UPDATE #tmp_one
SET head = head + SPACE(@extractlength - 8) + 'EARNINGS'
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = #tmp_four.page_total
FROM #tmp_four
WHERE #tmp_four.sno = @sno
AND #tmp_one.H01_Emp_Num = #tmp_four.H01_Emp_Num

UPDATE #tmp_one
SET elhead = elhead + SPACE(@extractlength - @len) + LTRIM(RTRIM(@element))
WHERE sno = @sno

UPDATE #tmp_one
SET element = @element
WHERE sno = @sno

/*Code added by bala - to nullify the tempamount before updating the value - because in case if there is no value in p21, then the previous value (for the previous element remain there)*/
UPDATE #tmp_one
SET tempamount = 0.0

UPDATE #tmp_one
SET tempamount = ISNULL(T3.P21_Actual_Rate,0.0)
FROM #tmp_three T3, #tmp_one T1
WHERE T1.H01_emp_num = T3.H01_Emp_num
AND T3.P09_Element_Code = T1.element
AND T1.sno = @sno

UPDATE #tmp_one
SET amountlen = DATALENGTH(LTRIM(RTRIM(STR(tempamount,@extractlength,@pamt))))
WHERE sno = @sno

UPDATE #tmp_one
SET stramount = STR(tempamount,@extractlength - 2,@pamt)
WHERE sno = @sno

UPDATE #tmp_one
SET stramount = STUFF(stramount,1,0,' ')
WHERE sno = @sno
AND amountlen < 5 + @pamt

UPDATE #tmp_one
SET stramount = STUFF(stramount,@extractlength - 5 - @pamt,0,',')
WHERE sno = @sno
AND amountlen > 4 + @pamt

UPDATE #tmp_one
SET stramount = STUFF(stramount,1,0,' ')
WHERE sno = @sno
AND amountlen > 4 + @pamt
AND amountlen < 8 + @pamt

UPDATE #tmp_one
SET stramount = STUFF(stramount,@extractlength - 8 - @pamt,0,',')
WHERE sno = @sno
AND amountlen > 7 + @pamt

UPDATE #tmp_one
SET amount = amount + stramount
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = page_total + tempamount
WHERE sno = @sno


Thanks
KK

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 05/25/2006 :  01:05:24  Show Profile  Send jen a Yahoo! Message  Reply with Quote
sno

--------------------
keeping it simple...
Go to Top of Page

CSK
Constraint Violating Yak Guru

USA
489 Posts

Posted - 05/25/2006 :  01:11:31  Show Profile  Send CSK a Yahoo! Message  Reply with Quote
thanks jen

Thanks
KK
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/25/2006 :  06:20:41  Show Profile  Reply with Quote
quote:
UPDATE #tmp_one
SET head = SPACE(@extractlength - 8) + 'EARNINGS'
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = 0
WHERE sno = @sno

UPDATE #tmp_one
SET amount = RTRIM('')
, head = RTRIM('')
, elhead = RTRIM('')
, total = RTRIM('')
, element = RTRIM('')
, stramount = RTRIM('')
, sumstr = RTRIM('')
WHERE sno = @sno



You can also combined most of the UPDATE statement into single statement. And you don't need to perform rtrim('') on empty string.

UPDATE #tmp_one
SET    head       = SPACE(@extractlength - 8) + 'EARNINGS',
       page_total = 0,
       amount     = '',
       head       = '',
       elhead     = '',
       total      = '',
       element    = '',
       stramount  = '',
       sumstr     = ''
WHERE  sno = @sno




KH


Edited by - khtan on 05/25/2006 06:21:42
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000