|
|
Posted by Patti on 12/12/06 13:29
My original code does produce the correct result. Below I have
simplified my original code and have given some sample data. I hope
this helps clarifies what I am looking for. Thank you in advance.
/*This is how the tblInjectLoans table
looks like before I start my cursor.*/
LNKEY AuditorID
000001 lpAAAAA
000002 lpBBBBB
000003 lpCCCCC
/*I then need to find 3 percent of completed loans for each auditor and
insert it into
a temp table*/
INSERT INTO @tempB
SELECT
ROUND(COUNT(LNKEY) * 3/100, 0) AS 'LnkeyCount'
,AuditorID
FROM dbo.tblALPSLoans
GROUP BY AuditorID
/*Results of @TempB insert
AuditorID LnkeyCount
lpAAAAA 3
lpBBBBB 2
lpCCCCC 1
*/
/*Create cursor to loop through records and add a loan number to
tblinjectloans table if the number of loans in tblinjectloans for each
auditor is less than the LnkeyCount for each auditor from
@tempB*/
DECLARE @lnkey varchar(10)
DECLARE @AuditorID varchar(7)
DECLARE @var1 int
DECLARE @var2 int
DECLARE @sql varchar(4000)
DECLARE c1 CURSOR FOR
SELECT lnkey, auditorid
FROM @TempA
OPEN c1
FETCH NEXT FROM c1
INTO @LNKEY, @AuditorID
WHILE @@FETCH_STATUS = 0
BEGIN
Select @var1 = COUNT(Lnkey) from dbo.tblInjectLoans where
AuditorID=@AuditorID
Select @var2 = LnkeyCount from @tempB where
AuditorID=@AuditorID
IF @var1 < @var2
Insert into dbo.tblInjectLoans
(lnkey, AuditorID)
Values (@LNKEY, @AuditorID)
FETCH NEXT FROM c1
INTO @LNKEY, @AuditorID
END
CLOSE c1
DEALLOCATE c1
/*Desired results of tblInjectLoans when cursor is done
LNKEY AuditorID
00001 lpAAAAA
00005 lpAAAAA
00007 lpAAAAA
00002 lpBBBBB
00008 lpBBBBB
00003 lpCCCCC
*/
As you can see each auditor's count of loans is equal to the number of
LNKEYCount from @TempB. This is my ultimate desired results. I need
loans added to the tblInjectLoans for each auditor until the total for
that auditor reaches their LNKEYCount from @tempB. My original code
does produce these results. It just takes a long time to run.
Erland Sommarskog wrote:
> Patti (pdavis269@worldsavings.com) writes:
> > This was a great suggestion, thank you. I've actually been playing
> > with the code because right now it inserts every record from the
> > tblALPSLoans table into the tblinjectloans table for each auditor. But
> > I need it to insert just enough records for each auditor until the
> > percentage number for that auditor is met. So, for example, if auditor
> > A has 1 record in the tblInjectLoans table and his percentage number
> > (lnkeycount from @tempb from my original code) is 3, then I need to
> > insert only 2 more records from the tblALPSLoans table into
> > tblInjectLoans. I was playing with Top N, but that isn't working for
> > me. Hopefully, this makes sense. Any ideas would be much appreciated.
>
> So does the original code you posted produce the correct result or
> not? This is not clear to me.
>
> A good idea for this type of questions, is that you post:
>
> o CREATE TABLE statements for your tables, preferrably simplified to
> the pertinent columns.
> o INSERT statement with sample data.
> o The desired result given the sample.
>
> This make it easy to copy and paste and develop a tested solution. Also
> the test data helps to clarify the verbal resitriction.
>
> Note that the amount of sample data can be fairly small, but it should
> be big enough to cover important cases.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|