Answered by:
Netezza Query (M+S+T)
Question

Hi All,
I am using netezza as a database to run my queries, i have below input ( highlighted in Green) basted on the following output needs to be derived.( highlighted in Yellow.
Ideally, customers are ordering multi items in a same order, based on that we need bucketing.
For example 1 customer is having following orders (A1,A2,A3,A4,A5,A6,A7,A8,A9) ordering different items.
Description
Ordered 2 M's with Order no A1, hence the count of M's is 2
Ordered 1 T with Order no A2, hence the the count of T is 1
Ordered 1 T + 1 m+ 1 S with Order no A3, hence the count of T is 1; M +S is 1;; priority to be given for M to get S added. ( M+S)
Ordered 1 T + 1 M+ 2 S with Order no A4, hence the count of T+S is 1; M +S is 1
Ordered 2 S with Order no A5, hence the count of S is 2
Ordered 1 M with Order no A6, hence the count of M is 1
Ordered 1 T + 1 S with Order no A7, hence the count of t+s is 1
Ordered 1 T + 1 M with Order no A8, hence the count of M is 1 and T is 1
Ordered 2 T's with Order no A9, hence the count of T is 2
Answers

Hi Fareed37,
I checked my sql and lacked of discussion of a situation in Case when statement.
So please refer to the modified code:inner join ( select [Order NO], ISNULL(M,0) as m,ISNULL(S,0) as s,ISNULL(T,0) as t, Case when ISNULL(T,0)=0 or ISNULL(S,0)=0 then 0 when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)=0 and ISNULL(S,0) ISNULL(T,0)>=0 then T when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)=0 and ISNULL(S,0) ISNULL(T,0)<0 then S when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)!=0 and ISNULL(S,0) ISNULL(M,0)>=ISNULL(T,0)then T when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)!=0 and ISNULL(S,0) ISNULL(M,0)<ISNULL(T,0) and ISNULL(S,0) ISNULL(M,0)>=0 then ISNULL(S,0) ISNULL(M,0) when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)!=0 and ISNULL(S,0) ISNULL(M,0)<0 then 0 End as v from input1 )c
Best Regards,
Daniel Zhang
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Edited by Daniel_ZhangMSFTMicrosoft contingent staff Monday, October 12, 2020 6:22 AM
 Marked as answer by Fareed37 Monday, October 12, 2020 7:38 AM
All replies

Hi Fareed37,
I made a test with sql statement below and you can refer to it.select a.[Order NO], (b.m  b.u) as M, b.u as [M+S], c.v as [T+S], (c.tc.v) as T,(b.s  b.uc.v) as S from input a inner join ( select [Order NO], ISNULL(M,0) as m,ISNULL(S,0) as s, Case when ISNULL(M,0)=0 or ISNULL(S,0)=0 then 0 When ISNULL(M,0)!=0 and ISNULL(S,0)!=0 and M<=S then M when ISNULL(M,0)!=0 and ISNULL(S,0)!=0 and M>S then S End as u from input )b on a.[Order NO]=b.[Order NO] inner join ( select [Order NO], ISNULL(M,0) as m,ISNULL(S,0) as s,ISNULL(T,0) as t, Case when ISNULL(T,0)=0 or ISNULL(S,0)=0 then 0 when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)=0 and ISNULL(S,0) ISNULL(T,0)>=0 then T when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)=0 and ISNULL(S,0) ISNULL(T,0)<0 then S when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)!=0 and ISNULL(S,0) ISNULL(M,0)>=ISNULL(T,0)then T when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)!=0 and ISNULL(S,0) ISNULL(M,0)<ISNULL(T,0)then ISNULL(S,0) ISNULL(M,0) End as v from input )c on a.[Order NO]=c.[Order NO]
Best Regards,
Daniel Zhang
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Edited by Daniel_ZhangMSFTMicrosoft contingent staff Friday, October 9, 2020 7:41 AM

Hi Daniel
Thank you for this lovely piece of code!!!
I have tested all the scenarios, noticed one scenario is failing...
I tested for Order A5 and A6 and it is comming few negative values. ( Sorry that scenario did not provide you while posting this question initially)
Many many thanks!!!


Hi Fareed37,
I checked my sql and lacked of discussion of a situation in Case when statement.
So please refer to the modified code:inner join ( select [Order NO], ISNULL(M,0) as m,ISNULL(S,0) as s,ISNULL(T,0) as t, Case when ISNULL(T,0)=0 or ISNULL(S,0)=0 then 0 when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)=0 and ISNULL(S,0) ISNULL(T,0)>=0 then T when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)=0 and ISNULL(S,0) ISNULL(T,0)<0 then S when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)!=0 and ISNULL(S,0) ISNULL(M,0)>=ISNULL(T,0)then T when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)!=0 and ISNULL(S,0) ISNULL(M,0)<ISNULL(T,0) and ISNULL(S,0) ISNULL(M,0)>=0 then ISNULL(S,0) ISNULL(M,0) when ISNULL(T,0)!=0 and ISNULL(S,0)!=0 and ISNULL(M,0)!=0 and ISNULL(S,0) ISNULL(M,0)<0 then 0 End as v from input1 )c
Best Regards,
Daniel Zhang
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Edited by Daniel_ZhangMSFTMicrosoft contingent staff Monday, October 12, 2020 6:22 AM
 Marked as answer by Fareed37 Monday, October 12, 2020 7:38 AM


Hi Fareed37,
I am glad to help you.
Best Regards,
Daniel ZhangMSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.