May 19, 2011 at 4:32 am
Hi I have a table containing following type of information
YRF No DateCr. Dr.Type
========================================================
0Cash-MEM-429 2011-05-19 15000.00 0.00C
0CRCARD-MEM-4292011-05-19 8000.00 0.00C
0MEM-429 2011-05-19 0.00 33090.00 B
0REC-2258 2011-05-19 10090.00 0.00R
0REC-2259 2011-05-19 5.00 0.00R
but now I want to out put like as
Y RF No Date Cr.Dr. Type Balance
=======================================================
0 Cash-MEM-429 2011-05-19 15000.00 0.00 C -15000.00
0 CRCARD-MEM-429 2011-05-19 8000.00 0.00 C -23000.00
0 MEM-429 2011-05-19 0.00 33090.00 B 10090.00
0 REC-2258 2011-05-19 10090.00 0.00 R 0.00
0 REC-2259 2011-05-19 5.00 0.00 R -5.00
I have done this from front end by using
{OpeningBal}-{RTotal0}+{RTotal1}
but I want to do so in SQL Query... Please help me
How can i do so..
May 19, 2011 at 4:44 am
Since all those rows are from the same date, how do you decide which order they are in to calculate the running total? Please provide full table DDL (including constraints) and sample data in the form of INSERT statements.
Thanks
John
May 26, 2011 at 1:32 am
To Do so I have written a query as
select T1.Y,T1.[REF NO],T1.[REF DATE],T1.CREDIT,T1.DEBIT,T1.[TYPE],
(Sum(T2.DEBIT)-Sum(T2.CREDIT))as BALANCE from temp T1
inner join temp T2 on T2.Row<=T1.Row
Group by T1.Y,T1.[REF NO],T1.[REF DATE],T1.CREDIT,T1.DEBIT,T1.[TYPE]
--order by [REF DATE] ,[TYPE] desc
--order by T1.DEBIT desc
order by [REF DATE]
and result is as
0 Cash-MEM-429 2011-05-19 15000.000.00 C-15000.00
0 CRCARD-MEM-429 2011-05-19 8000.000.00 C-23000.00
0 MEM-429 2011-05-19 0.0033090.00 B10090.00
0 REC-2258 2011-05-19 10090.000.00 R0.00
0 REC-2259 2011-05-19 9.000.00 R-9.00
0 REC-2260 2011-05-23 0.500.00 R-9.50
0 MEM-434 2011-05-24 0.0033090.00 B13080.50
0 REC-2261 2011-05-24 20000.000.00 R-20009.50
But output is getting wrong in last two lines
33080.5
13080.5
in last column. Please tell me where is the problem
May 26, 2011 at 2:29 am
Table DDL and sample data as requested, please.
The query you have posted cannot return those results, since the ABS function always returns a positive number.
Also bear in mind that an inequality join will cause you performance problems when your tables start to grow. Search this site for triangular joins for more information on that.
John
May 26, 2011 at 2:41 am
Sorry sir,
That was previous query..
expect query is
select T1.Y,T1.[REF NO],T1.[REF DATE],T1.CREDIT,T1.DEBIT,T1.[TYPE],
(Sum(T2.DEBIT)-Sum(T2.CREDIT))as BALANCE from temp T1
inner join temp T2 on T2.Row<=T1.Row
Group by T1.Y,T1.[REF NO],T1.[REF DATE],T1.CREDIT,T1.DEBIT,T1.[TYPE]
--order by [REF DATE] ,[TYPE] desc
--order by T1.DEBIT desc
order by [REF DATE]
the complete query which performing calculation is
with abc as
(
select 0 as [Y],'Cash-'+BillNoWPrefix as [REF NO],Bill_Date as [REF DATE],isnull(Cash_AMount,0) as [CREDIT], 0 [DEBIT],'C' as [Type]
from dbo.Bill_Master B inner join dbo.Prop_Customer C on C.Customer_ID = B.Customer_ID
where B.Customer_ID = '48172' and B.CREDIT_AMOUNT > 0 and B.CASH_AMOUNT>0 and B.Bill_Prefix='MEM' and B.Bill_Date between '2011-05-19' and '2011-05-26'
Union all
select 0 as [Y],'CRCARD-'+BillNoWPrefix as [REF NO],Bill_Date as [REF DATE],isnull(CRCARD_AMount,0) as [CREDIT], 0 [DEBIT],'C' as [Type]
from dbo.Bill_Master B inner join dbo.Prop_Customer C on C.Customer_ID = B.Customer_ID
where B.Customer_ID = '48172' and B.CREDIT_AMOUNT > 0 and B.CRCARD_AMOUNT>0 and B.Bill_Prefix='MEM' and B.Bill_Date between '2011-05-19' and '2011-05-26'
union all
select 0 as [Y],'CHEQUE-'+BillNoWPrefix as [REF NO],Bill_Date as [REF DATE],isnull(CHEQUE_AMount,0) as [CREDIT], 0 [DEBIT],'C' as [Type]
from dbo.Bill_Master B inner join dbo.Prop_Customer C on C.Customer_ID = B.Customer_ID
where B.Customer_ID = '48172' and B.CREDIT_AMOUNT > 0 and B.CHEQUE_AMount>0 and B.Bill_Prefix='MEM' and B.Bill_Date between '2011-05-19' and '2011-05-26'
union all
select 0 as [Y],BillNoWPrefix as [REF NO],Bill_Date as [REF DATE],0 [CREDIT], isnull(Net_Amount,0)[DEBIT],'B' as [Type]
from dbo.Bill_Master B inner join dbo.Prop_Customer C on C.Customer_ID = B.Customer_ID
where B.Customer_ID = '48172' and B.CREDIT_AMOUNT > 0 and B.Bill_Prefix ='MEM'and B.Bill_Date between '2011-05-19' and '2011-05-26'
union all
select 0 as [Y] ,Convert(varchar(20),R.RecpNoWPrefix) as [REF NO],R.ReceiptDate as [REF DATE],isnull(R.ReceivedAmount,0) [CREDIT],0[DEBIT],'R' as [Type]
from Prop_ReceiptMaster R inner join dbo.Prop_Customer C on C.Customer_ID = R.Customer_Id
where C.Customer_ID ='48172' and R.ReceiptDate between '2011-05-19' and '2011-05-26' and R.ReceiptFor='MEMBER'
),
temp as
(
select 0 as [Row],0 as [Y],'Opening' as [REF No],0 as [REF DATE],case when Status='Cr' then Balance else 0 end as [Credit],Case when Status='Dr' then Balance else 0 end as [Debit],Status as [TYPE] From Fun_GetOpening('48172','2011-05-19')
Union all
select Row_Number() over (order by [REF DATE] asc)as ROW,Y,[REF NO],[REF DATE],CREDIT,DEBIT,[TYPE] from abc
)
select T1.Y,T1.[REF NO],T1.[REF DATE],T1.CREDIT,T1.DEBIT,T1.[TYPE],
(Sum(T2.DEBIT)-Sum(T2.CREDIT))as BALANCE from temp T1
inner join temp T2 on T2.Row<=T1.Row
Group by T1.Y,T1.[REF NO],T1.[REF DATE],T1.CREDIT,T1.DEBIT,T1.[TYPE]
--order by [REF DATE] ,[TYPE] desc
--order by T1.DEBIT desc
order by [REF DATE]
May 26, 2011 at 2:47 am
This is the last time I'm going to ask. Please provide full table DDL (including constraints) and sample data in the form of INSERT statements.
John
May 26, 2011 at 6:34 am
Create table temp (Y bit,Ref_No Varchar(50),Ref_Date datetime,Credit numeric(18,2),Debit numeric(18,2), type )
in case of Credit amount
insert into ('0','Cash-Mem-245','2011-05-11','2500.00','0.00','C')
in case of Debit amount
insert into ('0','Mem-245','2011-05-11','0.00',250'0.00','B')
May 26, 2011 at 8:31 am
amitsingh308 (5/26/2011)
Create table temp (Y bit,Ref_No Varchar(50),Ref_Date datetime,Credit numeric(18,2),Debit numeric(18,2), type )in case of Credit amount
insert into ('0','Cash-Mem-245','2011-05-11','2500.00','0.00','C')
in case of Debit amount
insert into ('0','Mem-245','2011-05-11','0.00',250'0.00','B')
You know the people here are volunteers. We get paid nothing for helping people. We do it because we enjoy helping others (and learning a little for ourselves along the way). This is a pathetic attempt at providing what has been asked repeatedly. The create table doesn't work, the inserts don't work. The is not enough sample data to let anybody test some code to help you. The fact that you are either too lazy or unwilling to help us help you does not give anybody much interest. IF you can provide working ddl and some sample data (more than 1 or 2 rows) there are people here willing to help. We just prefer to spend or freebie time working on the problem instead of setting it up.
/soapbox off
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 26, 2011 at 9:10 am
There is a link in Sean's signature line to this article. It's worth reading.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
As Sean said, the people who answer questions in these forums are unpaid volunteers. His request is for you to paste up both CREATE code and INSERTs for sample data that will allow him to cut and paste it into his SQL editor to recreate your example exactly, and start looking at the problem to come up with solutions. You should have already tested your create statements and insert statements to be sure that they run correctly and adequately describe the problem. Yes, this may take you another 30 minutes to set up. But otherwise the unpaid volunteers have to spend 30 minutes doing it, and their time is as important to them as yours is to you.
If you will take the time to code your example, you will find any number of people here quite willing to provide you with coded and tested solutions.
Best regards,
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply