x

Case or If/Then Statement

I need to build a temp table and as it is being built I'm running into problems identifying uniqueness.

I have a list of customers with the PK would be Acct and Mstr_Acct

ACCT #    Master ACCT          Grouping   
1             1                 a
2             1                 b
3             3                 a
4             3                 NULL
5             5                 NULL

What I need is just a Y/N flag in the grouping - if the ACCT Number Belongs to a grouping Then Y if Not then N so Master Acct 3 would have a Y and Master Acct 5 would have a N

But as it is right now if i do left joins it will create an acct / master acct / Grouping combonation - all I want is Y/N....
more ▼

asked Jan 28, 2011 at 09:36 AM in Default

siera_gld gravatar image

siera_gld
1k 77 82 84

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
 More info to view.
    Customers can have differenct contract numbers so we are just testing the absolute. 
`if there is a contract on file for any of the customers it would be "Y" Else "N" 
so the customer below should only display one record indicating "Y"
    SELECT DISTINCT
        r.CUST_ACCT_ID AS ACCT_NUM,
        r.PRI_ACCT_IND AS PRIMARY_ACCOUNT,
        r.CUST_NUM AS PRI_ACCT_NUM,
        r.CUST_ACCT_NAM,
        CASE WHEN (cvo.CNTRCT_ID) IS NULL  THEN 'N' ELSE 'Y' END AS CARVOUT_LEAD
    Results
    ACCT_NUM    PRIM_ACCT  PRI_ACCT_NUM CUST_ACCT_NAM   SPCL_GRP
    050774    Y           050774           ACME CUSTOMER   N
050774 Y 050774 ACME CUSTOMER Y
more ▼

answered Jan 28, 2011 at 10:32 AM

siera_gld gravatar image

siera_gld
1k 77 82 84

(comments are locked)
10|1200 characters needed characters left

So all you want is

Acct     MasterAcct    Grouping
1        1             Y
2        1             Y
3        3             Y
4        3             Y
5        5             N

In which case, something like this:

Setting up temporary table & data

declare @t table (Acct int, MasterAcct int)
insert into @t select 1,1 union all select 2,1 union all select 3,3 union all select 4,3 union all select 5,5

and the query itself:

;
WITH T2
     AS (SELECT Acct,
                MasterAcct,
                Rank() OVER (partition BY masteracct ORDER BY acct DESC) AS AccRank
         FROM   @t)
SELECT Acct,
       MasterAcct,
       CASE
         WHEN AccRank <> 1
              AND Acct = MasterAcct THEN 'Y'
         WHEN Acct <> MasterAcct THEN 'Y'
         ELSE 'N'
       END
FROM   T2
ORDER  BY Acct,
          MasterAcct 
more ▼

answered Jan 31, 2011 at 06:34 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x104
x12

asked: Jan 28, 2011 at 09:36 AM

Seen: 1398 times

Last Updated: Jan 28, 2011 at 09:58 AM