Flag off similar records..

[gyfu]

Hey guys,
I have been searching high and low for a solution. I hope you guys can help me on this.

Here is the case:

1 table with duplicate SSN

TableName: CustInfo
Name SSN CustNO Flag
-----------------------------
Ali 1120 78643
Ali 1120 98732
Sammy 345 36791
John 09212
Mia 3344 11224
Mia 3344 78303

Another Table links to CustInfo by CustNo

TableName: AcctInfo
CustNo ActiveDate
----------------------
78643 03/06/2005
98732 09/08/2004
35791 12/01/2001
09212 11/10/2003
11224 12/14/2002
78303 01/29/2006

I am trying to update the record with only the duplicate ssn with a value of 'O' against the earlier date.

The output should be as follows:

TableName: CustInfo
Name SSN CustNO Flag
-----------------------------
Ali 1120 78643
Ali 1120 98732 O
Sammy 345 36791
John 09212
Mia 3344 11224 O
Mia 3344 78303

I have been trying so many methods,I hope there is a way to do it.

Thanks.



-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[bborissov]

[COLOR=blue]DECLARE[/color] @CustInfo [COLOR=blue]TABLE[/color] ([COLOR=blue]Name[/color] [COLOR=blue]varchar[/color](20), SSN  [COLOR=blue]Char[/color](4), CustNO [COLOR=blue]char[/color](5))
[COLOR=blue]DECLARE[/color] @AcctInfo [COLOR=blue]TABLE[/color] (CustNO [COLOR=blue]char[/color](5), ActiveDate [COLOR=#FF00FF]datetime[/color])

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @CustInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'Ali'[/color],[COLOR=red]'1120'[/color] ,[COLOR=red]'78643'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @CustInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'Ali'[/color],[COLOR=red]'1120'[/color] ,[COLOR=red]'98732'[/color])    
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @CustInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'Sammy'[/color],[COLOR=red]'345'[/color],[COLOR=red]'36791'[/color])  
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @CustInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'John'[/color],[COLOR=red]''[/color]    ,[COLOR=red]'09212'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @CustInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'Mia'[/color],[COLOR=red]'3344'[/color] ,[COLOR=red]'11224'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @CustInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'Mia'[/color],[COLOR=red]'3344'[/color] ,[COLOR=red]'78303'[/color])

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @AcctInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'78643'[/color],[COLOR=red]'20050306'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @AcctInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'98732'[/color],[COLOR=red]'20040908'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @AcctInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'35791'[/color],[COLOR=red]'20011201'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @AcctInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'09212'[/color],[COLOR=red]'20031110'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @AcctInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'11224'[/color],[COLOR=red]'20021214'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @AcctInfo [COLOR=blue]VALUES[/color] ([COLOR=red]'78303'[/color],[COLOR=red]'20060129'[/color])


[COLOR=blue]SELECT[/color] CustInfo.*,
       [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Acct.Ssn [COLOR=blue]IS[/color] NULL
            [COLOR=blue]THEN[/color] [COLOR=red]''[/color]
            [COLOR=blue]ELSE[/color] [COLOR=red]'O'[/color] [COLOR=blue]END[/color] [COLOR=blue]AS[/color] Flag
[COLOR=blue]FROM[/color] ([COLOR=blue]SELECT[/color] CustInfo.*, AcctInfo.ActiveDate
             [COLOR=blue]FROM[/color] @CustInfo CustInfo
                  [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @AcctInfo AcctInfo [COLOR=blue]ON[/color] CustInfo.CustNo = AcctInfo.CustNo) CustInfo
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] CustInfo.Ssn, [COLOR=#FF00FF]MAX[/color](AcctInfo.ActiveDate) [COLOR=blue]AS[/color] ActiveDate
                  [COLOR=blue]FROM[/color] @CustInfo CustInfo
                  [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] @AcctInfo AcctInfo [COLOR=blue]ON[/color] CustInfo.CustNo = AcctInfo.CustNo
                  [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] Ssn) Acct
[COLOR=blue]ON[/color] CustInfo.Ssn = Acct.Ssn AND CustInfo.ActiveDate < Acct.ActiveDate
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] [COLOR=blue]Name[/color], CustNo

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

create table #custinfo (name varchar(5), ssn varchar (5) , custno int, flag char(1))

insert into #custinfo
values ( 'ali', '1120', 78643,null)
insert into #custinfo
values ( 'ali', '1120', 98732,null)
insert into #custinfo
values ( 'john', null, 09212,null)
insert into #custinfo
values ( 'mia', '3344', 11224,null)
insert into #custinfo
values ( 'mia', '3344', 78303,null)
insert into #custinfo
values ( 'sammy', 345, 36791,null)

create table #acctinfo (custno int, activedate datetime)

insert into #acctinfo
values (78643, '03/06/2005')
insert into #acctinfo
values (98732, '09/08/2004')
insert into #acctinfo
values (09212, '11/10/2003')
insert into #acctinfo
values (11224, '12/14/2002')
insert into #acctinfo
values (78303, '01/29/2006')
insert into #acctinfo
values (36791, '12/01/2001')

select * from #custinfo
Select * from #acctinfo

update #custinfo
set flag = 'O'
--select * 
from #custinfo t3 join 
(select  d.custno, d.ssn, d.activedate from
(select t2.custno, ssn, activedate from #custinfo t2
join #acctinfo c2 on t2.custno = c2.custno)d
join
(select ssn, min(activedate) as activedate from
(select t.custno, activedate, t.ssn from #custinfo t join
(select ssn from #custinfo group by ssn having count(*)>1) a on t.ssn = a.ssn
join #acctinfo c1 on t.custno = c1.custno) b
group by b.ssn) c on c.ssn = d.ssn and c.activedate = d.activedate ) e on t3.custno = e.custno

select * from #custinfo

"NOTHING is more important in a database than integrity." ESquared

 

[gyfu]

Thanks Guys. This Works well..

-- gyfu --
Crystal Ver 10
Microsoft SQL