1 Reply Latest reply: Feb 14, 2013 3:58 AM by AlbertoFaenza RSS

    query help

    VI
      Hi ,

      I need a query help. I have data in table called order_points like below. I need to display user information for each 3000 in total and once for every 500 over 3000.


      1 A 01/02/2013 2000
      1 A 01/03/2013 1000
      1 A 01/04/2013 750
      1 A 01/05/2013 500
      2 B 01/01/2013 3620

      Example: user A has total points 4150 , user would be listed 3 times like below. user B has total points 3620 so would be listed 2 times like below.

      output:

      1 A
      1 A
      1 A
      2 B
      2 B
      create table order_points(user_id NUMBER(10),user_name VARCHAR2(20),order_date DATE, points NUMBER(18))
      /
      INSERT INTO order_points VALUES (1,'A','02-JAN-2013',2000)
      /
      INSERT INTO order_points VALUES (1,'A','03-JAN-2013',1000)
      /
      INSERT INTO order_points VALUES (1,'A','04-JAN-2013',750)
      /
      INSERT INTO order_points VALUES (1,'A','05-JAN-2013',500)
      /
      INSERT INTO order_points VALUES (2,'B','01-JAN-2013',3620)
      /
        • 1. Re: query help
          AlbertoFaenza
          Here is a solution:
          WITH mydata AS 
          (  
             SELECT user_id, user_name
                  , CEIL ( (SUM (points) - 3000) / 500) n
               FROM order_points
              GROUP BY user_id, user_name
          )
          , max_times AS
          ( 
             SELECT LEVEL lvl
               FROM DUAL
            CONNECT BY LEVEL <= (SELECT MAX (n) FROM mydata)
          )
          SELECT a.user_id, a.user_name
            FROM mydata a, max_times b
           WHERE b.lvl <= a.n
           ORDER BY a.user_id, a.user_name;
          
             USER_ID USER_NAME           
          ---------- --------------------
                   1 A                   
                   1 A                   
                   1 A                   
                   2 B                   
                   2 B                 
          Regards.
          Al

          Edited by: Alberto Faenza on Feb 14, 2013 10:57 AM
          Modified with an additional WITH for readability