case-sql server(logical thnk)
-
accountno transaction amount date
==========================================
A-123 Cr 4500 13-jan-08
E-23455 Cr 8000 13-hjuly-09 ===>actaul table
R-747 Dr 8900 02-apr-12
L-64646 Dr 9300 06-apr-12
cr-credit,dr-debit
now i need when the account is started only
'A', means the amount has been come=amount(dr-cr)}
'L' means the amount has been come=amount(cr-dr)} ===>opening balance
(note: suppose i m giving particular date
where date between'01-apr-12' and '30-apr-12'means the opening balance ll be calculated the starting date to till'31-marc-2012'onwards)
how make the auery suggest me guys.......
-
raghuldrag (7/20/2012)
accountno transaction amount date==========================================
A-123 Cr 4500 13-jan-08
E-23455 Cr 8000 13-hjuly-09 ===>actaul table
R-747 Dr 8900 02-apr-12
L-64646 Dr 9300 06-apr-12
cr-credit,dr-debit
now i need when the account is started only
'A', means the amount has been come=amount(dr-cr)}
'L' means the amount has been come=amount(cr-dr)} ===>opening balance
(note: suppose i m giving particular date
where date between'01-apr-12' and '30-apr-12'means the opening balance ll be calculated the starting date to till'31-marc-2012'onwards)
how make the auery suggest me guys.......
Please post DDL and expected results. If you could write in unabbreviated English you would stand a better chance of getting help.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
How many topics do you need to start for the same question
http://www.sqlservercentral.com/Forums/Topic1332708-392-1.aspx
http://www.sqlservercentral.com/Forums/Topic1332029-392-1.aspx
http://www.sqlservercentral.com/Forums/Topic1332694-392-1.aspx
Keep everything in the same topic,
Do not start new topics for the same question, you get fragmented replies
-
its very urgent any one can help me .....
-
No one will do anything until you provide us with consumable DDL, sample data, expected results and what you have used so far.
Again as with the other topics follow the best practise links on how to provide this information.
Also remember what I said on private message, we are all unpaid volunteers and will help when we have time. Saying something is urgent is all well and good but you wont get an urgent responce back.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
-
raghuldrag (7/20/2012)
its very urgent any one can help me .....Sorry. If its really that urgent, you would not have wasted time by cross posting again and again.
Did you read the article on forum etiquettes suggested to you?
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
my query:
select account_no,fs_locn_code,
sum(case when transc ='Cr' then amountelse 0 end) "Credit",
sum(case when transc ='Dr' then amountelse 0 end) "Debit" ,
SUM(CASE WHEN fs_tran_date <'31-mar-2011'and left(account_no,1) in ('A','E') and transc='Dr' then amount
WHEN left(account_no,1) in ('A','E') and transc='Cr' then amount* -1
WHEN fs_tran_date <'31-mar-2011'and left(account_no,1) in ('R','L') and transc='Cr' then amount
WHEN left(account_no,1) in ('R','L') and transc='Dr' then amount* -1
ELSE 0 END) As "opening Bal"
from
FMS..fs_postings
where fs_tran_date between '01-apr-2011' and '30-apr-2011'
group by account_no,fs_locn_code
=================================================================================
expecting output:(here data are stored from the date of 2008 onwards,em selecting apr mnth datas oly,but opning bal column should show the value of 2008 to 31 march onwards)
accountno location credit debit openingbal
--------------------------------------------
a1223 chenn 5677 000 -950000(entire sum of 2008 to march 31-2011)
a88939 banglore 000 74747 888000(entire sum of 2008 to march 31-2011)
e8888 hydr 7000 0000 000000(no caluctn for "E" & "L" Appreng acc)
r9595 banglore 9000 0000 00000(no caluctn for "E" & "L" Appreng acc
-
Table definition and sample data which fits your expected outcome?
-
outlook of the output structer sample,:-)
-
This data
accountno transaction amount date
==========================================
A-123 Cr 4500 13-jan-08
E-23455 Cr 8000 13-hjuly-09 ===>actaul table
R-747 Dr 8900 02-apr-12
L-64646 Dr 9300 06-apr-12
Does not equal this data
a1223 chenn 5677 000 -950000(entire sum of 2008 to march 31-2011)
a88939 banglore 000 74747 888000(entire sum of 2008 to march 31-2011)
e8888 hydr 7000 0000 000000(no caluctn for "E" & "L" Appreng acc)
r9595 banglore 9000 0000 00000(no caluctn for "E" & "L" Appreng acc
Either provide the base data for the expected results provided, or change the expected results to match the provided base data.
Also what data types are the columns.
Have you read the best practise links we have asked so kindly for you to read? If so you should know how to do this.
-
expecting output is sample.... dnt consider.... to see opening bal oly.....letter "A" in accuntno=debit-credit ,
"L" in accuntno=credit-debit
-
But we need something to work on and without the base data and what the base data should look like after the logic is applied we cannot do the work.
So again help us to help you
We need
CREATE TABLE definition of all tables involved
EG.
CREATE TABLE MyTable
(
AccountNo INT,
Col1 INT,
Col2 NVARCHAR(MAX),
...
...
)
INSERT INTO script for all sample data.
EG.
INSERT INTO MyTable (AccountNo, Col1, Col2,...,...) VALUES (1,100,'I am a string',...,...)
INSERT INTO MyTable (AccountNo, Col1, Col2,...,...) VALUES (1,852,'I am a string',...,...)
Expected outcome BASED on the SAMPLE DATA.
-
accountno loct credit debit opning bal
===================================================================== =============
A001-FA103-3053 MDEL .00000000700. .00000000
A001-FA110-3032 APMC .00000000557630. .00000000
A001-FA110-3032 CHN .00000000314243. .00000000
A002-CA100-3157 CHN 550062897119. .00000000 ===>> ouput of the table
A002-CA101-3231 FCHN 3377042..00000000.00000000
A002-CA101-3250 CAHD 1093292.490019. .00000000
A002-CA101-3250 CASS 439271. 346206. .00000000
my query:
========
select account_no,loct,
sum(case when trans ='Cr' thenamount else 0 end) "Credit",
sum(case when trans ='Dr' thenamount else 0 end) "Debit",
sum(case when fs_tran_date<'01-apr-2011'and left(fs_account_no,1) in ('A') and trans ='Dr' thenamount
when fs_tran_date<'01-apr-2011'and left(fs_account_no,1) in ('A') and trans ='Cr' thenamount * -1
when fs_tran_date<'01-apr-2011'and left(fs_account_no,1) in ('L') and trans ='Dr' thenamount * -1
when fs_tran_date<'01-apr-2011'and left(fs_account_no,1) in ('L') and trans ='Cr' thenamount
else 0
end) "openingBal"
from
FMS..fs_postings
where fs_tran_date between '01-apr-2011' and '30-apr-2011'
group by account_no,loct
now i need "opeing balance" as entire credit - debit till starting date to 31st march 2011 onwards
-
Anthony pretty much laid it on a plate for you and still - no DDL. I'm out.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I'm out too.
Asked for that information a number of times on the different posts, even took the time on one post to actually create the consumable DDL and data so the OP knew for future how to post, but keep going round in circles.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply