CODEWITHSUNDEEP
sql-servert-sqlfilterrow-number
Chris
Chris

Reputation: 71

SQL Server : I am trying to add a conditional select of column values based on another column

Here is my example table:

CREATE TABLE demo1 
(
    ProviderID int NOT NULL,
    ProviderClientID int NOT NULL,
    ClientIdentifier nvarchar(9) NOT NULL,
    ClientIdentifierTypeID int NOT NULL
);

INSERT INTO demo1 (ProviderID, ProviderClientID, ClientIdentifier, ClientIdentifierTypeID)
VALUES 
(5, 7, 123444567, 1),
(5, 7, 000111234, 2),
(5, 11, 145342332, 1),
(5, 12, 234212345, 1),
(5, 13, 324564332, 3),
(5, 14, 123222467, 3),
(5, 19, 234444879, 1),
(5, 19, 000111643, 2),
(5, 19, 999234252, 3),
(5, 20, 456333245, 1)

The column ClientIdentifierTypeID values mean:

1 - Drivers License #,
2 - SchoolID,
3 - StateID

So clients can be in the table multiple times with different identifier types.

I want to select each client & just their drivers license #. If they don't have drivers license, then get their state id. I don't want their school id at all.

This is going to be part of a client count I am going to do next.

Here is what I tried in my query so far (I filter out school id already):

WITH A AS
(
    SELECT
        *
    FROM
        demo1
    WHERE
        ClientIdentifierTypeID IN ('1', '3')
), B AS
(
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY [ProviderClientID] ORDER BY [ClientIdentifierTypeID]) AS rn
    FROM 
        A
)
SELECT
    *
FROM 
    B
WHERE 
    rn = 1

EDIT: I am re-reading through my query, it seems do what I want. When there is a license# rn will be 1. If no license, the state id will have rn=1.

Upvotes: 0

Views: 74

Answers (2)

Gudwlk
Gudwlk

Reputation: 1157

I use two Selects to filter. One is to get the uses who has driver License . Second block is to get the users who has Sate but not driver license. then combine both lists with UNION ALL.
This is one possibility.

 --client has driver License 
  SELECT * FROM  demo1
  WHERE ClientIdentifierTypeID  in (1)
  AND ClientIdentifierTypeID not in  (3,2)

  UNION ALL

  -- Clients who has State and not driver License 
 SELECT * FROM  demo1
 WHERE  ClientIdentifierTypeID  in (3)
 and ProviderClientID Not in (SELECT ProviderClientID FROM  demo1
 WHERE ClientIdentifierTypeID  in (1)
   )

Result

Upvotes: 1

Rahul Biswas
Rahul Biswas

Reputation: 3467

If counts clients including school id then disable where clause otherwise enable it.

-- sql server
SELECT ProviderClientID
     , COUNT(1) count_client
     , MIN(CASE WHEN ClientIdentifierTypeID = 1
                   THEN ClientIdentifier
                WHEN ClientIdentifierTypeID = 3
                   THEN ClientIdentifier
           END)
FROM demo1
-- WHERE ClientIdentifierTypeID IN ('1', '3')
GROUP BY ProviderClientID;

Upvotes: 1

Related Questions