how to calculate all previous datas sum of a sql column on a date range report

Question

I have a crystal report with debit credit columns using a sql command. This report contains a date to date filtering parameters. So the problem is if i filter the report to date range i need all the previous data sum using a sql command.

Select SUM(CAST(debit as DECIMAL(9,2)))- SUM(CAST(credit as DECIMAL(9,2)))
from sum_balance 
where sum_date < sum_date

this is my code but i can't get the result from it. (e.g. : if the report starting from 2014-07-01 then i need the sum(debit - credit) of all previous data before 2014-07-01). Can anyone help me to find a solution for this. THe main thing is to add a brought forward balance using sql command on first row. If it is null then it should be 0.00.

Answer 1

When you need records previous than some date then you need to have that comparision date so that records can be extracted.

Your where clause where sum_date < sum_date won't work this way either you change the right side comparision operator in query or create a parameter in crystal so that user can enter the required end date during run time.

option 1:

E.g: where sum_date < currentdate

option 2:

Create a parameter and declare it in Record Selection Formula in crystal reports so that formed query will be something like

where sum_date < 2014-07-01

Answer 2

Here is your sample table

CREATE TABLE #TEMP(DEBIT NUMERIC(20,2),CREDIT NUMERIC(20,2),DT VARCHAR(20))

INSERT INTO #TEMP
SELECT 1000 DEBIT,500 CREDIT,'2014-11-27' DT
UNION ALL
SELECT 2000 DEBIT,700 CREDIT,'2014-11-28' DT
UNION ALL
SELECT 3000 DEBIT,900 CREDIT,'2014-11-29' DT

I am updating answer for your updated requirement

QUERY 1

This will bring the total till current date, ie, for 2014-11-28 the amount will be (1000+2000)-(500+700), for 2014-11-29 the amount will be (1000+2000+3000)-(500+700+900)

SELECT T2.DEBIT,T2.CREDIT,T2.sum_date,
  (SELECT SUM(DEBIT)-SUM(CREDIT) FROM sum_balance WHERE sum_date <= CAST(T2.sum_date AS DATE))
   AMOUNT
FROM sum_balance T2

• SQL FIDDLE

QUERY 2

This will bring the sum till previous day, that will be opening balance for today's date ie, for 2014-11-29 the amount will be (1000+2000)-(500+700). For easy understanding I have added the previous column also.

;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY CAST(T2.sum_date AS DATE))RNO,
     T2.DEBIT,T2.CREDIT,T2.sum_date,
    ISNULL((SELECT SUM(DEBIT)-SUM(CREDIT) FROM sum_balance WHERE sum_date <= CAST(T2.sum_date AS DATE)),0)
     AMOUNT
    FROM sum_balance T2
)
SELECT C1.*,ISNULL(C2.AMOUNT,0) CARRYFORWARD
FROM CTE C1
LEFT JOIN CTE C2 ON C1.RNO=C2.RNO+1

• SQL FIDDLE

You can use QUERY 2 and you will get opening balance till previous day in CARRYFORWARD column. Please leave a message or comment for any changes.

Answer 3

You can try this:-

SELECT SUM(CAST(debit as DECIMAL(9,2)))- SUM(CAST(credit as DECIMAL(9,2)))
FROM sum_balance 
WHERE sum_date < (Select Max(sum_date) FROM sum_balance)