Business Objects - product date salesp1 20/Jan/1999 100p1 20/Feb/1999 200p1 20/apl/1999 100p1 20/DEC/1999 50p1 20/Jan/2

GeekInterview.com > Interview Questions > Data Warehousing > Business Objects

Go To First | Previous Question | Next Question

Business Objects | Question 20 of 239

product date sales
p1 20/Jan/1999 100
p1 20/Feb/1999 200
p1 20/apl/1999 100
p1 20/DEC/1999 50
p1 20/Jan/2000 100
p1 20/mar/2000 500

now i want query like
product ,sum of actual year ,sum of business year
p1 450 750

here actual year means 1 Jan 1999 to 31 st Jan 1999
business year means 1 st APR 1999 to 31 st mar 200

pls give me answer as early as possible..

Total Answers and Comments: 4

Last Update: October 10, 2007 Asked by: reddeppa

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

April 03, 2006 02:51:41

Sid

RE: product date salesp1 20/Jan/1999 100p1...

guess you can try this

(select product,sales S_A_Y from info where date >= 1 st Jan 1999 & date =<
31 st Jan 1999 groupby product) sal1 ,
(select product,sales S_B_Y from info where date >= 1 st Apr 1999 & date =<
31 st Mar 2000 groupby product) sal2
where sal1.product=sal2.product ;

Is this answer useful? Yes | No

April 04, 2006 12:41:41

reddeppa

hai

hai.....iam not understand what u says..can give me deatil....what is sal1.product=sal2.product..actually waht sal1 and sal2 are column alises,,,?right?

then why u put these are in conditions??/

plz give me clearly.

Is this answer useful? Yes | No

April 20, 2006 04:06:43

pat.mclean

Member Since: April 2006 Contribution: 52

RE: product date salesp1 20/Jan/1999 100p1...

Hi thr!

for reusability try breaking the dates into days & months & year. use them in the given statement(instead of hard-coding dates).

Can you please specify what exactly u want as "sum of actual year"? is it total number of years ?

Cheers,

Pat

Is this answer useful? Yes | No

October 10, 2007 01:52:27

Yathish Uppala

RE: product date salesp1 20/Jan/1999 100p1...

I am providing query(oracle) below.
Plz go through it.

SELECT SAL1.PRODUCT,SAL1.ACTUALSAL,SAL2.BUSINESSSAL FROM (SELECT PRODUCT,SUM(SALES) AS ACTUALSAL FROM PRODUCTS
                                                          WHERE PDATE BETWEEN '1-JAN-1999' AND '31-DEC-1999'
                                                          GROUP BY PRODUCT) SAL1,
                                                         (SELECT PRODUCT,SUM(SALES) AS BUSINESSSAL FROM PRODUCTS
                                                          WHERE PDATE BETWEEN '1-APR-1999' AND '31-MAR-2000'
                                                          GROUP BY PRODUCT) SAL2
WHERE SAL1.PRODUCT = SAL2.PRODUCT

Is this answer useful? Yes | No

Go To Top