Partioning a table according to a certain WHERE clause

• JimReeves71

  Default port

  Points: 1410

  More actions

  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

• ScottPletcher

  SSC Guru

  Points: 101096

  More actions

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• chris.asaipillai 94746

  SSChasing Mays

  Points: 628

  More actions

  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!