CODEWITHSUNDEEP
sqlsql-server-2008select
Ree
Ree

Reputation: 81

SQL Query - Select Statement

I have a table where the data could be like these below:

CusID | Name | State | PhyAddress

160285 | FYZPZ | NULL | 0

160285 | FYZPZ | NJ | 1

160285 | FYZPZ | NJ | 1

or

CusID | Name | State | PhyAddress

160285 | FYZPZ | NJ xx | 1

or

CusID | Name | State | PhyAddress

160285 | FYZPZ | NULL | 0

160285 | FYZPZ | NJ xx | 0

I need to get the default state for the user. And the logic is:

The result should display the physcial state first (PhyAddress = 1) and if there is no physical address (PhyAddress = 0), then it should display the mailing adress state (PhyAddress = 0). If there are none, the state can be blank.

There are multiple customers in the table and they each can have multiple rows like above and I need to get the default state for each customer.

The resuts needs to show a result of CusID, Name, State

Please help me with the query. TIA

Upvotes: 0

Views: 197

Answers (4)

Yahia
Yahia

Reputation: 70369

use (EDIT after comments):

SELECT DISTINCT Y.CusID, Y.Name, ISNULL (Y.S1, Y.S0) AS State
FROM
(
SELECT
X.CusID,
X.Name,
( SELECT MAX ( State ) FROM Address T WHERE T.PhyAddress = 1 AND T.CusID = X.CusID ) AS S1,
( SELECT MAX ( State ) FROM Address T WHERE T.PhyAddress = 0 AND T.CusID = X.CusID ) AS S0
FROM Address AS X
) AS Y

Upvotes: 1

Brian Hoover
Brian Hoover

Reputation: 7991

You could do something like 

select CusID, Name, State, PhyAddress from sometable where (CusID, PhyAddress) in
  (select CusID, max(PhyAddress) from sometable)
  group by CusID, Name, State, PhyAddress

Upvotes: 0

dansasu11
dansasu11

Reputation: 913

What have you tried?

let me give you something to get you going

 Select CusID, Name, case when phyaddress = something then something
        when
        when
        ELSE ' ' END as State

from sometable

where something

Upvotes: 0

Cyril Gandon
Cyril Gandon

Reputation: 17048

Search the maximum value of PhyAddress:

SELECT CusID, 
       State
FROM table t1
WHERE PhyAddress = 
    (
        SELECT MAX(t2.PhyAddress)
        FROM table t2
        WHERE t2.CusID = t1.CusID
    )

Upvotes: 0

Related Questions