| How to compare two rows for values [message #72757] |
Wed, 15 October 2003 01:45  |
Max
Messages: 18
Registered: May 2002
|
Junior Member |
|
|
Hi ,
Can anyone help me in modifying a query.
the existing tables are
ca_product and ca_total_market_data and field are as follows
ca_product
PRODUCT_NAME,
PARENT_ID,
LEVEL_ID,
UPDATE_DATE,
EFFECTIVE_DATE,
EXPIRATION_DATE,
DELETION_STATUS
ca_total_market_data
TOTAL_MARKET_DATA_ID,
AREA_ID,
PRODUCT_ID,
YEAR,
SALE_QUANTITY,
UNIT_QUANTITY,
ACTIVE_STATUS_CODE,
UPDATE_DATE
the query is as follows,
"
select p2.PRODUCT_ID,p2.PRODUCT_NAME,tmd.YEAR,sum(tmd.SALE_QUANTITY)
SALE_QUANTITY,sum(tmd.UNIT_QUANTITY) UNIT_QUANTITY,
sum(DECODE(tmd.UNIT_QUANTITY,0,0,ROUND(tmd.SALE_QUANTITY*1000000/tmd.UNIT_QUANTITY)))
AVG_VALUE from ca_total_market_data tmd, ca_product p1, ca_product p2
where p1.parent_id=p2.product_id and tmd.PRODUCT_ID=p1.PRODUCT_ID and tmd.AREA_ID=176
and tmd.year>=1980 and tmd.year<1985 group by p2.PRODUCT_ID,p2.PRODUCT_NAME,tmd.YEAR "
and the result is as shown below
product_id product_name year sale_quantity unit_quantity avg_value
5 absorp 1980 8987 100 637268
5 absorp 1981 2343 201 23454
5 absorp 1982 3454 165 3455
5 absorp 1983 4554 300 23453
5 absorp 1984 2344 307 34545
6 airside 1980 56465 500 34535
6 airside 1981 45445 750 45435
6..........................................................so on
if u see the result , u can observe the unit_quantity is displayed for all 5 years, hte query which i need shld be such that , it shld follow the following condition
It shld only display hte sales and unit quantity of the years whose difference in sales is more that 100%
for ex compare 80 and 81 , so now sales for 81 is considered
next 81 and 82 is compared but but sales has fallen less than previous year so 82 sales is rejected; if 82 sales was 300 then also we dont consider 82 as
it si not 100 % but 99
now we will compare 82 and 83 and 300 is considered as the diff between
165 adn 300 is more that 100%
similarly 83 and 84 is comparedand sales is not considerer
NOw out of 5 years we shld consider only
81 and 83 sales,
CAN U PROVIDE ME WITH A QUERY FOR THIS PROBLEM,
Hope u provide me with solution as soon as possible
in the result finally i shld get only 83 and 84 sales and not hte other 3 years
thanks in advance
max
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
