More than one way to achieve the same results?

  • I have the following sql statements to create an expected output.

    The output is what I would like to see but I am looking for ideas and ways to do it maybe more efficiently? My real life production data has the GROUP BY with 6 columns already. It will be 7 columns in the group by if I use my approach as seen below. Thanks

    DECLARE @Cust table

    (CustomerID int,

    CustFlag char(1))

    Insert into @Cust values (100,'Y')

    Insert into @Cust values (200,' ')

    Insert into @Cust values (300,'Y')

    Insert into @Cust values (400,' ')

    Insert into @Cust values (500,'Y')

    --select * from @cust

    DECLARE @CustLoc table

    (CustomerID int,

    Loc int)

    insert into @CustLoc values (100,0)

    insert into @CustLoc values (100,1)

    insert into @CustLoc values (100,2)

    insert into @CustLoc values (200,0)

    insert into @CustLoc values (200,20)

    insert into @CustLoc values (200,21)

    insert into @CustLoc values (200,22)

    insert into @CustLoc values (200,23)

    insert into @CustLoc values (300,30)

    insert into @CustLoc values (300,31)

    insert into @CustLoc values (400,40)

    insert into @CustLoc values (400,41)

    insert into @CustLoc values (500,0)

    --select * from @CustLoc

    DECLARE @OutTable table

    (CustomerID int,

    InvoiceLoc varchar(50))

    -- subset of customers with CustFlag = 'Y' does not require the location value on their invoices

    Insert into @OutTable

    select distinct c.CustomerID, 'Don''t care to state location'

    from @Cust c where c.CustFlag='Y'

    -- subset of customers with CustFlag = ' ' require a specific location per invoice

    Insert into @OutTable

    select c.CustomerID, cl.Loc

    from @CustLoc cl inner join @Cust c

    on cl.CustomerID = c.CustomerID

    where c.CustFlag=' '

    group by c.CustomerID, cl.Loc

    -- this select shows the expected output table

    select * from @OutTable order by CustomerID, InvoiceLoc

  • SELECT C.CustomerID,

    Loc = ISNULL(CONVERT(VARCHAR(50), CL.Loc), 'Do not care')

    FROM @Cust C

    LEFT

    JOIN @CustLoc CL

    ON CL.CustomerID = C.CustomerID

    AND C.CustFlag = ' ';

    Paul

  • Thank you, Paul. Your solution is neat and easier to read.

  • You are welcome.

Viewing 4 posts - 1 through 3 (of 3 total)

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