Post reply

If branch then 'HDQ CityName, State

  • September 12, 2018 at 3:02 pm

    #368164

    I have a table and a user requested a data and he wants me to format it in a way he wants it. I can't find a way to do it but before I tell him that, I am still working on figuring this out.
    The attached file contains the data, however; instead of showing 'Branch' in a Headquarter column, he wants the value ('HDQ, and the city of HQ + State). Is it even possible? I can't think of anything.

  • September 12, 2018 at 3:12 pm

    #2005516

    NewBornDBA2017 - Wednesday, September 12, 2018 3:02 PM

    I have a table and a user requested a data and he wants me to format it in a way he wants it. I can't find a way to do it but before I tell him that, I am still working on figuring this out.
    The attached file contains the data, however; instead of showing 'Branch' in a Headquarter column, he wants the value ('HDQ, and the city of HQ + State). Is it even possible? I can't think of anything.

    You've been around long enough to know that a picture is not consumable data.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • September 12, 2018 at 3:18 pm

    #2005520

    Had to ask. Thanks.

  • September 12, 2018 at 3:20 pm

    #2005522

    SELECT *,
        MAX(CASE WHEN [Headquarters or Branch] = 'Headquarters' THEN 'HDQ, and the city of ' + BusinessCity + ', ' + State' END) OVER( PARTITION BY CompanyName) AS HQ
    FROM YourTableNameHere

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • September 13, 2018 at 10:22 am

    #2005632

    Thanks Drew for the query, worked beautifully. My development skills are still in developing stage so I am not sure what this query is doing even though it is working. Can you please help me understand?

    select [Country]
      ,[CompanyName]
      ,[Headquarters or Branch]
      ,[BusinessCity]
      ,[State] as BusinessState
      ,MailingCity
      ,MailingState
      ,[PMGPIN]
      , MAX(CASE WHEN [Headquarters or Branch] = 'Headquarters' THEN 'HDQ, ' + MailingCity + ', ' + MailingState
        end)
        OVER( PARTITION BY CompanyName) AS HQ
    FROM [PMG].[PMGDataforbook]
    where CompanyName like 'Allen Lund Company L%'

  • September 13, 2018 at 10:42 am

    #2005635

    I did understand the concept. Thanks a lot. I appreciate it.

Viewing 6 posts - 1 through 5 (of 5 total)

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