Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Customer categorisation query

Hi

Putting it out to the helpful experts again.

Trying to categorise customers by certain purchase combinations. Difficulty being that i'm having to rely on item description to ensure all data is captured and so am using the "contains" condition.

Categories i'm trying to cover are as follows,

  • Group 1: Product type A/ Product type B/ Product type C, Product Type D), Product type E), Product Type F
  • Group 2: Product type A/ Product type B/ Product type C, Product Type D), Product type E),
  • Group 3: Product type A/ Product type B/ Product type C, Product Type D),
  • Group 4: Product type A/ Product type B/ Product type C
  • Group 5: Product type A/ Product type B/

EG Group one contains only active BPs that purchase A&B&C&D&E&F

Group two contains only active BPs that purchase A&B&C&D&E

and so on.

Initial query is here but am running into issues going beyond the one product type.

SELECT count(distinct T0.[CardCode]) [No Of Customers]

FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T1.[Dscription] Like '%%[%0]%%' and cast(T1.DocDate as date) >= dateadd(dd, -180, cast(getdate() as date))

Many thanks in advance for any advice!

replied

ha, then remove the outer part

SELECT T0.CardCode,
MAX(CASE WHEN T2.QryGroup1 = 'N' THEN 0 ELSE 1 END) TypeA,
MAX(CASE WHEN T2.QryGroup2 = 'N' THEN 0 ELSE 1 END) TypeB,
MAX(CASE WHEN T2.QryGroup3 = 'N' THEN 0 ELSE 1 END) TypeC,
MAX(CASE WHEN T2.QryGroup4 = 'N' THEN 0 ELSE 1 END) TypeD,
MAX(CASE WHEN T2.QryGroup5 = 'N' THEN 0 ELSE 1 END) TypeE,
MAX(CASE WHEN T2.QryGroup6 = 'N' THEN 0 ELSE 1 END) TypeF


FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

WHERE T0.DocDate BETWEEN GETDATE()- 180 AND GETDATE()
GROUP BY T0.CardCode

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question