Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: problem with code

    hie guys
    i tried this, to aggregate two TABLE into a reporting table but this is the result it returns is not exactly what i want, there is something wrong with the code

    the code i used:

    Code:
    select 
    	max(trunc(sysdate))            AS date_loaded,
    	max(to_char(sysdate,'WW'))     AS week_no,
    	max(trunc(a.reload_date))      AS reload_date, 
    	a.loyalty_id 		           AS loyalty_id,
    	sum(trunc(a.reloads_value_j2)) AS total_reloads_value_j2,
      	sum(case when trunc(b.reload_date) = a.reload_date then 
                 b.reloads_value_j1 
      	else 
                 0 
      	end) AS total_reloads_value_j1,
      	sum(abs(a.reloads_value_j2 - case when trunc(b.reload_date) = trunc(a.reload_date) then 
    					b.reloads_value_j1 
    				 else 
    					0 
      				 end
            )) as gap,
      	sum(abs(a.reloads_value_j2 - case when trunc(b.reload_date) = trunc(a.reload_date) then 
    					b.reloads_value_j1 
    				 else 
    					0 
      				 end
            ))/sum(a.reloads_value_j2 + case when trunc(b.reload_date) = trunc(a.reload_date) then 
                 				b.reloads_value_j1 
      				else 
                 				0 
      				end)*100 AS gap_percentage
    from trap_test1 a, 
         trap_test2 b
    where a.loyalty_id = b.loyalty_id
    GROUP BY a.loyalty_id;

    the actual result /reporting table data shoud be :

    date_loaded,week_no,reload_date,loyalty_id,total_r eloads_value_j2,total_reloads_value_j1,gap,gap_per centage

    12/7/2005,49,12/1/2005,4,45,45,0,0
    12/7/2005,49,12/2/2005,4,15,0,15,100
    12/7/2005,49,12/3/2005,3,0,30,30,100

    pls check for me...(the data table and the reporting table data is in the attachment)
    Attached Files Attached Files
    Last edited by shatishr; 12-07-05 at 00:05.

  2. #2
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Hi,
    I believe you need to group at the first three columns also apart from loyality_id which is the 4th column in ur query.
    I hv downloaed the requirement and working on it. In case if i get solution, i will post it for you. Thanks.

    Regards,
    Kamesh Rastogi
    - KR

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I tested it on MS SQL server, it works fine. Syntax for oracle should be:
    Code:
    select 
    	date_loaded,
    	week_no,
    	reload_date,
    	loyalty_id,
    	total_reloads_value_j2,
    	total_reloads_value_j1,
    	abs(total_reloads_value_j2 - total_reloads_value_j1) as gap,
    	100*abs(total_reloads_value_j2 - total_reloads_value_j1)/(total_reloads_value_j2 + total_reloads_value_j1) as gap_perc
    from
    (	
    select 
    	sysdate as date_loaded,
    	to_char(sysdate, 'WW') 	AS week_no,	
    	reload_date,
    	loyalty_id,
    	(select nvl(sum(reloads_value_j2), 0) from t1 where t1.loyalty_id = x.loyalty_id and t1.reload_date = x.reload_date) as total_reloads_value_j2,
    	(select nvl(sum(reloads_value_j1), 0) from t2 where t2.loyalty_id = x.loyalty_id and t2.reload_date = x.reload_date) as total_reloads_value_j1
    from
    (select distinct loyalty_id, reload_date from t1
     union
     select distinct loyalty_id, reload_date from t2) x
    ) y
    BTW I use subquery twice for more comforteable calucalation of gap and gap_perc

  4. #4
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    madafaka,
    thanks a lot, i have got it working but yet to test it out with the 9 million row tables ;p..
    really appreciate your help and could you help me understand this part

    Code:
    (select nvl(sum(reloads_value_j2), 0) from trap_test1 where trap_test1.loyalty_id = x.loyalty_id and trap_test1.reload_date = x.reload_date) as total_reloads_value_j2,
    	(select nvl(sum(reloads_value_j1), 0) from trap_test2 where trap_test2.loyalty_id = x.loyalty_id and trap_test2.reload_date = x.reload_date) as total_reloads_value_j1
    from
    (select distinct loyalty_id, reload_date from trap_test1
     union
     select distinct loyalty_id, reload_date from trap_test2) x
    ) y
    maybe could explain what would the variables x and y store ??


    ramesh,
    thanks for trying but i would love to know how will you solve it...

    Thanks !!!
    Last edited by shatishr; 12-07-05 at 21:44.

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I'm glad it helped you. Here is explanation:
    according to your result expectation: first of all you need core table, which contains only loyalty_id and reload_date - distinct values. you have to derive that table. its name is x and of course it's based on t1 and t2 (your source tables)
    Code:
    select * from 
    (select distinct loyalty_id, reload_date from t1
     union
     select distinct loyalty_id, reload_date from t2) x
    
    loyalty_id	reload_date
    ---------------------------
    3		2005-12-03
    4		2005-12-01
    4		2005-12-02
    now you need to select total_reloads_value_j1 (from t2) and total_reloads_value_j2 (from t1) for specific loyalty_id and reload_date. to do so you use subqueries.
    in case there's no reloads_value_j1 in table t1 (for specific loyalty_id and date) return 0 - this solve nvl() function
    finaly you give subqueries proper names - use aliases ... "as reloads_value_j1"
    Code:
    ...
    	(select nvl(sum(reloads_value_j2), 0) from t1 where t1.loyalty_id = x.loyalty_id and t1.reload_date = x.reload_date) as total_reloads_value_j2,
    	(select nvl(sum(reloads_value_j1), 0) from t2 where t2.loyalty_id = x.loyalty_id and t2.reload_date = x.reload_date) as total_reloads_value_j1
    from
    (select distinct loyalty_id, reload_date from t1
     union
     select distinct loyalty_id, reload_date from t2) x
    you can use this result as another derived table called y:
    Code:
    select * from
    (	
     select 
    	sysdate as date_loaded,
    	to_char(sysdate, 'WW') 	AS week_no,	
    	reload_date,
    	loyalty_id,
    	(select nvl(sum(reloads_value_j2), 0) from t1 where t1.loyalty_id = x.loyalty_id and t1.reload_date = x.reload_date) as total_reloads_value_j2,
    	(select nvl(sum(reloads_value_j1), 0) from t2 where t2.loyalty_id = x.loyalty_id and t2.reload_date = x.reload_date) as total_reloads_value_j1
     from
     (select distinct loyalty_id, reload_date from t1
      union
      select distinct loyalty_id, reload_date from t2) x
    ) y
    
    date_loaded	week_no	reload_date	loyalty_id	total_reloads_value_j2	total_reloads_value_j1
    ------------------------------------------------------------------------------------------------------
    2005-12-08 	50	2005-12-03 	3		0			30
    2005-12-08 	50	2005-12-01 	4		45			45
    2005-12-08 	50	2005-12-02 	4		15			0
    now you pretend you have all you need in one table called y, so you can comfortably calculate gap and gap_percentage (see my final statement)

    BTW if you'll test this on 9 mil rows you'll probably have performance issue. in this case I'd suggest: create and use temporary tables - x, y instead of derived tables. don't forget create indexes.

Posting Permissions

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