compare data in a single table by month period

Question

Post reply

compare data in a single table by month period

asita

SSCarpal Tunnel

Points: 4144
More actions
May 28, 2014 at 10:03 am

#396827

Hello Please.,
i would like to see the 2014-06 matched results (3rd query),
if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'
i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06
select c.* from (
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join
)
Please Help me with this
Thank you very much in Advance
Asitti

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

Are you looking for something like this?

😎

;WITH TEST AS

(

SELECT 'KM' AS Channel, '2012-06' AS TrailMonth UNION ALL

SELECT 'KX' AS Channel, '2012-06' AS TrailMonth UNION ALL

SELECT 'KM' AS Channel, '2012-06' AS TrailMonth UNION ALL

SELECT 'KM' AS Channel, '2012-01' AS TrailMonth UNION ALL

SELECT 'KM' AS Channel, '2012-06' AS TrailMonth

)

SELECT * FROM (

SELECT

T.Channel

,T.TrailMonth

,COUNT(*) OVER (PARTITION BY Channel,TrailMonth) AS TCNT

FROM TEST T ) AS X WHERE X.TCNT = 1

Results

Channel TrailMonth TCNT

------- ---------- -----

KM 2012-01 1

KX 2012-06 1

sgmunson SSC Guru Points: 110640 More actions · Answer 2

I'm going to go with a literal interpretation of the original poster's written words:

that he/she wants records that don't appear in BOTH of the previous year queries.

Here's my shot:

;WITH PRIOR_YEAR_DATA AS (

SELECT ssn, acctno

FROM jnj.drgSamples

WHERE Channel = 'KM'

AND TrailMonth = '2012-06'

INTERSECT

SELECT ssn, acctno

FROM jnj.drgSamples

WHERE Channel = 'KM'

AND TrailMonth = '2013-06'

)

SELECT S.ssn, S.acctno

FROM jnj.drgSamples AS S

LEFT OUTER JOIN PRIOR_YEAR_DATA AS PYD

ON S.ssn = PYD.ssn

AND S.acctno = PYD.acctno

WHERE S.Channel = 'KM'

AND S.TrailMonth = '2014-06'

AND PYD.ssn IS NULL;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Please, tell me that all of those tables that have SSN in them at least have the SSN properly encrypted. If not, please tell me the name of the company that you're working for so that I can report them to the various government agencies that would be interested in knowing about it. I promise to keep you out of it. PM me with the company name if that'll make it easier.

--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)

asita SSCarpal Tunnel Points: 4144 More actions · Answer 4

Hi Jeff,

i just comeup with ssn its actually an account holder unique id just for firm, i am working for major pharma industry

hope this helps

Best Regards

MIla