on 11-03-2015 12:12 PM
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,
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!
HI
I didn't understand what you need
can you explain what you need in your query?
shachar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sorry, a little convoluted.
I need to be able to see customers that have purchased items containing a description (eg cards). I can do this for one product selection but not multiple. I'd like to be able to select up to 6.
An example would be
All customers that have purchased this combination - CARDS and PAPER and RIBBON within the last 180 days.
Unfortunately, we have hundreds of products that are grouped differently and would like to include all products that contain the word card (for instance), hence the Like '%%[%0]%%'
Looking at that even i think it's crazy, Any help would be great though!
HI
you can try to display 6 text fields to the customer to define the words you wont to find inside the description
Try this query :
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]%%' or T1.[Dscription] Like '%%[%1]%%'
or T1.[Dscription] Like '%%[%2]%%' or T1.[Dscription] Like '%%[%3]%%'
or T1.[Dscription] Like '%%[%4]%%' or T1.[Dscription] Like '%%[%5]%%'
and cast(T1.DocDate as date) >= dateadd(dd, -180, cast(getdate() as date))
shachar
Thanks - the input fields upon executing the query are exactly what i want, but i think the issue there is that i'm not seeing a combination.
This query gives me anyone that's purchased any of these things, and i need to see only those that have purchased a combination of the item descriptions.
HI
You should try to put and between the conditions
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 T1.[Dscription] Like '%%[%1]%%'
and T1.[Dscription] Like '%%[%2]%%' and T1.[Dscription] Like '%%[%3]%%'
and T1.[Dscription] Like '%%[%4]%%' and T1.[Dscription] Like '%%[%5]%%'
and cast(T1.DocDate as date) >= dateadd(dd, -180, cast(getdate() as date))
shachar
Sure, thanks.
This still doesn't solve my issue of being able to see customers that are purchasing a combination of items.
Example - i want to see only those customers that purchase items with attributes 1, 2 AND 3. I don't want to see those outside that combination. I'm looking at INV1 table - should i be looking elsewhere? Very frustrating!
Thanks again!
u can query ur OINV and INV1 and use the item code there and sub query your OITM to get the category, since u r using attribute there, u can always add 6 columns for ur A-F category, and give 0 for item not in group, and 1 for in group.
so now, u have a result set some like
cardcode, itemcode, GroupA, GroupB, GroupC, GroupD, GroupE, GroupF
xxxxxx abcd 1 1 1 1 1 1
with above set, u can sum on GroupA, GroupB, GroupC, etc
and for ur customer group 1, u just need to make sure those SUM of GroupA, GroupB, etc are not 0.
This is not hard. If u want, u can send me teamview.
Frank
Hi Frank
Been a few days where i walked away from it.
Bit of background, i'm just a B1 user, not an expert and have traditionally created very simple queries using query generator, so this is all a bit over my head - really keen to expand the knowledge though!
I can see what you're getting at with the above but to get there using my limited knowledge is proving a bit much! Appreciate your time and if you're able to help out with what that query might look like from start to finish that would be great - although understand if that's too much of an ask!
Thanks
Lisa
SELECT T.CardCode, T.TypeA * 32 + T.TypeB * 16
+ T.TypeC * 8 + T.TypeD * 4 + T.TypeE * 2 + T.TypeF * 1 GROUP_NUM
FROM
(
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) T
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
User | Count |
---|---|
89 | |
7 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.