|
|
Posted by Patti on 12/11/06 19:36
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.
Ed Murphy wrote:
> Patti wrote:
>
> > I am new to SQL and have created a stored procedure for a .net web
> > application. Unfortunately I had to use a cursor in the stored
> > procedure, so it is taking several minutes to execute because it has to
> > read through about 15,000 records. There must be another way to do
> > what I'm trying to do without a cursor. I've tried temp tables and
> > case statements, but I can't seem to get them to work. I've been
> > trying to figure this out for over a week and I am just running into a
> > wall. Some expert advise would be much appreciated. My code is below.
> > Thank you in advance.
> >
> >
> > --Insert records into first temp table
> > DECLARE @tempA TABLE
> > (
> > lnkey varchar(10),
> > AuditorID varchar(7)
> > )
> >
> > INSERT INTO @tempA
> >
> > SELECT
> > LNKEY
> > ,AuditorID
> >
> > FROM
> > dbo.tblALPSLoans
> > WHERE AuditDate BETWEEN @BegDate AND @EndDate --parameters from my
> > application
> > AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
> > 'ADDED')
> > AND AuditType = @AuditType --parameter from my application
> >
> >
> > --Insert percentage value of Pre-Funding completes for each auditor
> > into temp table B
> > DECLARE @tempB TABLE
> > (
> > LnkeyCount int,
> > AuditorID varchar(7)
> > )
> >
> > INSERT INTO @tempB
> >
> > SELECT
> > ROUND(COUNT(LNKEY) * @Percent/100, 0) AS 'LnkeyCount'
> > ,AuditorID
> > FROM dbo.tblALPSLoans
> > WHERE AuditDate BETWEEN @BegDate AND @EndDate
> > AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
> > 'ADDED')
> > GROUP BY AuditorID
> >
> >
> >
> > /*Create cursor to loop through records and add a loan number to
> > tblinjectloans if the number of loans in tblinjectloans for each
> > auditor is less than the percentage value 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
>
> Untested:
>
> insert into tblInjectLoans (lnkey, AuditorID)
> select lnkey, AuditorID
> from tblALPSLoans al
> where AuditDate between @BegDate and @EndDate
> and AuditorID in (
> select lanid
> from tblEmployees
> where actiontype = 'ADDED'
> )
> and AuditType = @AuditType
> and (select count(lnkey)
> from tblInjectLoans il
> where il.AuditorID = al.AuditorID
> )
> < (select round(count(lnkey) * @Percent/100, 0)
> from tblALPSLoans al2
> where al2.AuditDate between @BegDate and @EndDate
> and al2.AuditorID = al.AuditorID
> )
Navigation:
[Reply to this message]
|