Results 1 to 6 of 6

Thread: complicated SQL

  1. #1
    Join Date
    Apr 2015
    Posts
    3

    Unanswered: complicated SQL

    Hello,

    I really need to know how to write an sql statement for this!
    I have a table that consists following data:

    CustomerID Year Amount
    A 2014 343
    A 2003 434
    B 2014 0
    B 2013 231
    C 2014 543
    C 2013 0

    I need an where clause that retreives customers that have zero (0) latest year!
    In my example I wan't customer B not customer C becuase customer C has 543 in amount a year later.

    How can I do that?

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select CustomerID, Year, Amount
    from (
    select t.*, first_value(amount) over(partition by customerid order by year desc rows between unbounded preceding and unbounded following) fv
    from mytable t
    )
    where fv=0
    Regards,
    Mark.

  3. #3
    Join Date
    Apr 2015
    Posts
    3

    Another complicated SQL

    Hello Mark!

    You seems to be my best friend!
    It works fine for me.

    I have a following question regarding to this subject.
    I also have a table that consist almost similiar data, se below.

    CustomerID Year Type Amount
    A 1999 1 22343
    A 2000 1 3233
    A 2001 1 3211
    B 2013 1 3212
    B 2014 1 0
    C 2012 2 2321
    C 2013 2 3233

    In this case I'm only intrested in CustomerID B, Year 2013, Type 1 and with amount 0.
    I have tried with your example but in doesn't work for me yet.

    /Mr Big Spender
    Last edited by hakaxh; 04-24-15 at 08:48.

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Big,

    I'm not sure if I got your problem correctly. When you say "in doesn't work for me yet", it would be better that you say what exactly doesn't work and why.
    The following query returns 2 rows:
    Code:
    with mytable (CustomerID, Year, Type, Amount) as (values
      ('A', 1999, 1, 22343)
    , ('A', 2000, 1, 3233)
    , ('A', 2001, 1, 3211)
    , ('B', 2013, 1, 3212)
    , ('B', 2014, 1, 0)
    , ('C', 2012, 2, 2321)
    , ('C', 2013, 2, 3233)
    )
    select CustomerID, Year, Amount
    from (
    select t.*, first_value(amount) over(partition by customerid order by year desc rows between unbounded preceding and unbounded following) fv
    from mytable t
    )
    where fv=0
    
     CUSTOMERID YEAR TYPE AMOUNT
     ---------- ---- ---- ------
     B          2014    1      0
     B          2013    1   3212
    Do you want to return only 1 row with AMOUNT=0 for the customerid=B?
    If so, why do you want YEAR=2013 for this record? Original row has YEAR=2014 for this customerid='B' and amount=0.
    So, I'm not able to understand your requirements yet...
    Regards,
    Mark.

  5. #5
    Join Date
    Apr 2015
    Posts
    3
    Hi,

    Exceuse me: I only want to return 1 row and I'm only intrested in type=1!

    Expected result:

    Customer B should be year 2014 with amount = 0.
    Customer D should be year 2002 with amount = 0.
    Customer E should be year 2001 with amount = 0.

    Other customers for example can also have:

    Customer ID Year Type Amount
    D 2001 1 2323
    D 2002 1 0
    E 2001 1 0
    E 2002 1 1132


    /Mr BigSpender

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Code:
    with mytable (CustomerID, Year, Type, Amount) as (values
      ('A', 1999, 1, 22343)
    , ('A', 2000, 1, 3233)
    , ('A', 2001, 1, 3211)
    , ('B', 2013, 1, 3212)
    , ('B', 2014, 1, 0)
    , ('C', 2012, 2, 2321)
    , ('C', 2013, 2, 3233)
    , ('D', 2001, 1, 2323)
    , ('D', 2002, 1, 0)
    , ('E', 2001, 1, 0)
    , ('E', 2002, 1, 1132)
    )
    select CustomerID, Year, Type, Amount
    from (
    select t.*, rownumber() over(partition by customerid order by year desc) rn_
    from mytable t
    where type=1
    )
    where amount=0 and rn_=1
    
     CUSTOMERID YEAR TYPE AMOUNT
     ---------- ---- ---- ------
     B          2014    1      0
     D          2002    1      0
    Have no idea why you want to return a row for E. There is a row with higher YEAR (2002) and AMOUNT<>0 for that customerid.
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •