OraFAQ Forum: SQL & PL/SQL => query to find bonus qty and ytd quantity form a given table (merged)

Members

Help

Help

Register

Login

Home

Home » SQL & PL/SQL » SQL & PL/SQL » query to find bonus qty and ytd quantity form a given table (merged)

Show: Today's Messages :: Show Polls :: Message Navigator

E-mail to friend

Switch to threaded view of this topic

Create a new topic

Submit Reply

query to find bonus qty and ytd quantity form a given table (merged) [message #318695]

Wed, 07 May 2008 10:52

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

I am facing a problem with a query

I have a table trans with the following
columns
item_code varchar2(20)
slt_code varchar2(10)
invoice_date date
sa_amount number(25,10)
qty_sold number (25,10)
fy_year number(4)
fyp_seq number(2)

where item_code is the name of item
slt_code is the type of sales , it can be C or B (c being cash and b being bonus )
invoice_date is the date
sa_amount is the cost of the the items sold
qty_sold is the quantity sold of the item
fy_year indicates the year
fyp_seq indicates the period

example of data
PHBI0 C 24-MAR-08 20 12 2008 3
PHBI0 B 28-MAR-08 0 10 2008 3
PHBI0 C 24-FEB-08 10 10 2008 2

problem is to display the
item_code , cost of the item(this should contain only belonging to slt_code C), qty_sold (again belonging to slt_code c), bonus quantity(belonging to slt_code =B) , ytd_qty( this should total all the qty of the particular item upto the current period for the slt_code =C)

so if i run a query for the item_code PHBIO for the month of march
i should get the result as
PHBI0,20,12,10,22
I have all this info from just one table , is there a way out

i

select itm_code, sa_amount ,qty_sold,(select sum(qty_sold from trans where slt_code='B')bqty

from trans 
where 
slt_code='C'
and itm_code ='PHBI0'and fy_year=2008 and fyp_seq=3 ;

but couldnt get the bonus qty and ytd quantity , please do help out

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #318696 is a reply to message #318695 ]

Wed, 07 May 2008 11:00

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

Study the following topic, I think it will help you:
http://www.orafaq.com/forum/m/318019/102589/#msg_318019

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #318706 is a reply to message #318696 ]

Wed, 07 May 2008 12:36

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

yeah i went thru but still cant get the desired result

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #318708 is a reply to message #318706 ]

Wed, 07 May 2008 12:48

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

Post what you tried.

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #318777 is a reply to message #318708 ]

Wed, 07 May 2008 23:32

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

I tried the following code

select
itm_code,(select sum(qty_sold) from sh_sales_history_details where fy_year=2008 and fyp_seq=3 and slt_code='S')qty_sold ,
(select sum(qty_sold) from sh_sales_history_details where fy_year=2008 and fyp_seq=3 and slt_code='B') bonus_qty
from sh_sales_history_details
itm_code ='PHBI0'and fy_year=2008 and fyp_seq=3;

[/CODE]
but still couldn't get it

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #318799 is a reply to message #318777 ]

Thu, 08 May 2008 00:53

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

1/ reread how to use code tags
2/ Use SQL Formatter (for instance) to format your query, don't put it in a single line
3/ I don't see anything in your query that is closed to the link I posted.

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319308 is a reply to message #318695 ]

Fri, 09 May 2008 13:38

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

I cant figure out the query , please help out anybody out there

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319309 is a reply to message #319308 ]

Fri, 09 May 2008 13:43

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

Did you try whay is in the link?
Of course you have to use SUM instead of COUNT but the rest is the same.

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319389 is a reply to message #318695 ]

Sat, 10 May 2008 08:27

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

Michel , i treid the link , thanks for that , i got to get the bonus quantity as well as

SELECT  Itm_Code,
         SUM(CASE 
               WHEN slt_Code = 'B' THEN qty_Sold
             END) Bonus_qty,
         SUM(CASE 
               WHEN slt_Code = 'C' THEN qty_Sold
             END) Invoice_qty,
         SUM(CASE 
               WHEN slt_Code = 'C' THEN l_Sales_Amount
             END) Sales_Amount
FROM      trans
WHERE 

         fy_Year = 2008
         AND fyp_seq = 3
GROUP BY Itm_Code

I was able to get the bonus quantity and , invoiced qty , but i can figure out how to calculate the total quantity sold upto the current period say april , for the sales type code C

[Updated on: Sat, 10 May 2008 09:17] by Moderator

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319391 is a reply to message #319389 ]

Sat, 10 May 2008 09:20

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

CASE condition could be as complex as you want using AND, OR and so on.
You just have to express your condition "upto the current period say april , for the sales type code C" (which I don't understand) in terms of value in your columns.

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319395 is a reply to message #319391 ]

Sat, 10 May 2008 09:51

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

I tried using 'and' combinations , in the case statement ,(as below )
but the ytd_qty is just gives the same , amount as the sales amount

slt_code means sales type , that is 'C' it is cash which has a cost , 'B' is bonus which does not have a cost

SELECT   tRans.Itm_Code,
        
        
         SUM(CASE 
               WHEN slt_Code1 = 'B' THEN qty_Sold
             END) Bonus_qty,
         SUM(CASE 
               WHEN slt_Code1 IN ('C',
                                  'T') THEN qty_Sold
             END) Invoice_qty,
         SUM(CASE 
               WHEN slt_Code1 = 'C' THEN l_Sales_Amount
             END) Sales_Amount,
         SUM(CASE 
               WHEN slt_Code1 = 'C'
                    AND fyp_seq = 1 THEN l_Sales_Amount
               WHEN slt_Code1 = 'C'
                    AND fyp_seq = 2 THEN l_Sales_Amount
               WHEN slt_Code1 = 'C'
                    AND fyp_seq = 3 THEN l_Sales_Amount
             END) ytd_Sales
FROM     tRans
WHERE    War_seq = 1
         AND fy_Year = To_char(SYSDATE,'YYYY')
         AND fyp_seq = To_char((Add_months(SYSDATE,- 2)),'MM')
GROUP BY tRans.Itm_Code

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319397 is a reply to message #319395 ]

Sat, 10 May 2008 10:03

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

If "fyp_seq = To_char((Add_months(SYSDATE,- 2)),'MM')" then I think it is always 3 and so never 1 or 2 and so it is the same than the previous query.
If you want 1, 2 or 3 the condition should be <= and not =.

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319401 is a reply to message #319397 ]

Sat, 10 May 2008 10:21

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

no did that because i would like to do it on a monthly basis (for sysdate ) ,( what i want is to calculate the sum of cost of items the sold for the sales type C upto the given period

example 
item_code   slt_code  qty_sold l_Sales_Amount  fy_year  fy_seq
PH BM 1080	C	200	 1000            2008	   3
PH BM 1080	C	100	 2000	         2008	   2
PH BM 1080	C	150	 3000	         2008	   1

so when i run the above query my desired result
would be

item_code   bonus_qty  Invoice_qty   Sales_Amount    ytd_Sales
PH BM 1080              200           1000             6000

as from the above example
my ytd_sales upto the given period is 6000.
i hope u get the picture on what i am trying to achieve

[Updated on: Sat, 10 May 2008 10:27]

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319402 is a reply to message #319401 ]

Sat, 10 May 2008 10:31

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

I got the picture you want to achieve but I think you didn't got it.
The condition is indeed <= 3 but you didn't modify the correct CASE.
You want the values for = 3 for the 3 first case and <= 3 for the last one, doesn't it?

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319403 is a reply to message #318695 ]

Sat, 10 May 2008 10:46

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

I tried putting on the last case statement fyp_seq<=3 like the following code


SELECT   tRans.Itm_Code,
        
        
         SUM(CASE 
               WHEN slt_Code1 = 'B' THEN qty_Sold
             END) Bonus_qty,
         SUM(CASE 
               WHEN slt_Code1='c'  THEN qty_Sold
             END) Invoice_qty,
         SUM(CASE 
               WHEN slt_Code1 = 'C' THEN l_Sales_Amount
             END) Sales_Amount,
         SUM(CASE 
               WHEN slt_Code1 = 'C'
               AND fyp_seq <= 3 THEN l_Sales_Amount
               END) ytd_Sales
FROM     tRans
WHERE    War_seq = 1
         AND fy_Year = To_char(SYSDATE,'YYYY')
         AND fyp_seq = To_char((Add_months(SYSDATE,- 2)),'MM')
GROUP BY tRans.Itm_Code

but i got the answer as

item_code   bonus_qty  Invoice_qty   Sales_Amount    ytd_Sales
PH BM 1080              200           1000             1000

is there something wrong in the coding of my final case statement

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319404 is a reply to message #319403 ]

Sat, 10 May 2008 10:49

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

Quote:

The condition (in where clause) is indeed <= 3 but you didn't modify the correct CASE.
You want the values for = 3 for the 3 first case and <= 3 for the last one, doesn't it?

Regards
Michel

[Updated on: Sat, 10 May 2008 10:51]

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319462 is a reply to message #319404 ]

Sun, 11 May 2008 11:46

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

hwy Michel ,

i have got the desired output thanks for your valuable tios

much appreciated ......... Wink

Wink

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319465 is a reply to message #319462 ]

Sun, 11 May 2008 12:42

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

Please post the final solution so every one can take profit of it.

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319498 is a reply to message #318695 ]

Sun, 11 May 2008 23:07

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

this what i did


SELECT   tRans.Itm_Code,
         SUM(CASE 
               WHEN slt_Code1 = 'B'
                    AND fyp_seq = To_char((Add_months(SYSDATE,- 1)),'MM') THEN qty_Sold
             END) Bonus_qty,
         SUM(CASE 
               WHEN slt_Code1 IN ('C')
                    AND fyp_seq = To_char((Add_months(SYSDATE,- 1)),'MM') THEN qty_Sold
             END) Invoice_qty,
         SUM(CASE 
               WHEN slt_Code1 = 'C'
                    AND fyp_seq = To_char((Add_months(SYSDATE,- 1)),'MM') THEN l_Sales_Amount
             END) Sales_Amount,
         SUM(CASE 
               WHEN slt_Code1 = 'C' THEN l_Sales_Amount
             END) ytd_Sales
FROM     sh_Sales_History_Details tRans
WHERE    War_seq = 1
         AND fy_Year = To_char(SYSDATE,'YYYY')
         AND fyp_seq <= To_char((Add_months(SYSDATE,- 1)),'MM')
GROUP BY tRans.Itm_Code

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319518 is a reply to message #319498 ]

Mon, 12 May 2008 01:26

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

hi i am facing about using nvl in case
in the code that i have put i used nvl function on the qty_sold

SELECT   Itm_Code,
         SUM(CASE 
               WHEN slt_Code1 in ( 'B','P')
                    AND fyp_seq =4 THEN nvl(qty_Sold,0)
             END) Bonus_qty,
         SUM(CASE 
               WHEN slt_Code1 = 'C' THEN l_Sales_Amount
             END) ytd_Sales
FROM     tRans
WHERE    War_seq = 1
         AND fy_Year = To_char(SYSDATE,'YYYY')
         AND fyp_seq <= 4
GROUP BY Itm_Code

but it still shows null in the output, instead of 0
, i cant figure out why its not showing 0

Report message to a moderator

Send a private message to this user

Re: using nvl function in case statement [message #319523 is a reply to message #318695 ]

Mon, 12 May 2008 01:53

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

Because your rows does not satisfy the WHEN clause.
If you want a value in any case you have to add an ELSE clause in your CASE.

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319524 is a reply to message #319518 ]

Mon, 12 May 2008 01:54

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

Don't multipost your questions.

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319549 is a reply to message #319524 ]

Mon, 12 May 2008 02:43

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

I am sorry abt the multiple post ,

i want to use nvl function on the qty_sold , because not all quantities will have bonuses ,

SELECT   Itm_Code,
         SUM(CASE 
               WHEN slt_Code1 in ( 'B','P')
                    AND fyp_seq =4 THEN qty_Sold
else nvl(qty_sold,0)
             END) Bonus_qty,
         SUM(CASE 
               WHEN slt_Code1 = 'C' THEN l_Sales_Amount
             END) ytd_Sales
FROM     tRans
WHERE    War_seq = 1
         AND fy_Year = To_char(SYSDATE,'YYYY')
         AND fyp_seq <= 4
GROUP BY Itm_Code

but it gave me , wrong set of data, in the sense i got those that dont have bonus , values
is there anything wrong if i use nvl on the sum

like the below

SELECT   Itm_Code,
         nvl(SUM(CASE 
               WHEN slt_Code1 in ( 'B','P')
                    AND fyp_seq =4 THEN qty_Sold
             END),0) Bonus_qty,
         SUM(CASE 
               WHEN slt_Code1 = 'C' THEN l_Sales_Amount
             END) ytd_Sales
FROM     tRans
WHERE    War_seq = 1
         AND fy_Year = To_char(SYSDATE,'YYYY')
         AND fyp_seq <= 4
GROUP BY Itm_Code

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table (merged) [message #319551 is a reply to message #318695 ]

Mon, 12 May 2008 03:01

flyboy
Messages: 519
Registered: November 2006

Senior Member

> else nvl(qty_sold,0)
Think about it. You do not get the NULL value because of "qty_sold" is NULL, but because the row does not satisfy the condition.
The CASE returns NULL, so you get NULL as SUM( NULL, NULL, ... ) = NULL.
You want to get 0 = SUM ( 0, 0, ... ).

What about
> ELSE 0

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table [message #319570 is a reply to message #319549 ]

Mon, 12 May 2008 04:45

Michel Cadot
Messages: 17697
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

Michel Cadot wrote on Mon, 12 May 2008 08:53

Because your rows does not satisfy the WHEN clause.
If you want a value in any case you have to add an ELSE clause in your CASE.

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: query to find bonus qty and ytd quantity form a given table (merged) [message #319691 is a reply to message #318695 ]

Mon, 12 May 2008 12:03

melvinRav
Messages: 43
Registered: April 2008
Location: India

Member

thanks a lot Michel , flyboy , else 0 statement worked out .

its Much appreciated

Report message to a moderator

Send a private message to this user

Switch to threaded view of this topic

Create a new topic

Submit Reply

Previous Topic:	need to remove sql query
Next Topic:	Oracle Syntax error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 25 17:39:01 CDT 2008

Total time taken to generate the page: 0.04758 seconds

.:: Forum Home :: Site Home :: Wiki Home :: Contact :: Privacy ::.