none
Netezza Query (M+S+T) RRS feed

  • 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
    Friday, October 9, 2020 3:11 AM

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.

    Monday, October 12, 2020 6:21 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.t-c.v) as T,(b.s - b.u-c.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.

    Friday, October 9, 2020 7:39 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!!!

    Friday, October 9, 2020 10:19 AM
  • The Out put for A5 is M+S = 1; M=1 , T=1

    The output for A6 ia M+S =1; M=1, T=2

    Friday, October 9, 2020 10:23 AM
  • 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.

    Monday, October 12, 2020 6:21 AM
  • Thanksnalot Daniel, Great stuff !!  It works very well!!
    Monday, October 12, 2020 7:39 AM
  • Hi Fareed37,
    I am glad to help you.
    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.

    Monday, October 12, 2020 8:11 AM