Need Help in Query

Database
Enthusiast

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?

Tags (1)
2 REPLIES
Senior Apprentice

Re: Need Help in Query

Hi Vishal,

simply add

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

Re: Need Help in Query

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,