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  |
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 #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
|
|
|
|
|
|
|
|
|
|
| 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 |
|
|
|
|
|
| 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
|
|
|
|
|
|
| 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 |
|
|
|
|
|
| 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
|
|
|
|
|
|
|
|
|
|
| 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
|
|
|
|
| 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
|
|
|
|
|
|
|
|
| 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
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 17:41:17 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
