June 28, 2006 at 11:51 am
I have a problem set that you all can help me with...
I have 7 tables. All named for the months oct thru apr. For the purposes =
of this question I've shown the oct table and nov table below.
If the account is still active then the data on the account is in the =
table for each active month. If the account gets paid off then it =
doesn't show up in the next month. So in....
Oct table
mbrnbr accttype name
12345 L1 Joe
12345 L12 Joe
12356 L58 Jonnah
12356 L56 Jonnah
12398 L3.1 Jack
12343 L1 Jenn
12342 L4 Jill
Nov table
mbrnbr accttype name
12345 L1 Joe
12356 L58 Jonnah
12398 L3.1 Jack
12343 L1 Jenn
12342 L4 Jill
Joe's 12345L12 account got paid off in Oct so it doesn't show up in the =
Nov table but notice his 12345L1 account is still active.
Jonnah's 12356L56 account did the same as Joe's account but he still has =
an L58 account active in nov.
WHAT I'M TRYING TO DO IS THIS...
Find out what account was paid off and when. So in the above example I =
would get a result set of=20
12345 L12 Joe
12356 L56 Jonnah
WHAT'S NOT WORKING IS THIS...
SELECT oct.mbrnbr, oct.accttype, oct.name, nov.mbrnbr AS Expr1, =
nov.accttype AS Expr2
FROM oct left<<<and right>>> OUTER JOIN
nov ON oct.mbrnbr =3D nov.mbrnbr
WHERE (nov.mbrnbr IS NULL) AND (nov.accttype IS NULL)
I'VE ALSO TRIED SOMETHING SIMILAR TO THIS...
select oct.mbrnbr, oct.accttype, oct.name
from oct
where not exists (select null from nov where oct.mbrnbr =3D nov.mbrnbr)=20
and not exists (select null from nov where oct.accttype =3D =
nov.accttype)
These are the tables I'm working with...
CREATE TABLE [dbo].[oct](
[mbrnbr] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[accttype] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[nov](
[mbrnbr] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[accttype] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Any good ideas on how to parse the pay offs?
Thanks for any clues you might provide guys and gals,
Jim
June 28, 2006 at 12:12 pm
SELECT O.mbrnbr,
O.accttype,
O.name,
N.mbrnbr AS Expr1,
N.accttype AS Expr2
FROM Oct O
LEFT JOIN Nov N
ON O.MbrNbr = N.MbrNbr and O.AcctType = N.AcctType
WHERE N.MbrNbr IS NULL
June 28, 2006 at 1:21 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply