Query for finding Sum of credit and debit

how to calculate debit credit and balance in sql server
how to calculate balance from debit and credit in sql
how to calculate balance from debit and credit in oracle
how to calculate debit credit and balance in mysql
balance sheet query in sql server
how to calculate debit credit and balance in php
ledger balance+sql query
debit credit running total in sql server

I need a SQL query to retrieve SUM in DATE ranges.

this is my sudo code:

 SELECT da.Debit,da.Credit,
    (SELECT SUM(daa.Debit) FROM Acc.DocumentAccount daa
    INNER JOIN Acc.Document dd ON dd.Id = daa.DocumentId
     WHERE dd.Date BETWEEN '2018-03-21' AND '2018-06-21' AND daa.Id=da.id) SumDebit
     FROM Acc.DocumentAccount da 
    INNER JOIN Acc.Document d ON d.Id = da.DocumentId
    WHERE d.Date BETWEEN '2018-06-21' AND '2019-03-21'

consider following sample data:

CREATE TABLE Document(
    Id INT,
    [Date] date
);


INSERT INTO Document VALUES (1,'2018-01-01');
INSERT INTO Document VALUES (2,'2018-01-02');
INSERT INTO Document VALUES (3,'2018-07-01');
INSERT INTO Document VALUES (4,'2018-07-02');
CREATE TABLE DocumentAccount(
    DocumentId INT,
    Debit INT,
    Credit INT
    );

INSERT INTO DocumentAccount VALUES (1,100,200);
INSERT INTO DocumentAccount VALUES (2,150,300);
INSERT INTO DocumentAccount VALUES (3,150,300);
INSERT INTO DocumentAccount VALUES (4,160,310);
INSERT INTO DocumentAccount VALUES (4,170,320);

CREATE TABLE DocumentAccountDetail
(
    DocumentAccountId INT,
    DetailId INT 
)

I need this hard coded data:

select * from 
(values (250, 500,0,0,480,0,0,0,930,0)) x(RemainDebit, RemainCredit,Q1Debit,Q2Debit,Q3Debit,Q4Debit,Q1Credit,Q2Credit,Q3Credit,Q4Credit)

RemainDebit is sum of debit between '2018-03-21' AND '2018-06-21'

RemainCredit is sum of credit between '2018-03-21' AND '2018-06-21'

Q1Debit is sum of debit between '2018-01-01' AND '2018-03-30' in this case because my filter range is BETWEEN '2018-06-21' AND '2019-03-21' Q1Debit is 0

Q2Debit is sum of debit between '2018-04-01' AND '2018-06-30' in this case because my filter range is BETWEEN '2018-06-21' AND '2019-03-21' Q1Debit is 0

Q3Debit is sum of debit between '2018-06-01' AND '2018-09-30' in this case because my filter range is BETWEEN '2018-06-21' AND '2019-03-21' and we have 3 document in 2018-07 then Q1Debit is 150+160+170=480

etc...

From your question you don't need a subquery on select clause, you can use JOIN in two tables and set date range on where clause

Select 
   Sum(debit) as Totledebit,
   Sum(credit) as Totlecredit
From DocumentAccount da 
INNER JOIN Document d on da.DocumentId = d.id
WHERE d.[Date] BETWEEN '2018-03-21' AND '2019-03-21'

SQLFiddle

EDIT

I saw you edit your question, thought there is some logic is strange. But I believe, you can try to use CASW WHEN expression and Sum function to make your expect.

Select 
   Sum(CASE WHEN d.[Date]  between '2018-01-01' AND '2018-06-21'  THEN debit ELSE 0 END) as Totledebit,
   Sum(CASE WHEN d.[Date]  BETWEEN '2018-01-01' AND '2018-06-21'  THEN credit ELSE 0 END) as Totlecredit,
   SUM(CASE WHEN d.[Date]  between '2018-01-01' AND '2018-03-30'  AND  d.[Date]  BETWEEN '2018-06-21' AND '2019-03-21' THEN debit ELSE 0 END) as Q1Debit,
   SUM(CASE WHEN d.[Date]  between '2018-04-01' AND '2018-06-30'  AND  d.[Date]  BETWEEN '2018-06-21' AND '2019-03-21'  THEN debit ELSE 0 END) as Q2Debit,
   SUM(CASE WHEN d.[Date]  between '2018-07-01' AND '2018-09-30'  AND  d.[Date]  BETWEEN '2018-06-21' AND '2019-03-21'  THEN debit ELSE 0 END) as Q3Debit,
   SUM(CASE WHEN d.[Date]  between '2018-10-01' AND '2018-12-31'  AND  d.[Date]  BETWEEN '2018-06-21' AND '2019-03-21'  THEN debit ELSE 0 END) as Q4Debit,
   SUM(CASE WHEN d.[Date]  between '2018-01-01' AND '2018-03-30'  AND  d.[Date]  BETWEEN '2018-06-21' AND '2019-03-21'  THEN Credit ELSE 0 END) as Q1Credit,
   SUM(CASE WHEN d.[Date]  between '2018-04-01' AND '2018-06-30'  AND  d.[Date]  BETWEEN '2018-06-21' AND '2019-03-21'  THEN Credit ELSE 0 END) as Q2Credit,
   SUM(CASE WHEN d.[Date]  between '2018-07-01' AND '2018-09-30'  AND  d.[Date]  BETWEEN '2018-06-21' AND '2019-03-21'  THEN Credit ELSE 0 END) as Q3Credit,
   SUM(CASE WHEN d.[Date]  between '2018-10-01' AND '2018-12-31'  AND  d.[Date]  BETWEEN '2018-06-21' AND '2019-03-21'  THEN Credit ELSE 0 END) as Q4Credit
From DocumentAccount da 
INNER JOIN Document d on da.DocumentId = d.id

sqlfiddle:https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=de775aaf8c5e03b25c346dca3bd80e21

CASE WHEN

[Solved] Credit debit balance calculation on SQL, Only different is, first the query need to find the different between the debit and credit, then run the running total calculation. See below as an  ABAP Query - Sum amount field with debit and credit Posted on I'm working on a Infoset query to sum up amount (dmbtr) from ekbe table. However, the dmbtr only

I would go with the following

SELECT da.Debit,da.Credit,
(SELECT ISNULL(SUM(daa.Debit),0) FROM Acc.DocumentAccount daa
INNER JOIN Acc.Document dd ON dd.Id = daa.DocumentId
 WHERE dd.Date BETWEEN '2018-03-21' AND '2018-06-21' AND da.AccountId=daa.AccountId) SumDebit
 FROM Acc.DocumentAccount da 
INNER JOIN Acc.Document d ON d.Id = da.DocumentId
WHERE d.Date BETWEEN '2018-06-21' AND '2019-03-21'

Ask TOM "ORACLE SQL Question sum credits and debits", I need total credit and debit for those 3 paricular dates. REQUIRED Please help me out on this to get the query. You're looking for a "group by then pivot". Sir, i have query but no response plz suggest us how to implements those or suggest new SQl query. String sql ="SELECT Debit, Credit, sDate SUM(Debit-Credit) OVER (ORDER BY sDate) Balance FROM Customer " ;

What is the best way to find out the sum of a field from a table , Always use the full query (#2 or similar depending how many ids you want at the moment), SELECT SUM(debit) - SUM(credit) FROM debit_credit WHERE  Query for finding balance of credit and debit in SQL How to calculate debit, credit, balance like bank statement in RDLC report Crystal Report formula to Calculate Balance from Credit and Debit

This might point you in the right direction...

Select 
   Sum(debit) as Dr,
   Sum (credit) as cr,
   Eomonth(datecolumn) as monthend
From table
    Group by eomonth (datecolumn)

If you only want one row outputted with Dr and cr amounts remove line 4 in the code and filter the date.

Is it possible to do a running balance? - Databases, Credit Debit Balance start -- -- 100.00 row1 10.00 110.00 row2 20.00 90.00 row3 to reflect changes brought by query filters that determine which rows show up. -Calculating-Running-Totals-Subtotals-and-Grand-Total-Without-a-Cursor.htm. Display the total debit counts, total credit counts, sum of total debits, sum of total credits from an employee's transaction table (a single table) containing the following columns. Transaction_number Transaction_amount Transaction_date Transaction_type --> tells if the amount is a credit or a debit.

You can achieve this by making SQL Functions, although the other answer is also very much considerable.

Function that will give result for Credit sum:

CREATE FUNCTION CreditSum (@Dt1 Date ,@Dt2 Date)
AS
BEGIN 

DECLARE @Sum decimal(10,2)

SELECT @sum = SUM(Credit) FROM Acc.DocumentAccount da 
INNER JOIN Acc.Document d ON d.Id = da.DocumentId WHERE DT BETWEEN @Dt1 And @Dt2

RETURNS @Sum
END

Function that will give result for debit sum:

CREATE FUNCTION DebitSum (@Dt1 Date ,@Dt2 Date)
AS
BEGIN 

DECLARE @Sum decimal(10,2)

SELECT @sum = SUM(Debit) FROM Acc.DocumentAccount da 
INNER JOIN Acc.Document d ON d.Id = da.DocumentId WHERE DT BETWEEN @Dt1 And @Dt2

RETURNS @Sum
END

Now You can get result anytime by just

SELECT DBO.CreditSum('2018-03-21','2018-06-21'),DBO.DebitSum('2018-03-21','2018-06-21'),DBO.CreditSum('2018-06-21',CAST(GETDATE() AS DATE)),DBO.DebitSum('2018-06-21',CAST(GETDATE() AS DATE))

Hope it will help.

How to calculate debit-credit-balance-sdate for sql query, Sir, i have query but no response plz suggest us how to implements those String sql ="SELECT Debit, Credit, sDate SUM(Debit-Credit) OVER  Function that will give result for Credit sum: CREATE FUNCTION CreditSum (@Dt1 Date ,@Dt2 Date) AS BEGIN DECLARE @Sum decimal(10,2) SELECT @sum = SUM(Credit) FROM Acc.DocumentAccount da INNER JOIN Acc.Document d ON d.Id = da.DocumentId WHERE DT BETWEEN @Dt1 And @Dt2 RETURNS @Sum END Function that will give result for debit sum:

Need Debit and Credit Values in Single Query in Teradata, One bank project maintains data both credit and debit values in single column as + and - . Now I want each and every employee's sum of debit and sum of credit  Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat This was the macro, I was trying to use earlier. The "columns to compare" are different in this because I was trying to use Journal ID as a matching content.

Calculate Balances from Debit and Credit Column in MySQL , Calculate Balances from Debit and Credit Column in MySQL Actually this query I was required to create in one of my project where I was developing a wallet system COALESCE(((SELECT SUM(credit) FROM user_transactions b WHERE b.id <= a.id AND user_id Please do share if you find it useful. A general ledger represents the record-keeping system for a company's financial data with debit and credit account records validated by a trial balance. more Accounts Receivable Subsidiary Ledger

https://social.msdn.microsoft.com/Forums/sqlserver, I want to calculate Balance that will be the sum of Debit - Credit + I prefer to get exact requirements on the query & not blindly code something  Increase your Credit Scores & Get Credit for the Bills You're Already Paying. 100% Free! New Credit Scores Take Effect Immediately. Free Credit Monitoring and Alerts Included.

Comments
  • Instead of giving sudo code, you should provide sample data and expected output
  • Thanks for your comment, I added some more detail..
  • By far the simplest solution that is aligned with the code in the OP.
  • Thanks for your response, I need RemainDebit and RemainCredit before this date '2018-03-21' AND '2019-03-21', in this case RemainDebit is 100+150=250 and RemainCredit is 200+300=500
  • @ElhamAzadfar It will didn't match any data when then date range is between '2018-03-21' AND '2018-06-21'
  • @ElhamAzadfar I edit my answer and add some information you can add your logic on CASW WHEN expression