All Forums Database
vishalrstg 7 posts Joined 07/14
17 Jul 2015
Need Help in Query

I have volatile table with columnas as
#vol_test1 (
CaseId    varchar(40),
AccountId int,
DateOfBirth Date,
FirstName varchar(50),
LastName  varchar(50)
) Primary Index(caseId);
Situation:- For each CaseId I can have multiple accountid.
ex:-
CaseId  AccountId DateOfBirth FirstName LastName
------------------------------------------------------ -------------------
A  11  '1986-02-21' XYZ  ABC
A  22  '1985-05-22' YUZ  ABC
A  31  '1985-05-22' YUZ  ABC
B  56  '1976-05-22' PQR  DEF
B  44  '1976-05-22' PQR  DEF
B  75  '1979-08-09' RST  GHI
C  71  '1981-01-19' QXA  JKL
Req:- 1) I need to select caseid which have more than one accountid.---that i did
2) For the accountids obtained from step 1, I need to fetch accountid which have same DOB, LastName and FirstName
Expected result:-
A  22  '1985-05-22' YUZ  ABC
A  31  '1985-05-22' YUZ  ABC
B  56  '1976-05-22' PQR  DEF
B  44  '1976-05-22' PQR  DEF
 
I have tried to do with Self join and Intersect but as caseid can have any number of accountid this approach is not successful. 
Can anyone help me in this?

- Vishal
Tags:
dnoeth 4628 posts Joined 11/04
17 Jul 2015

Hi Vishal,
simply add

qualify count(*) over (partition by DateOfBirth, FirstName, LastName) > 1

 

Dieter

vishalrstg 7 posts Joined 07/14
17 Jul 2015

Hi Dieter,
 
Thanks for your response.
In the query you have mentioned, I have to add caseid as well.
 

qualify count(*) over (partition by caseid, DateOfBirth, FirstName, LastName) > 1

Though I have found another solution as well. :)
 
Thanks,

- Vishal

You must sign in to leave a comment.