July 20, 2012 at 12:09 am
hi friends,
ACCTUAL TABLE:
account transction amount
===============================
A-1234 Cr 6454
A-8464 Dr 4500
L-435353 Cr 6000
R-5499 dr 8000
from the date of 01 apr 2011 to 30 apr 2011,
here cr is credit, dr is debit
noe i need the output of "opening balance"is
ACCOUn'A%' means=dr-cr upto 31-march-2011 in data base
ACCOUn'L%' means=cr-dr upto 31-march-2011 in data base
and closing balance ve calculate 'A%'=opengbal+dr-cr
'L%'=opengbal+cr-dr
expected output
account transction openingbal closingbal
======================================================
A-1234 Cr dr-cr<01 apr2011 (amount) opengbal+dr-cr(amount)
A-8464 Dr dr-cr<01 apr2011 (amount) opengbal+dr-cr(amount)
L-435353 Cr cr-dr<01 apr2011 (amount) opengbal+dr-cr(amount)
R-5499 dr cr-dr<01 apr2011 (amount) opengbal+dr-cr(amount)
July 20, 2012 at 12:11 am
Cross post. Please direct your replies here : http://www.sqlservercentral.com/Forums/Topic1332694-392-1.aspx
July 20, 2012 at 12:16 am
i m workin in server 2000
July 20, 2012 at 12:23 am
raghuldrag (7/20/2012)
i m workin in server 2000
In that case, please post your query in appropriate forum
There is a separate forum for SQL Server 7, 2000
Also note that people helping you out here are volunteers. You should help them to help you
You have been pointed to the posting etiquettes time and again(and this remains the same for any version of SQL Server), but you seem to have no time to read the article
Cross posting is not going to help and people won't take the trouble of helping you if you don't take the trouble of helping them by providing what they want
I will ask you read the below article on how to post your questions one final time
http://www.sqlservercentral.com/articles/Best+Practices/61537/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 20, 2012 at 12:34 am
i'm wrote query for opening bal ol....
select account_no,locn_code,
sum(case when transc ='Cr' then amount else 0 end) "Credit",
sum(case when transc ='Dr' then amount else 0 end) "Debit" ,
sum(case when left(account_no,1) in ('A','E') and transc='Dr' and fs_tran_date <'01-apr-2011' then amount
when left(account_no,1) in ('A','E') and transc='Cr' and fs_tran_date <'01-apr-2011' then amount * -1
when left(account_no,1) in ('R','L') and transc='Dr' and fs_tran_date <'01-apr-2011' then amount
when left(account_no,1) in ('R','L') and transc='Cr' and fs_tran_date <'01-apr-2011' then amount * -1
else 0
end) "Opening Bal"
from
FMS..fs_postings
where fs_tran_date between '01-apr-2011' and '30-apr-2011'
group by account_no,locn_code
abve the qury output:
ACCOUNTNO LOCATN CREDIT DEBIT OPENG BAL
================================================================
A001-FA103-3053 MDEL .00000000700.00000000.00000000
A001-FA110-3032 APMC .00000000557630.00000000.00000000
A001-FA110-3032 CHN .00000000314243.00000000.00000000
A002-CA100-3157 CHN 550062897119.75000000.00000000
A002-CA101-3231 FCHN 3377042.00000000.0000000
CONDTION:
OPENGBAL=DR-CR BEFOR APPERNG 01 APR,
CLOSEBAL=OPENGBAL+DR-CR
NOW MYSELF OPENGBAL SHWNG "0" ONLY
HOW TO MAKE QUERY.....
July 20, 2012 at 12:47 am
Please continue all further posting here. By starting a new thread you are just going to fragment any responses you get and everyone is going to get frustrated.
Don't get frustrated, please use the other thread and provide us with the information we need to help you in a format we can readily use.
Thank you.
July 20, 2012 at 1:11 am
in my table ve data from 2008 to 2012..... now ve to calculate the "cr-dr" before appearing 01 apr 2011,
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply