Home » SQL & PL/SQL » SQL & PL/SQL » query to find bonus qty and ytd quantity form a given table (merged)
query to find bonus qty and ytd quantity form a given table (merged) [message #318695] Wed, 07 May 2008 10:52 Go to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Study the following topic, I think it will help you:
http://www.orafaq.com/forum/m/318019/102589/#msg_318019

Regards
Michel
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 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
yeah i went thru but still cant get the desired result
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried.

Regards
Michel
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
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 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
I cant figure out the query , please help out anybody out there
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
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 Go to previous messageGo to next message
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

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
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 Go to previous messageGo to next message
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
        
    
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
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 Go to previous messageGo to next message
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

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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

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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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

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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post the final solution so every one can take profit of it.

Regards
Michel
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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

Re: using nvl function in case statement [message #319523 is a reply to message #318695] Mon, 12 May 2008 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your questions.

Regards
Michel
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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


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 Go to previous message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
thanks a lot Michel , flyboy , else 0 statement worked out .

its Much appreciated
Previous Topic: need to remove sql query
Next Topic: Oracle Syntax error
Goto Forum:
  


Current Time: Sat Dec 10 01:23:22 CST 2016

Total time taken to generate the page: 0.11607 seconds