none
Query Help

    Question

  • Hello experts,

    We have sample records as :- 

    CREATE TABLE #TEMP
    (
    	CL			VARCHAR(12),
    	CNAME		VARCHAR(50),
    	CTP		VARCHAR(2),
    	CADD1		VARCHAR(40),
    	PID			VARCHAR(20),
    	PENT		VARCHAR(1),
    	PNAME		VARCHAR(50),
    	PADD1		VARCHAR(40)
    )
    
    INSERT INTO	#TEMP VALUES ( '130048977', 'Demester Scott', 'XA', '3300 Main Rd', '1102010005', 'H', 'Demester Scott', '3300 Main Rd' )
    INSERT INTO	#TEMP VALUES ( '130048977', 'Demester Scott', 'XA', '3300 Main Rd', '1102010005', 'H', 'Demester Scott', '3300 Main Rd' )
    INSERT INTO	#TEMP VALUES ( '130048977', 'Demester Scott', 'XA', '3300 Main Rd', '1100002902', 'Q', 'Demester Scott', '3300 Main Rd' )
    INSERT INTO	#TEMP VALUES ( '130048977', 'Demester Scott', 'XA', '3300 Main Rd', '1100002902', 'Q', 'Demester Scott', '3300 Main Rd' )
    INSERT INTO	#TEMP VALUES ( '130048977', 'Inova Scott', 'FF', '463 Breeze Dr', '1102000609', 'H', 'Inova Scott', '13135 Jackson St' )
    INSERT INTO	#TEMP VALUES ( '130048977', 'Inova Scott', 'FF', '463 Breeze Dr', '1102000609', 'H', 'Inova Scott', '13135 Jackson St' )
    INSERT INTO	#TEMP VALUES ( '130048977', 'Inova Scott', 'FF', '463 Breeze Dr', '1102011848', 'Q', 'Inova Scott', '13135 Jackson St' )
    INSERT INTO	#TEMP VALUES ( '130048977', 'Inova Scott', 'FF', '463 Breeze Dr', '1102011848', 'Q', 'Inova Scott', '463 Breeze Dr' )
    
    INSERT INTO	#TEMP VALUES ( '130046570', 'John Smith', 'XA', '123 Jack Ave', '1102123405', 'H', 'John Smith', '123 Jack Ave' )
    INSERT INTO	#TEMP VALUES ( '130046570', 'John Smith', 'XA', '123 Jack Ave', '1102123405', 'Q', 'John Smith', '123 Jack Ave' )
    INSERT INTO	#TEMP VALUES ( '130046570', 'Ramdin John', 'FF', '57 Arlington Blvd', '1102054809', 'H', 'Ramdin John', '57 Main Street' )
    INSERT INTO	#TEMP VALUES ( '130046570', 'Ramdin John', 'FF', '57 Arlington Blvd', '1102054809', 'Q', 'Ramdin John', '57 Main Street' )
    
    SELECT	*
    FROM	#TEMP
    
    DROP TABEL #TEMP

    If  ( CTP = 'XA' & PENT = 'H' THEN match CNAME = PNAME ) AND ( CTP = 'FF' THEN match CADD1 = PADD1 ) for each CL #

    and finally list only DISTINCT PID for above matched records.

    Expected o/p:-

    --/*First Step Expected o/p*/
    SELECT '130048977' AS 'CL', 'Demester Scott' AS 'CNAME', 'XA' AS 'CTP', '3300 Main Rd' AS 'CADD1', '1102010005' AS 'PID', 'H' AS 'PENT', 'Demester Scott' AS 'PNAME', '3300 Main Rd' AS 'PADD1'
    UNION ALL
    SELECT '130048977' AS 'CL', 'Demester Scott' AS 'CNAME', 'XA' AS 'CTP', '3300 Main Rd' AS 'CADD1', '1102010005' AS 'PID', 'H' AS 'PENT', 'Demester Scott' AS 'PNAME', '3300 Main Rd' AS 'PADD1'
    UNION ALL
    SELECT '130048977' AS 'CL', 'Inova Scott' AS 'CNAME', 'FF' AS 'CTP', '463 Breeze Dr' AS 'CADD1', '1102011848' AS 'PID', 'Q' AS 'PENT', 'Inova Scott' AS 'PNAME', '13135 Jackson St' AS 'PADD1'
    UNION ALL
    SELECT '130048977' AS 'CL', 'Inova Scott' AS 'CNAME', 'FF' AS 'CTP', '463 Breeze Dr' AS 'CADD1', '1102011848' AS 'PID', 'Q' AS 'PENT', 'Inova Scott' AS 'PNAME', '463 Breeze Dr' AS 'PADD1'
    
    --/*Final Step Expected o/p*/ 
    SELECT	'130048977' AS 'CL', '1102010005' AS 'PID'
    UNION ALL
    SELECT	'130048977' AS 'CL', '1102011848' AS 'PID'



    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS




    • Edited by _Kumar Sunday, September 08, 2013 7:12 AM
    Sunday, September 08, 2013 4:19 AM

