Please HELP! with this stor proc

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)

Please HELP! with this stor proc

Author

Topic

MattieBala69
Starting Member

30 Posts

Posted - 2005-06-05 : 09:43:33

Hey ,
Let me try to explain .
What this stor procedure does is It needs to count the total no of rows for that branch and then find out the 1% of that row count and show the result . For eg. lets say i have 100 rows . it should show up as 1 count. this works perfect.
THe problem i have is when i run the stor proc the first time i get the summary part of it with 1 row as it supposed to
eg1. BranchCode BranchName LoanNo LastName
17 ccc 123 Foster

THE PROBLEM IS Here when i run the stor proc the second time . I get diff set of data
eg2 . BranchCode BranchName LoanNo LastName
17 ddd 234 Baker.

Could someone PLEASE tell me what i am doing wrong and how i should be able to get the first eg. all the time when i run the stor proc .

Here is the stor proc :

CREATE PROCEDURE dbo.spr_Corr_Weekly_Can_Loans_Test '20050315','20050428'
@StartDate VARCHAR( 10 ),
@EndDate VARCHAR( 10 )

AS

SET NOCOUNT ON

SELECT B.brcBranch_ID as BranchCode,
B.brcBranchName,
M.[_@LOAN#] as LoanNo,
M.B1LNM as LastName,
P.Team,
M.PCANCELDT as CancelDt
INTO #temp1
FROM dbo.M_ALSMASTER M
LEFT JOIN dbo.L_TMOBranchs B ON M.BRANCH = B.brcBranch_ID
INNER JOIN dnaursql002.PERFORMAX.DBO.EI005P P on P.LoanNum = M.[_@LOAN#]
WHERE M.PCANCELDT BETWEEN dbo.fnTMOConvertDateToNumber( @StartDate ) AND dbo.fnTMOConvertDateToNumber( @EndDate )
AND M.UWDENIDT = 0
AND M.WIREDT = 0
AND ( B.brcBranch_ID = 17 Or B.brcBranch_ID = 21 Or B.brcBranch_ID = 22 )
ORDER BY B.brcBranchName ASC

SELECT BranchCode AS BranchCode1,
COUNT(*) AS TotalPop1,
.01 AS OnePerSele1,
Count( * ) * .01 AS Calculation1,
CASE
WHEN CAST( ROUND( COUNT( * ) * .01, 0 ) AS INT ) = 0 then 1
ELSE CAST( ROUND( COUNT( * ) * .01, 0 ) AS INT )
END AS TotSample1

INTO #SumCalc
FROM #temp1
GROUP BY BranchCode
--================declaring to get the 10% of Selected rows
Select *
INTO #TEMP2
from #temp1
where BranchCode = 17
DECLARE @rows INT

SELECT @rows = CASE
WHEN ROUND( 0.01 * COUNT( * ), 0 ) = 0 THEN 1
ELSE ROUND( 0.01 * COUNT( * ), 0 )
END
FROM #temp2
--W--HERE BranchCode = 17

SET ROWCOUNT @rows
SELECT *
INTO #Branch1
FROM #temp2
WHERE @rows > 0
--AND BranchCode = 17 -- if rowcount is 0, that means all rows

SET ROWCOUNT 0 -- reset

SELECT B1.*,
sc.TotalPop1,
sc.OnePerSele1,
sc.Calculation1,
sc.TotSample1
INTO #B1a
FROM #Branch1 B1
INNER JOIN #sumcalc Sc ON b1.BranchCode = sc.branchcode1
----====================================

CREATE TABLE #Cancel
( BranchCode INT,
BranchName VARCHAR( 50 ),
LoanNo CHAR( 10 ),
LastName VARCHAR( 50 ),
Team VARCHAR( 10 ),
CanceledDate VARCHAR( 10 ),
--SDate varchar(10),
--EDate varchar(10),
TotalPop decimal (11,2),
OnePerSele decimal (11,2),
Calculation decimal (11,2),
TOTSample decimal (11,2) NULL )

INSERT #Cancel
SELECT *
FROM #B1a
WHERE TotSample1 > 0

SELECT c.BranchCode,
c.BranchName,
c.LoanNo,
c.LastName,
c.Team,
dbo.fnTMOConvertNumberToDate( c.CanceledDate ) AS CanceledDate,
--SUBSTRING( c.CanceledDate, 5, 2 ) + '/' + SUBSTRING( c.CanceledDate, 7, 2 ) + '/' + SUBSTRING( c.CanceledDate, 1, 4 ) AS CanceledDate,
c.TotalPop,
c.OnePerSele,
c.Calculation,
c.TOTSample,
sc.*
FROM #cancel c
RIGHT JOIN #SumCalc sc ON c.BranchCode = sc.BranchCode1

Thank you,
Mattie

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-07 : 01:43:32

It would help us if you presented your question with some code that we can execute. Take a peek at this link for guidelines:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Though, at first glance it makes sense that you could be getting different results with each execution because you are not dropping those temp tables after using them. Im surprised its not erroring on you... im away from the server right now so I cannot test this for myself, but if you SELECT INTO a table_name that already exists does it not error reporting "Table already exists with that name?"

Maybe Im wrong :) But you should still clean up all those temps...

Please post some runnable code and let us help you clean up this proc! I am sure we can help you acheive the same results in a much more efficient manner.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-07 : 02:10:48

Do you always get the same 2 different results??

After a quick look at your code there is a lack of ORDERING in your SELECT statements so the chances of different results are high.

Sample data, DDL and expected results would help us to help you

Andy

Beauty is in the eyes of the beerholder

MattieBala69
Starting Member

30 Posts

Posted - 2005-06-07 : 09:36:41

Thank you soo much for replying to me Nathan and Andy.
U where right Andy, i tried the ORDER BY. It seems to work i am going to test it 4 or 5 times before i am really sure its working. I will let you guys know if that works for sure.
Once again thank you sooooo much.
Thank you,
Mattie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources