How to get ssns that occures in less than 12 month gap

Question

Post reply

How to get ssns that occures in less than 12 month gap

asita

SSCarpal Tunnel

Points: 4144
More actions
September 28, 2014 at 12:48 pm

#104246

Hello Good Afternoon,
How can i get ssns that are occured in less than 12 month period
create table #mytemp (SSN varchar(9), planID vcarchar(8), period varchar(7))
Insert Into #mytemp values ('123456789', '200R1','2013-04')
Insert Into #mytemp values ('123456789', '200R5','2014-09')
Insert Into #mytemp values ('123456789', '200R1','2012-05')
Insert Into #mytemp values ('123456789', '200R3','2013-11')
Insert Into #mytemp values ('123456789', '200R1','2014-01')
Insert Into #mytemp values ('213456789', '400R1','2014-04')
Insert Into #mytemp values ('213456789', '400R5','2014-09')
Insert Into #mytemp values ('213456789', '400R1','2013-05')
Insert Into #mytemp values ('213456786', '700R5','2014-05')
Insert Into #mytemp values ('213456786', '400R1','2013-05')
Insert Into #mytemp values ('215556786', '400P1','2013-05')
i am trying to get the below two results
1) How to find out the duplicate SSN's that were having period of less than 12 month difference
2) How to find out the duplicate SSN's that were having period of less than 12 month difference with same planID
Please Help me
expected results for Query 1
create table #ans1 (SSN varchar(9))
Insert Into #ans1 values ('123456789') --- had more than one record in 12 month span
Insert Into #ans1 values ('213456789') -- had more than one record in 12 month span
expected results for Query 2
create table #ans2 (SSN varchar(9))
Insert Into #ans2 values ('123456789') --- had more than one record in 12 month span for plan 200R1
Please help me here with your valuable advise
Thank you in Advance
Milan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick question, which SQL Server Version are you onÞ

😎

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

Why do people insist that it's ok to store SSNs in plain text?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mohit Dhiman Say Hey Kid Points: 677 More actions · Answer 3

I did not get the second scenario.

Why does (123456789,200R1) have more than one record in 12 month span.

Considering current month (i.e. September 2014) 12 month span would span uptil September 2013 i.e. 2013-09

Now, as per scenario no. two there need to be more than one records, after 2013-09 for same (SSN,PlanID)

For (123456789,200R1) there are three records:

2013-04 - Does not qualify

2012-05 - Does not qualify

2014-01 - Qualifies

So, there's only one record which satisfies above condition not more than one.. So how is it that u have it in second result??

Am i missing something?

"The price of anything is the amount of life you exchange for it" - Henry David Thoreau

Mohit Dhiman Say Hey Kid Points: 677 More actions · Answer 4

Keeping my previous question open , here's one solution to ur problem.

I came up with this quickly (so there could be better ways of doing it)..

And again if u're using this for a large dataset the queries can be optimised..

-- Scenario 1

select SSN

from #mytemp

where datediff (m,cast(period + '-01' as datetime),getdate()) <=12

group by SSN

having COUNT(SSN) >1

-- Scenario 2

select SSN,planID

from #mytemp

where datediff (m,cast(period + '-01' as datetime),getdate()) <=12

group by SSN,planID

having COUNT(SSN) >1

"The price of anything is the amount of life you exchange for it" - Henry David Thoreau