|
|
Posted by Patti on 12/14/06 20:38
I apologize if I sound naive, but I am unfamiliar with what a repro
script is. I am hoping this is what you need:
tblInjectLoans:
CREATE TABLE [tblInjectLoans] (
[lnkey] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuditorID] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QualityAuditorID] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_tblInjectLoans] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
tblALPSLoans:
CREATE TABLE [tblALPSLoans] (
[IDX] [int] IDENTITY (1, 1) NOT NULL ,
[LNKEY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AuditorID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuditDate] [datetime] NULL ,
[AuditType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblALPSLoans] PRIMARY KEY CLUSTERED
(
[IDX]
) ON [PRIMARY]
) ON [PRIMARY]
GO
I tried your suggestion and it is giving me all loans from temp table A
for each auditor where the count of loans from tblInjectLoans is
greater than the lnkeycount from temp table B. I do not want all loans
from temp table A, I want enough loans inserted until the lnkeycount
for each auditor is reached. Ex.
lnkeycount for Auditor lpAAAA = 3
lnkeycount for Auditor lpBBBB = 2
lnkeycount for Auditor lpCCCC = 1
Results:
LNKEY AuditorID
00001 lpAAAAA
00005 lpAAAAA
00007 lpAAAAA
00002 lpBBBBB
00008 lpBBBBB
00003 lpCCCCC
Thanks in advance!
Erland Sommarskog wrote:
> Patti (pdavis269@worldsavings.com) writes:
> > 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.
>
> You did not say which version of SQL Server you are using. The solution
> below works on SQL 2000, but on SQL 2005 it should be possible to
> all in one query.
>
> First, add this column to @TempA:
>
> rowno int IDENTITY
>
> No you can insert all rows at once with:
>
> INSERT dbo.tblInjectLoans (lnkey, AuditorID)
> SELECT a.lnkey, a.auditorid
> FROM @TempA a
> JOIN @TempB b ON a.AuthorID = b.AuthorIS
> WHERE b.lnkey >
> a.rowno + (SELECT COUNT(il.Lnkey)
> FROM tblInjectLoans il
> WHERE il.AuditorID = a.AuditorID)
>
>
> Since you did not include a repro script (i.e. the CREATE TABLE and
> INSERT statements I was asking for) this untest.
>
> Note that the code as you have written is not deterministic in which
> loans that goes to which auditor, but nor is it anything that can be
> called random.
>
>
> --
> 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
[Back to original message]
|