Post reply

Partioning a table according to a certain WHERE clause

  • January 26, 2017 at 10:47 am

    #320959

    Hi there

    I have an output table for CustomerIDs with specific codes , sorted in  CustomerID order

    A sample is as follows:

    row1     effectivedate                                    CustomerCode    customerID

    1         2015-02-11 11:53:55.403                  T                            19

    2        2015-03-26 16:31:15.847                    P                          19

    1        2015-02-11 11:50:46.643                    P                         81

    2         2015-02-19 15:09:46.973                 C                            81

    1          2015-02-05 17:00:43.967                X                            1441

    2          2015-06-28 15:57:26.357               C                        1441

    1            2015-11-16 09:35:30.967            P                         1650000952

    2              2015-11-24 09:11:28.357         C                         1650000952

    Now I would like to write a query to extract all customers (customerID) where when (RowID = 1 and Customer Code = 'P') and   (RowID = 2 and Customer Code = 'C')  only.
    So in the above example, I would only extract Customers  ( 81 and    1650000952)  and not the others which are not satisfying the above criteria.

    Is there a way that I can do this by partitioning the data above withtout selecting the Ts and Xs?

    Many thanks

  • January 26, 2017 at 11:31 am

    #1925137

    SELECT customerID
    FROM table_name
    GROUP BY customerID
    HAVING MAX(CASE WHEN RowID = 1 and CustomerCode = 'P' THEN 1 ELSE 0 END) = 1 AND
      MAX(CASE WHEN RowID = 2 and CustomerCode = 'C' THEN 1 ELSE 0 END) = 1

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • January 26, 2017 at 5:18 pm

    #1925214

    ScottPletcher - Thursday, January 26, 2017 11:31 AM

    SELECT customerID
    FROM table_name
    GROUP BY customerID
    HAVING MAX(CASE WHEN RowID = 1 and CustomerCode = 'P' THEN 1 ELSE 0 END) = 1 AND
      MAX(CASE WHEN RowID = 2 and CustomerCode = 'C' THEN 1 ELSE 0 END) = 1

    Hi Scott

    That works brilliantly! thank you so much for that!

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

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