Answers

  • Hi Kumar,

    I'm not sure that I fully understand the logic of your requirement, but perhaps this script can help you find a satisfactory solution:

    with cte1
    as
    (select * from #temp where ctp='XA' and pent='H' and cname = pname),
    cte2
    as
    (select * from #temp where ctp='FF' and cadd1 = padd1)
    
    SELECT a.cl, a.pid
    FROM cte1 as "a" inner join cte2 as "b"
    on a.cl = b.cl
    union
    select b.cl, b.pid
    from cte2 as "b" inner join cte1 as "a"
    on b.cl = a.cl;

    Regards

    Rudolf Swiers

    • Marked as answer by _Kumar Monday, September 09, 2013 4:52 AM
    Sunday, September 08, 2013 11:16 AM

All replies

  • I tried below query but that is not working correctly for me.

    SELECT	*
    FROM	#TEMP a
    WHERE	EXISTS
    		(
    			SELECT	1
    			FROM	#TEMP b
    			WHERE	a.CL = b.CL
    						AND b.CTP = 'XA'
    						AND b.PENT = 'H'
    						AND b.CNAME = b.PNAME
    		)
    			AND EXISTS
    			(
    				SELECT	11
    				FROM	#TEMP b
    				WHERE	a.CL = b.CL
    							AND b.CTP = 'FF'
    							AND b.CADD1 = b.PADD1
    			)

    Please help me out.

    Thanks in advance

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS

    Sunday, September 08, 2013 5:06 AM
  • Hello Kumar,

    If I understand your requirement correctly the query below is what you need.

    SELECT	DISTINCT a.CL, a.PID
    FROM  #TEMP a
    WHERE (a.CTP = 'XA' AND a.PENT = 'H' AND a.CNAME = a.PNAME) 
        OR (a.CTP = 'FF' AND a.CADD1 = a.PADD1)
    

    output:

    CL              PID

    130046570 1102123405
    130048977 1102010005
    130048977 1102011848

    PID '1100002902'  in your final output doesn't satisfy your requirement  CTP = 'XA' & PENT = 'H' . So it is excluded in my query above.

    ================================

    Please mark as answered if this solves your problem.


    --sIbu

    Sunday, September 08, 2013 6:54 AM
  • Hello Sibu,

    No my expected o/p is as below:-

    SELECT	'130048977' AS 'CL', '1102010005' AS 'PID'
    UNION ALL
    SELECT	'130048977' AS 'CL', '1102011848' AS 'PID'

    CL = '130046570' should NOT come up as for this CL value only name matches and NOT address.

    In your query I wanted to do AND in place of OR operator, basically I want to match both name and address if matches than only I want PID, else I want to exclude CL from the final expected o/p.

    Makes sense now?

    Let me know if you have any more questions for me.

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS


    • Edited by _Kumar Sunday, September 08, 2013 7:10 AM
    Sunday, September 08, 2013 7:03 AM
  • In your query I wanted to do AND in place of OR operator, basically I want to match both name and address if matches than only I want PID, else I want to exclude CL from the final expected o/p.

    Makes sense now?

    If ( CTP = 'XA' & PENT = 'H' THEN match CNAME = PNAME ) AND ( CTP = 'FF' THEN match CADD1 = PADD1 ) for each CL #

    If it is AND that you want how can "CTP = 'XA' AND CTP = 'FF'" be TRUE at the same time??

    For CL '130046570'  there is PID 1102123405 for which name and address match as per your sample data and that's why it is there is the output. You can try the below one which checks for name and address.

    SELECT	DISTINCT a.CL, a.PID
    FROM  #TEMP a
    WHERE ((a.CTP = 'XA' AND a.PENT = 'H') 
        OR (a.CTP = 'FF'))  
        AND (a.CNAME = a.PNAME AND a.CADD1 = a.PADD1)
    


    --sIbu

    Sunday, September 08, 2013 7:23 AM
  • Hi Again,

    For XA type I'm looking for Name & for FF type I'm looking for Address.

    For CL = '130048977',
          First Check Type = 'XA' AND PENT = 'H' THAN match CNAME = PNAME only for those records (in my example for this CL #, we have only 2 records with PID = '1102010005'

          Second Check Type = 'FF' THAN match CADD1 = PADD1 only for those records (in my example for this CL #, we have only 1 record with PID = '1102011848' out of 4 records, other 3 records has PADD1 values = '13135 Jackson St'

    Now as we get at least one PID match from both of above 2 checks, I want only those PID's from CL = '130048977' which are ( '1102010005' AND '1102011848' )

    For CL = '130046570',
           First check matches but second check doesn't matches as we both the addresses has value of '57 Main Street' hence this CL is out from the final expected result set.

    Makes sense now?

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS

    Sunday, September 08, 2013 7:40 AM
  • Hi Kumar,

    I'm not sure that I fully understand the logic of your requirement, but perhaps this script can help you find a satisfactory solution:

    with cte1
    as
    (select * from #temp where ctp='XA' and pent='H' and cname = pname),
    cte2
    as
    (select * from #temp where ctp='FF' and cadd1 = padd1)
    
    SELECT a.cl, a.pid
    FROM cte1 as "a" inner join cte2 as "b"
    on a.cl = b.cl
    union
    select b.cl, b.pid
    from cte2 as "b" inner join cte1 as "a"
    on b.cl = a.cl;

    Regards

    Rudolf Swiers

    • Marked as answer by _Kumar Monday, September 09, 2013 4:52 AM
    Sunday, September 08, 2013 11:16 AM