CODEWITHSUNDEEP
sqlsql-server
Rohini Mathur
Rohini Mathur

Reputation: 441

Checking Value in multiple column for a given Group

i am not sure how to get this in SQL 2008

For a same postal code there are multiple client and having RelationNo and i am trying to find missing Relation number when a same postal code has two or more client number and any one phone number of those client is same then same trade number will be updated where it is missing or NULL or Blank

IF PHONE NUMBER IS NOT PRESENT NO ACTION.

may someone please suggest how to do in sql

Table:

DECLARE @MASTER TABLE
(
POSTAL VARCHAR (50),
CLIENT VARCHAR (50),
phone1 INT,
phone2 INT,
RelationNo VARCHAR (50)
)

INSERT @MASTER




SELECT 'M169HE',    'E5017766',016187,88888,'E100' UNION ALL
SELECT 'M169HE',    'M0111136', '', 016187, '' UNION ALL
SELECT 'IV11UF',    'E3009949', 50505,  '', 'F200' UNION ALL
SELECT 'KA48HH',    'E3087713', 01290,  '', '' UNION ALL
SELECT 'KA48HH',    'E3017126', 55665,  01290,  'G999' UNION ALL
SELECT 'KA48HH',    'E5005326', '', 55665,  '' UNION ALL
SELECT 'YO72LZ',    'C5640095', '', '', 'T8888' UNION ALL
SELECT 'YO72LZ',    'C5640096', '', '', ''

Output:

POSTAL| CLIENT  |phone1  |  phone2| RelationNo
M169HE| E5017766|16187   |88888   |E100
M169HE| M0111136|        |16187   |E100
IV11UF| E3009949|   50505|        |F200
KA48HH| E3087713|   1290 |        |G999
KA48HH| E3017126|   55665|1290    |G999
KA48HH| E5005326|        |55665   |G999
YO72LZ| C5640095|        |  T8888 |
YO72LZ| C5640096|        |        |

Thanks

Upvotes: 2

Views: 47

Answers (2)

SqlZim
SqlZim

Reputation: 38023

Using cross apply inside a common table expression to find and update the matching RelationNo for those rows missing RelationNo:

test setup: http://rextester.com/YARZ79913

;with cte as (
  select o.*, x.FoundRelationNo
    from @MASTER as o
    cross apply (
      select top 1 FoundRelationNo = i.RelationNo 
        from @MASTER  as i
        where i.Postal = o.Postal
          and nullif(i.relationno,'') is not null
          and (
             nullif(i.phone1,0) in (o.phone1, o.phone2)
          or nullif(i.phone2,0) in (o.phone1, o.phone2)
              )
        ) as x
    where nullif(o.relationno,'') is null
     and (o.phone1 > 0 or o.phone2 > 0)
 )
 --select * from cte
 update cte set RelationNo = FoundRelationNo

 select * from @MASTER

results: 

+--------+----------+--------+--------+------------+
| POSTAL |  CLIENT  | phone1 | phone2 | RelationNo |
+--------+----------+--------+--------+------------+
| M169HE | E5017766 |  16187 |  88888 | E100       |
| M169HE | M0111136 |      0 |  16187 | E100       |
| IV11UF | E3009949 |  50505 |      0 | F200       |
| KA48HH | E3087713 |   1290 |      0 | G999       |
| KA48HH | E3017126 |  55665 |   1290 | G999       |
| KA48HH | E5005326 |      0 |  55665 | G999       |
| YO72LZ | C5640095 |      0 |      0 | T8888      |
| YO72LZ | C5640096 |      0 |      0 |            |
+--------+----------+--------+--------+------------+

Upvotes: 1

iamdave
iamdave

Reputation: 12243

This can be done with a simple apply and union all:

DECLARE @t TABLE(POSTAL VARCHAR (50),
                        CLIENT VARCHAR (50),
                        phone1 INT,
                        phone2 INT,
                        RelationNo VARCHAR (50)
                        )

INSERT @t
SELECT 'M169HE',    'E5017766',016187,88888,'E100' UNION ALL
SELECT 'M169HE',    'M0111136', '', 016187, '' UNION ALL
SELECT 'IV11UF',    'E3009949', 50505,  '', 'F200' UNION ALL
SELECT 'KA48HH',    'E3087713', 01290,  '', '' UNION ALL
SELECT 'KA48HH', 'E8888888', 01290, '', 'G888' UNION ALL
SELECT 'KA48HH',    'E3017126', 55665,  01290,  'G999' UNION ALL
SELECT 'KA48HH',    'E5005326', '', 55665,  '' UNION ALL
SELECT 'YO72LZ',    'C5640095', '', '', 'T8888' UNION ALL
SELECT 'YO72LZ',    'C5640096', '', '', '' 

select t.POSTAL
        ,t.CLIENT
        ,t.phone1
        ,t.phone2
        ,tt.RelationNo
from @t t
    outer apply(select top 1 RelationNo
                from @t t2
                where t.POSTAL = t2.POSTAL
                    and t.CLIENT <> t2.CLIENT
                    and (nullif(t.phone1,0) in(t2.phone1,t2.phone2)
                        or nullif(t.phone2,0) in(t2.phone1,t2.phone2)
                        )
                    and t2.RelationNo <> ''
                ) tt
where t.RelationNo = ''

union all

select *
from @t
where RelationNo <> ''
order by 1;

Output:

+--------+----------+--------+--------+------------+
| POSTAL |  CLIENT  | phone1 | phone2 | RelationNo |
+--------+----------+--------+--------+------------+
| IV11UF | E3009949 |  50505 |      0 | F200       |
| KA48HH | E8888888 |   1290 |      0 | G888       |
| KA48HH | E3017126 |  55665 |   1290 | G999       |
| KA48HH | E3087713 |   1290 |      0 | G888       |
| KA48HH | E5005326 |      0 |  55665 | G999       |
| M169HE | M0111136 |      0 |  16187 | E100       |
| M169HE | E5017766 |  16187 |  88888 | E100       |
| YO72LZ | C5640096 |      0 |      0 | NULL       |
| YO72LZ | C5640095 |      0 |      0 | T8888      |
+--------+----------+--------+--------+------------+

Upvotes: 1

Related Questions