Home » SQL & PL/SQL » SQL & PL/SQL » problem is getting result .. I am a lazy SOB!
problem is getting result .. I am a lazy SOB! [message #253113] Sun, 22 July 2007 14:28 Go to next message
safwanmehmood
Messages: 14
Registered: August 2005
Junior Member
Hi,

i m executing this query to get following result.

OB is function through which i m getting Opening qty at time '01-JAN-2005' .
QTYIN is qty purchased.
QTYOUT is qty sold.
END_BAL is difference of (OPENING_BALANCE+ QTYIN) - QTYOUT.
PNAME is product name.


SELECT ALL
TO_CHAR(STKTRAN.SDATE,'MON,YY') MON,
PRODUCTS.PNAME,
OB(PID,'01-OCT-2005') OB_BAL,
SUM(STKTRAN.QTYIN) QTYIN,
SUM(STKTRAN.QTYOUT) OUT,
END_BAL(PID,31-OCT-2005) END_BAL

FROM PRODUCTS, STKTRAN

WHERE PRODUCTS.PID = STKTRAN.PID
AND STKTRAN.SDATE BETWEEN '01-OCT-2005' AND '31-OCT-2005'

GROUP BY
TO_CHAR(STKTRAN.SDATE,'MON,YY'), PRODUCTS.PNAME;

MON PNAME OB QTYIN OUT BAL
----- ------- ----- ------ --- -------
OCT,05 GX-150 1000 18655 12300 7355
OCT,05 GX-220 0 20500 18245 2255

opening balance is the qty_in_stock + qty_purchased - qty_sold before 01-oct-05.

ending balance is the qty_in_stock + qty_purchased - qty_sold before 01-nov-05.

the problem is i want to get result between two dates. suppose between 01-jan-05 and 31-dec-05 group by month n product.

"one month ending balance should be the opening balance of other. " ???
means it will look like ....

MON PNAME OB QTYIN OUT BAL
------ ------ ----- ------ ----- ---------
OCT,05 GX-150 1000 18655 12300 7355
OCT,05 GX-220 0 20500 18245 2255

NOV,05 GX-150 7355 1000 5355 3355
NOV,05 GX-220 2255 1000 1255 2255

please guide me ......thanks

[mod-edit] removed the word URGENT from subject as it has no meaning.

[Updated on: Mon, 23 July 2007 08:06] by Moderator

Report message to a moderator

Re: problem is getting result .. its urgent! [message #253115 is a reply to message #253113] Sun, 22 July 2007 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
problem is getting result .. its urgent!

There is only one thing urgent:
Read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Repost correctly then we can start thinking of your problem.

Regards
Michel
Re: problem is getting result .. its urgent! [message #253116 is a reply to message #253113] Sun, 22 July 2007 14:42 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
1) You really should read & FOLLOW the posting guidelines in the STICKY posts at the top of this forum.
>.. its urgent
If you do not like the timeliness or content of any response you are entitled to a full & complete refund of all amounts paid.

Please explain why it is URGENT for me to solve this problem for you?

Please post what you've tried & explain why it is deficient.
Re: problem is getting result .. its urgent! [message #253122 is a reply to message #253116] Sun, 22 July 2007 15:17 Go to previous messageGo to next message
safwanmehmood
Messages: 14
Registered: August 2005
Junior Member
Hi,

the problem is i need to calculate product stock.
opening stock , purchased , sold remaining. it could be daily , monthly , yearly. user is passing date range parameter , from date to end date.

DATE OPENING_STOCK PURCHASED SOLD ENDING_STOCK
---- ------------- --------- ---- ------------

i tried to make it more clear. tell me if u r still unable to understand it.

thanks
Re: problem is getting result .. its urgent! [message #253123 is a reply to message #253116] Sun, 22 July 2007 15:33 Go to previous messageGo to next message
safwanmehmood
Messages: 14
Registered: August 2005
Junior Member
Quote:
this is required ...
"one month ending balance should be the opening balance of other. " ??? which is shown in below result.

MON PNAME OB QTYIN OUT BAL
------ ------ ----- ------ ----- ---------
OCT,05 GX-150 1000 18655 12300 7355
OCT,05 GX-220 0 20500 18245 2255

NOV,05 GX-150 7355 1000 5355 3355
NOV,05 GX-220 2255 1000 1255 2255



but i m getting like this ...
MON PNAME OB QTYIN OUT BAL
------ ------ ----- ------ ----- ---------
OCT,05 GX-150 1000 18655 12300 7355
OCT,05 GX-220 0 20500 18245 2255

NOV,05 GX-150 1000 1000 5355 3355
NOV,05 GX-220 0 1000 1255 2255


bolded ares have problem.

i m getting opening balance from this function ...

CREATE OR REPLACE FUNCTION PURCH_QTY_D
(PCODE IN NUMBER, DATED IN DATE) RETURN number IS
A NUMBER := 0;
BEGIN
SELECT NVL(SUM(S.QTYIN-S.QTYOUT)),0)
INTO A
FROM STKTRAN S
WHERE S.SDATE <= DATED AND B.PCODE = PCODE;

A:= NVL(A,0);

RETURN A;
END;
/
Re: problem is getting result .. its urgent! [message #253144 is a reply to message #253123] Sun, 22 July 2007 23:26 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already posted your question in the topic "opening n ending balance".
You didn't answer to Bonker to tell us if his solution was good.
You didn't follow JoyDivision advice about "date strings".
I don't see any reason to help you if you don't care about what we say and don't make any feedback.

Regards
Michel
Previous Topic: how i can get the day from date
Next Topic: delete
Goto Forum:
  


Current Time: Fri Dec 02 16:25:18 CST 2016

Total time taken to generate the page: 0.34341 seconds