none
Mobile +SIM ( SQL QUERY) RRS feed

  • Question

  • I have 4 Accounts as mentioned in the above table

    Customer 1 --> 5 Mobiles & 3 SIMS O/p I need is Mobile count -- 2, Mobile + SIM -- 3, SIM -- 0

    Customer 2 ---> 2 Mobiles & 3 SIMS O/p I need is Mobile Count --0, Mobile +SIM -- 2, SIM --1

    Basically the one to one mapping between mobile and SIM should go under Mobile + SIM bucket others should go in to respective buckets like SIM OR Mobile.

    in customer 1 case he is having 5 Mobiles and 3 sims , 3 mobiles will comprise 3 SIMS hence Mobile + SIM should get as 3 . The left are 2 Mobiles , it should fall under Mobile bucket as 2.

    How can I achieve this in SQL ( ANY CASE modulo will work?)


    • Edited by Fareed37 Wednesday, October 7, 2020 3:41 AM
    Wednesday, October 7, 2020 3:36 AM

Answers

  • Hi Fareed37,
    Have you tried to use inner join instead of cross apply that I provided above?
    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.

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:46 AM
    Thursday, October 8, 2020 1:15 AM
  • Hi Fareed37,
    For the previous problem, I have provided a solution. Please mark as answer, and it is recommended that you open a new thread to ask your new question.
    Thank you for your understanding.
    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.

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:45 AM
    Friday, October 9, 2020 1:31 AM
  • Check this achievement:

    declare @Table as table
    (
        Customer varchar(10),
        Mobiles int,
        SIMS int
    )
    
    insert into @Table values
    ( 'Account 1', 5, 3),
    ( 'Account 2', 2, 3),
    ( 'Account 3', 2, NULL),
    ( 'Account 4', NULL, 4)
    
    select * from @Table
    
    ---
    
    select Customer, m - u as Mobiles, u as [Mobile+SIM], s - u as SIMS
    from @Table
    cross apply (values (isnull(Mobiles, 0), isnull(SIMS, 0))) t1(m, s)
    cross apply (values (case when m > s then s else m end)) t2(u)
    
    

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:50 AM
    Wednesday, October 7, 2020 5:38 AM
  • Which error comes when you try the entire example without any adjustment?

    ‘Cross apply’ should be used together with preceding ‘select’; t1 and t2 are the names (aliases) of the imaginary tables that are built by this form of ‘cross apply’; m, s and u are columns.

    Show the error messages. This is a query for SQL Server 2019.

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:50 AM
    Wednesday, October 7, 2020 8:49 AM
  • Hi Fareed37,
    Netezza does not support CROSS APPLY or OUTER APPLY (Correlated Subqueries). 
    You can try to use inner join instead of cross apply.

    select a.Customer, m - u as Mobiles, u as [Mobile+SIM], s - u as SIMS
    from input a
    inner join 
    (
      select Customer, ISNULL(Mobiles,0) as m,ISNULL(SIMS,0) as s,
       Case When ISNULL(Mobiles,0) < ISNULL(SIMS,0) 
                   Then ISNULL(Mobiles,0) Else ISNULL(SIMS,0) End as u
      from input
    )b
    on a.Customer=b.Customer

    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.

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:46 AM
    Wednesday, October 7, 2020 9:22 AM
  • Your Query is perfect and it works fine in SQL Server 2012 as well.. I saw the output.

    I am applying this logic in Netezza warehouse ( which is a DB) for us. CROSS apply function does not exists.


    Any logic with CASE statement please? some thing with PLSQL. Sorry for this. I would be really great help.

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:46 AM
    Wednesday, October 7, 2020 11:54 AM

All replies

  • Check this achievement:

    declare @Table as table
    (
        Customer varchar(10),
        Mobiles int,
        SIMS int
    )
    
    insert into @Table values
    ( 'Account 1', 5, 3),
    ( 'Account 2', 2, 3),
    ( 'Account 3', 2, NULL),
    ( 'Account 4', NULL, 4)
    
    select * from @Table
    
    ---
    
    select Customer, m - u as Mobiles, u as [Mobile+SIM], s - u as SIMS
    from @Table
    cross apply (values (isnull(Mobiles, 0), isnull(SIMS, 0))) t1(m, s)
    cross apply (values (case when m > s then s else m end)) t2(u)
    
    

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:50 AM
    Wednesday, October 7, 2020 5:38 AM
  • Thank you for the reply..

    What is t1 and t2 in the below code

    I am using this code in Netezza database. not sure if CROSS apply works. I tried but  some error comes.

    cross apply (values (isnull(Mobiles, 0), isnull(SIMS, 0))) t1(m, s)
    cross apply (values (case when m > s then s else m end)) t2(u)

    Wednesday, October 7, 2020 8:29 AM
  • Which error comes when you try the entire example without any adjustment?

    ‘Cross apply’ should be used together with preceding ‘select’; t1 and t2 are the names (aliases) of the imaginary tables that are built by this form of ‘cross apply’; m, s and u are columns.

    Show the error messages. This is a query for SQL Server 2019.

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:50 AM
    Wednesday, October 7, 2020 8:49 AM
  • Hi Fareed37,
    Netezza does not support CROSS APPLY or OUTER APPLY (Correlated Subqueries). 
    You can try to use inner join instead of cross apply.

    select a.Customer, m - u as Mobiles, u as [Mobile+SIM], s - u as SIMS
    from input a
    inner join 
    (
      select Customer, ISNULL(Mobiles,0) as m,ISNULL(SIMS,0) as s,
       Case When ISNULL(Mobiles,0) < ISNULL(SIMS,0) 
                   Then ISNULL(Mobiles,0) Else ISNULL(SIMS,0) End as u
      from input
    )b
    on a.Customer=b.Customer

    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.

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:46 AM
    Wednesday, October 7, 2020 9:22 AM
  • Your Query is perfect and it works fine in SQL Server 2012 as well.. I saw the output.

    I am applying this logic in Netezza warehouse ( which is a DB) for us. CROSS apply function does not exists.


    Any logic with CASE statement please? some thing with PLSQL. Sorry for this. I would be really great help.

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:46 AM
    Wednesday, October 7, 2020 11:54 AM
  • Hi Fareed37,
    Have you tried to use inner join instead of cross apply that I provided above?
    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.

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:46 AM
    Thursday, October 8, 2020 1:15 AM
  • Hi Daniel

    Yes I did. and it was working fine. Today, again there is a slight change in the requirement given ( after the call)

    Sorry i was waiting for this call to reply you...

    Here below, its been told to consider Order no as it has multiple items ordered in the same no.

    please share some thoughts to achive the below output ( highlighted in Yellow). Thanks alot!!

    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

    Thursday, October 8, 2020 6:45 PM
  • Hi Fareed37,
    For the previous problem, I have provided a solution. Please mark as answer, and it is recommended that you open a new thread to ask your new question.
    Thank you for your understanding.
    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.

    • Marked as answer by Fareed37 Friday, October 9, 2020 2:45 AM
    Friday, October 9, 2020 1:31 AM
  • Ok Sure Daniel, I will open a new thread and will mark as answer.
    Friday, October 9, 2020 2:45 AM