Case Statement Within TSQL

  • I am drawing a blank. I am trying to retrieve data with the following:

    SELECT ent.firstname + ' ' + ent.lastname AS "FullName",

    addr.addressline1,

    addr.addressline2,

    addr.city,

    stcd.statecode,

    addr.postalcode

    FROM

    Entity AS ent

    inner join Address addr ON addr.entityid = ent.entityid

    inner join StateCodes AS stcd ON stcd.stateid = addr.stateid

    WHERE

    ent.LastName IS NOT NULL AND LEN(ent.lastname) > 0

    ORDER BY ent.LastName

    Now, most of the addresses are flagged with isShipping = 1 and isMailing = 1. There is the possibility that there will be one person who has 2 address records, one flagged with isShipping = 1 and isMailing = 0 and the other address record flagged as isShipping = 0 and isMailing = 1.

    I need to filter the result so that I get all records that has both flags set to 1 and those records that have the isShipping = 0 and isMailing = 1. Also, if a different person has one address with the flag set to isShipping = 1 and isMailing = 0, then I need that record also.

    This sounds confusing. I am thinking I can use the Case statment, but am not sure how to apply it.

  • Please post table definitions, sample data and desired output. I'm not 100% sure I follow what you want. Read this to see the best way to post this to get quick responses.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The actual tables are much more extensive.

    CREATE TABLE [dbo].[tmpEntity](

    [EntityID] [int] NULL,

    [FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[tmpAddress](

    [ADDRESSID] [int] NOT NULL,

    [ENTITYID] [int] NOT NULL,

    [ADDRESSLINE1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ADDRESSLINE2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CITY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [STATEID] [int] NULL,

    [POSTALCODE] [nvarchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ISMAILING] [numeric](1, 0) NULL,

    [ISSHIPPING] [numeric](1, 0) NULL

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[tmpState](

    [STATEID] [int] NOT NULL,

    [STATECODE] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    ) ON [PRIMARY]

    Insert into tmpentity (entityid,firstname,lastname) values (1,'Ray','Smith')

    Insert into tmpentity (entityid,firstname,lastname) values (1,'Jane','Taylor')

    Insert into tmpentity (entityid,firstname,lastname) values (1,'Shirley','Test')

    Insert into tmpAddress

    (ADDRESSID,ENTITYID,ADDRESSLINE1,ADDRESSLINE2,CITY,STATEID,

    POSTALCODE,ISMAILING,ISSHIPPING)

    values (1,1,'123 Main St',Null,'Anywhere',1,'60001',1,1)

    Insert into tmpAddress

    (ADDRESSID,ENTITYID,ADDRESSLINE1,ADDRESSLINE2,CITY,STATEID,

    POSTALCODE,ISMAILING,ISSHIPPING)

    values (2,2,'456 Back St',Null,'Somewhere Else',1,'60001',0,1)

    Insert into tmpAddress

    (ADDRESSID,ENTITYID,ADDRESSLINE1,ADDRESSLINE2,CITY,STATEID,

    POSTALCODE,ISMAILING,ISSHIPPING)

    values (3,2,'987 Front Street',Null,'Somewhere Else',1,'60001',1,0)

    Insert into tmpAddress

    (ADDRESSID,ENTITYID,ADDRESSLINE1,ADDRESSLINE2,CITY,STATEID,

    POSTALCODE,ISMAILING,ISSHIPPING)

    values (4,3,'23452 West St',Null,'Homewood',1,'65555',1,1)

    Insert into tmpState

    (STATEID,STATECODE)

    values (1,'IL')

    Results:

    Ray Smith

    123 Main St

    Anywhere, IL 60001

    Jane Taylor

    987 Front Street (because isMailing = 1 and isShipping = 0)

    Somewhere Else, IL 60001

    Shirley Test

    23452 West St

    Homewood, IL 65555

  • I agree with Gail. Post as she has asked as your requirements are more complex than just using a CASE statement as your case statement will return all combinations of isMailing and isShipping except for when both are 0.

    I'm thinking you will probably want to use a CTE and COUNT() OVER() to get what you need, but would need the information that Gail has requested to be sure.

  • Try this...

    with wcte as

    (Select *,(Select Count(*) from tmpAddress a where a.entityID = tmpAddress.entityID) rno

    from tmpAddress )

    SELECT ent.firstname + ' ' + ent.lastname AS "FullName",

    addr.addressline1,

    addr.addressline2,

    addr.city,

    stcd.statecode,

    addr.postalcode,

    addr.isMailing,addr.IsShipping

    FROM

    tmpEntity AS ent

    inner join (Select * from tmpAddress where (IsMailing = 1 and IsShipping = 1)

    or (IsMailing = 1 and IsShipping = 0)

    or AddressID in (Select addressID from wcte where rno = 1 and IsMailing = 0 and IsShipping = 1)

    ) addr ON addr.entityid = ent.entityid

    inner join tmpState AS stcd ON stcd.stateid = addr.stateid

    WHERE

    ent.LastName IS NOT NULL AND LEN(ent.lastname) > 0

    ORDER BY ent.LastName

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Am I completely missing the point.. But It sounds like you are just asking for items where isMailing = 1

    SELECT ent.firstname + ' ' + ent.lastname AS "FullName",

    addr.addressline1,

    addr.addressline2,

    addr.city,

    stcd.statecode,

    addr.postalcode

    FROM

    Entity AS ent

    inner join Address addr ON addr.entityid = ent.entityid

    inner join StateCodes AS stcd ON stcd.stateid = addr.stateid

    WHERE

    ent.LastName IS NOT NULL AND LEN(ent.lastname) > 0 and

    addr.IsMailing =1

    ORDER BY ent.LastName

  • Here is another option which I think is a little easier to read than Atif's, but in my simple tests between the 2 does not perform quite as well and doesn't look like it will scale as well based on 3 row tests.

    ;WITH cteRows AS

    (

    SELECT

    COUNT(TA.ADDRESSID) OVER (PARTITION BY TE.EntityId) AS no_addresses,

    TE.FirstName + ' ' + TE.LastName AS name,

    TA.ADDRESSLINE1,

    TA.ADDRESSLINE2,

    TA.CITY,

    TS.STATECODE,

    TA.POSTALCODE,

    TA.ISMAILING,

    TA.ISSHIPPING

    FROM

    dbo.tmpEntity AS TE JOIN

    dbo.tmpAddress AS TA ON TE.EntityID = TA.ENTITYID JOIN

    dbo.tmpState AS TS ON TA.STATEID = TS.STATEID

    )

    SELECT

    NAME,

    AddressLine1,

    AddressLine2,

    City,

    StateCode,

    PostalCode,

    isMailing,

    isShipping

    FROM

    cteRows AS CR

    WHERE

    CASE

    WHEN CR.isMailing = 1 THEN 1

    WHEN CR.no_addresses = 1 AND CR.isMailing = 0 AND CR.isSHipping = 1 THEN 1

    ELSE 0

    END = 1

    I added this data in order to have a row that met your criteria for an entity having 1 address that is only set as the shipping address:

    Insert into tmpentity (entityid,firstname,lastname) values (4,'John','Doe')

    Insert into

    tmpAddress

    (

    ADDRESSID,

    ENTITYID,

    ADDRESSLINE1,

    ADDRESSLINE2,

    CITY,

    STATEID,

    POSTALCODE,

    ISMAILING,

    ISSHIPPING

    )

    values

    (

    5,

    4,

    '125 Main St',

    Null,

    'Anywhere',

    1,

    '60001',

    0,

    1

    )

  • To Jason:

    I need to filter the result so that I get all records that has both flags set to 1 and those records that have the isShipping = 0 and isMailing = 1. Also, if a different person has one address with the flag set to isShipping = 1 and isMailing = 0, then I need that record also.

    There is a point of complexity when an entity has a address with

    IsMailing = 0 and IsSHipping = 1. The OP needs that record as well (but just in case of single address against the respective entity)

    To Jack:

    I have tested both the queries, and the results are same and Statistics are nearly equal. Is there any performance issue that I could rectify...?

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Ah.. thanks It seems like I didn't read the question properly.

  • If I recall correctly, on my machine, mine included a worktable while yours didn't and mine had more logical reads than yours (Set Statistics IO ON). For small data sets you probably wouldn't notice the difference, but I think you would on larger ones and highly active machines.

    I mainly thought mine was easier to read and follow because yours had the CTE and a derived table with a sub-query referencing the CTE in the where clause.

  • It sounds like you want to pick the single best address for each entity, using the isMailing and isShipping fields to define "best" (with preference given to isMailing). All you have to do is define "best" with an expression in a ROW_NUMBER() ORDER BY clause.

    SELECT ent.firstname + ' ' + ent.lastname AS "FullName",

    addr.addressline1,

    addr.addressline2,

    addr.city,

    stcd.statecode,

    addr.postalcode

    FROM Entity AS ent

    INNER JOIN ( SELECT entityid,

    addressline1,

    addressline2,

    city,

    postalcode,

    ROW_NUMBER() OVER ( PARTITION BY entityid

    ORDER BY ISNULL(isMailing, 0) * 2 + ISNULL(isShipping, 0) DESC ) AS rn

    FROM Address

    ) addr ON addr.entityid = ent.entityid

    AND rn = 1

    INNER JOIN StateCodes AS stcd ON stcd.stateid = addr.stateid

    WHERE LEN(ent.lastname) > 0

    ORDER BY ent.LastName

  • Scott, I like your solution. I have found that using row_number when you need the best of several potential result records works great as long as you can find a way to order the records some how. It also works well for removing duplicate records from a table.

    If it was easy, everybody would be doing it!;)

  • Thank you very much for your help. This worked great.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply