Home » SQL & PL/SQL » SQL & PL/SQL » opening n ending balance
opening n ending balance [message #252090] Tue, 17 July 2007 16:16 Go to next message
safwanmehmood
Messages: 14
Registered: August 2005
Junior Member
Hi,

i have to calculate opening and ending balance of products. i m executing this query which gives me following result.

1 SELECT ALL TO_CHAR(STKTRAN.SDATE,'YY') YY,
2 TO_CHAR(STKTRAN.SDATE,'MM') MM,
3 TO_CHAR(STKTRAN.SDATE,'MON,YY') MON,
4 PRODUCTS.PNAME,
5 SUM(STKTRAN.QTYIN) QTYIN,
6 SUM(STKTRAN.QTYOUT) OUT,
7 SUM(STKTRAN.QTYIN-STKTRAN.QTYOUT) BAL
8 FROM PRODUCTS, STKTRAN
9 WHERE PRODUCTS.PID = STKTRAN.PID
10 AND STKTRAN.SDATE BETWEEN '01-JAN-2005' AND '01-JAN-2007'
11 GROUP BY TO_CHAR(STKTRAN.SDATE,'YY') ,
12 TO_CHAR(STKTRAN.SDATE,'MM'),
13* TO_CHAR(STKTRAN.SDATE,'MON,YY'), PRODUCTS.PNAME
SQL> /

YY MM MON PNAME QTYIN OUT BAL
-- -- ------ ------- --------- --------- ---------
06 10 OCT,06 GX-150 18655 12300 6355
06 10 OCT,06 GX-220 20500 18245 2255
06 10 OCT,06 GX-250 14350 14555 -205
06 10 OCT,06 GX-275 5125 4920 205
06 10 OCT,06 GX-285 25625 25215 410
06 10 OCT,06 GX-290 6150 5740 410
06 10 OCT,06 GX-320 6150 6355 -205
06 10 OCT,06 GX-355 10250 10865 -615
06 10 OCT,06 GX-375 13940 11890 2050
06 11 NOV,06 GX-150 22550 20910 1640
06 11 NOV,06 GX-220 15375 12300 3075
06 11 NOV,06 GX-250 15375 12710 2665
06 11 NOV,06 GX-275 12300 8610 3690

QTYIN is qty purchased.
QTYOUT is qty sold.
BAL is difference of QTYIN and QTYOUT.
PNAME is product name.

my requirement is to get opening and ending balance of each product by date or by month.plz guide me how can i calculate it.


YY MM MON PNAME OPENINGBALANCE QTYIN OUT BAL
-- -- ------ ------- --------- --------- ---------

thanks

Safwan
Re: opening n ending balance [message #252147 is a reply to message #252090] Tue, 17 July 2007 23:46 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
select YY, MM,MON,Pname,
case 
  when lag(bal) over (partition by pname order by STKTRAN.SDATE) is null 
  then bal 
else lag(bal) over (partition by pname order by STKTRAN.SDATE)
end op_bal,
QTYIN,
qtyOUT
from (
 SELECT STKTRAN.SDATE,TO_CHAR(STKTRAN.SDATE,'YY') YY,
 TO_CHAR(STKTRAN.SDATE,'MM') MM,
 TO_CHAR(STKTRAN.SDATE,'MON,YY') MON,
 PRODUCTS.PNAME,
 SUM(STKTRAN.QTYIN) QTYIN,
 SUM(STKTRAN.QTYOUT) qtyOUT,
 SUM(STKTRAN.QTYIN-STKTRAN.QTYOUT) BAL
 FROM PRODUCTS, STKTRAN
 WHERE PRODUCTS.PID = STKTRAN.PID
 AND STKTRAN.SDATE BETWEEN to_date('01-JAN-2005','dd-Mon-yyyy') AND to_date('01-JAN-2007','dd-Mon-yyyy')
 GROUP BY TO_CHAR(STKTRAN.SDATE,'YY') ,
 TO_CHAR(STKTRAN.SDATE,'MM'),
 TO_CHAR(STKTRAN.SDATE,'MON,YY'), PRODUCTS.PNAME
)
/

[Updated on: Wed, 18 July 2007 00:53] by Moderator

Report message to a moderator

Re: opening n ending balance [message #252288 is a reply to message #252090] Wed, 18 July 2007 08:47 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
safwanmehmood wrote on Tue, 17 July 2007 17:16

10 AND STKTRAN.SDATE BETWEEN '01-JAN-2005' AND '01-JAN-2007'



How sad the people still insist on comparing DATE columns to character strings. They never learn.
Re: opening n ending balance [message #253418 is a reply to message #252147] Mon, 23 July 2007 16:15 Go to previous messageGo to next message
safwanmehmood
Messages: 14
Registered: August 2005
Junior Member
Hi,

Quote:
select YY, MM,MON,Pname,
case
when lag(bal) over (partition by pname order by STKTRAN.SDATE) is null
then bal
else lag(bal) over (partition by pname order by STKTRAN.SDATE)
end op_bal,
QTYIN,
qtyOUT



lag(bal) is giving error. sorry, i have not mentioned that i m using oracle8i.
i need result like that ..
Quote:

MON PNAME OB QI QO EB
----- ------ ---------- ---------- ---------- ----------
JAN,05 GX-150 0 1000 0 1000
JAN,05 GX-220 0 0

FEB,05 GX-150 1000 1000
FEB,05 GX-220 0 0



product opening balance should be qty of ending balance of previous month.

one month product ending balance should be opening balance of next month.

thanks
Re: opening n ending balance [message #253444 is a reply to message #253418] Tue, 24 July 2007 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
sorry, i have not mentioned that i m using oracle8i.

There is no excuse.
We already told you to format your post and give your version with 4 decimals.
You waste other's time.
This prevent me from helping you.

Regards
Michel
icon1.gif  Re: opening n ending balance [message #253479 is a reply to message #253444] Tue, 24 July 2007 01:46 Go to previous messageGo to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member

Hi,
In continuation to this thread when i try to execute the below query i got ORA-00904: "S"."SDATE": invalid identifier, i am running on Oracle 10g. But when i execute the inner query it is working.
Any help/idea !!!

Thanks in advance.
Regards
Sibghat


  
SELECT yy, mm, mon, pname,
       CASE
          WHEN LAG (bal) OVER (PARTITION BY pname ORDER BY s.sdate) IS NULL
             THEN bal
          ELSE LAG (bal) OVER (PARTITION BY pname ORDER BY s.sdate)
       END op_bal,
       qtyin, qtyout
  FROM (SELECT   TO_CHAR (s.sdate, 'YY') yy, TO_CHAR (s.sdate, 'MM') mm,
                 TO_CHAR (s.sdate, 'MON,YY') mon, p.pname, SUM (s.qtyin)
                                                                        qtyin,
                 SUM (s.qtyout) qtyout, SUM (s.qtyin - s.qtyout) bal
            FROM products p, stktran s
           WHERE p.pid = s.pid
             AND s.sdate BETWEEN TO_DATE ('01-JAN-2005', 'dd-Mon-yyyy')
                             AND TO_DATE ('01-JAN-2007', 'dd-Mon-yyyy')
        GROUP BY TO_CHAR (s.sdate, 'YY'),
                 TO_CHAR (s.sdate, 'MM'),
                 TO_CHAR (s.sdate, 'MON,YY'),
                 p.pname)

  
 
Re: opening n ending balance [message #253482 is a reply to message #253479] Tue, 24 July 2007 01:51 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
sibgat wrote on Tue, 24 July 2007 08:46
Hi,
In continuation to this thread when i try to execute the below query i got ORA-00904: "S"."SDATE": invalid identifier, i am running on Oracle 10g. But when i execute the inner query it is working.
Any help/idea !!!
You don't select sdate in your inner query. The outer query doesn't have access to it.

MHE
Re: opening n ending balance [message #253516 is a reply to message #253482] Tue, 24 July 2007 03:00 Go to previous messageGo to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member

Even if i select sdate in inner query, the error remains the same
Regards
Sibghat
Re: opening n ending balance [message #253521 is a reply to message #253516] Tue, 24 July 2007 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried with the error.
Copy and paste your screen. Do it in SQL*Plus.

Regards
Michel
Re: opening n ending balance [message #253540 is a reply to message #253521] Tue, 24 July 2007 03:40 Go to previous messageGo to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member


SQL>  SELECT yy, mm, mon, pname,
  2          CASE
  3             WHEN LAG (bal) OVER (PARTITION BY pname ORDER BY s.sdate) IS NULL
  4                THEN bal
  5             ELSE LAG (bal) OVER (PARTITION BY pname ORDER BY s.sdate)
  6          END op_bal,
  7          qtyin, qtyout
  8     FROM (SELECT  s.sdate, TO_CHAR (s.sdate, 'YY') yy, TO_CHAR (s.sdate, 'MM') mm,
  9                    TO_CHAR (s.sdate, 'MON,YY') mon, p.pname pname, SUM (s.qtyin) qtyin,
 10                    SUM (s.qtyout) qtyout, SUM (s.qtyin - s.qtyout) bal
 11               FROM products p, stktran s
 12              WHERE p.pid = s.pid
 13                AND s.sdate BETWEEN TO_DATE ('01-JAN-2005', 'dd-Mon-yyyy')
 14                                AND TO_DATE ('01-JAN-2007', 'dd-Mon-yyyy')
 15           GROUP BY TO_CHAR (s.sdate, 'YY'),
 16                    TO_CHAR (s.sdate, 'MM'),
 17                    TO_CHAR (s.sdate, 'MON,YY'),
 18                    p.pname,s.sdate);
           ELSE LAG (bal) OVER (PARTITION BY pname ORDER BY s.sdate)
                                                            *
ERROR at line 5:
ORA-00904: "S"."SDATE": invalid identifier


SQL> 

[Updated on: Tue, 24 July 2007 03:41]

Report message to a moderator

Re: opening n ending balance [message #253543 is a reply to message #253540] Tue, 24 July 2007 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what has been said.
What is the scope of "S": inner query.
"S" is not visible outside it.
SELECT clause is outside inner query.

Regards
Michel

[Updated on: Tue, 24 July 2007 03:43]

Report message to a moderator

Re: opening n ending balance [message #253545 is a reply to message #253543] Tue, 24 July 2007 03:46 Go to previous messageGo to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member

aaah..............

Yes Micheal..

Thanks a lot...
Re: opening n ending balance [message #253576 is a reply to message #252090] Tue, 24 July 2007 06:09 Go to previous messageGo to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member

Here is the alternate way of doing this..


SELECT x.sdate, x.pid, x.pname,x.qtyin,x.qtyout, x.qtyin - x.qtyout bal,
       ((x.qtyin + prevbal) - x.qtyout) outsbal
  FROM (SELECT s1.sdate, s1.pid,p.pname,
              ( SELECT SUM (s.qtyin) - SUM (s.qtyout)
                  FROM stktran s 
                 WHERE s.pid = s1.pid AND s.sdate < s1.sdate) prevbal,
               s1.qtyin, s1.qtyout
          FROM stktran s1 JOIN products p ON s1.pid = p.pid
         WHERE s1.pid = p.pid order by p.pid,s1.sdate) x



And the output for the above query is


SDATE            PID PNAME           QTYIN     QTYOUT        BAL    OUTSBAL
--------- ---------- ---------- ---------- ---------- ---------- ----------
10-JAN-81       1010 PROD-A          13333      10001       3332
10-JAN-82       1010 PROD-A          98234      45843      52391      55723
11-NOV-83       1010 PROD-A            500        400        100      55823
08-AUG-77       1011 PROD-B          11000       8832       2168
08-AUG-97       1011 PROD-B          14454       9032       5422       7590
23-SEP-02       1012 PROD-C           8000         23       7977
04-APR-06       1013 PROD-D          33000      12440      20560



Re: opening n ending balance [message #253774 is a reply to message #253576] Tue, 24 July 2007 16:06 Go to previous messageGo to next message
safwanmehmood
Messages: 14
Registered: August 2005
Junior Member
thanks sibgat,
i think so i will be able to change it according to my required result.

Quote:
There is no excuse.
You waste other's time.
This prevent me from helping you.

Regards
Michel



i am not execusing from you ... you waste urs n other's time .. if u do not wana guide then do not waste ur time to post such time wasting replies ...
Re: opening n ending balance [message #253835 is a reply to message #253774] Wed, 25 July 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i am not execusing from you ... you waste urs n other's time .. if u do not wana guide then do not waste ur time to post such time wasting replies ...

Please post in english.

Regards
Michel
Re: opening n ending balance [message #254116 is a reply to message #253835] Wed, 25 July 2007 15:45 Go to previous messageGo to next message
safwanmehmood
Messages: 14
Registered: August 2005
Junior Member
Quote:
Please post in english.

Regards
Michel



i think so u need english classes ...
it is in english. can u read it ?
otherwise join english learning forum n post their ur time wasting posts.
Re: opening n ending balance [message #254118 is a reply to message #254116] Wed, 25 July 2007 15:48 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Please show me an english dictionary where the following words are located:

u
ur
urs
n
Previous Topic: how to reove spaces and it becomes standard
Next Topic: how could this piece of code not working
Goto Forum:
  


Current Time: Mon Dec 05 19:09:42 CST 2016

Total time taken to generate the page: 0.05783 seconds