How to Subtract two columns and show the result in third column

[Former Member]

This is the data....

Date DocNum Amount Debit Credit

2011-04-21 00:00:00.000 9150051 497265.000000 422605.000000 497265.000000

2011-05-26 00:00:00.000 9150188 497265.000000 74660. 000000 497265.000000

2011-04-07 00:00:00.000 9290020 497265.000000 0.000000 497265.000000

I want to display the total Sum (Debit) - 497265(Amount) in Credit column

kindly give me the solution for above problem.

Regards

Rahul

[Former Member]

i got the solution

[Former Member]

Hi Rahul,

You need to keep your current row in detail section and add a summary row for Credit column.

Thanks,

Gordon

[Former Member]

Hi,

I am working on this Stored Procedure....i want to display the SUM of Debit Amount - Amount in the Credit Column.

ALTER PROCEDURE [dbo].[Customer_report]

@Fromdate datetime,

@Todate datetime,

@Customer_num varchar(10)

as

SELECT distinct OINV.DocDate AS 'Posting date', OINV.CardCode AS 'Customer no.',

OCRD.balance as 'Amount', OINV.Doctotal as ' Debit Amount' , SUM (OINV.Doctotal)-Max(OCRD.balance) as 'Credit',

FROM OINV INNER JOIN

OCRD ON OINV.CardCode = OCRD.CardCode

WHERE (OINV.DocDate BETWEEN @Fromdate and @Todate and OINV.CardCode =@Customer_num)

union all

SELECT distinct ORCT.DocDate AS 'Posting date', ORCT.CardCode AS 'Customer no.',

OCRD.balance as 'Amount', ORCT.NoDocSum AS ' Debit Amount', SUM (ORCT.NoDocSum)-Max(OCRD.balance) as 'Credit',

ORCT.CardCode AS 'Customer no.', ORCT.CardName AS 'Customer name',

ORCT.DocDueDate AS 'Due date',ORCT.Address

FROM ORCT INNER JOIN

OCRD ON ORCT.CardCode = OCRD.CardCode

WHERE (ORCT.DocDate BETWEEN @Fromdate and @Todate and ORCT.CardCode = @Customer_num)

EXEC Customer_report @Fromdate ='04/02/2011',@Todate='06/02/2011',@Customer_num='c00032'

But when i execute this Procedure , it shows an Error Message

"Column 'OINV.DocDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Kindly help me to solve the above problem

Regards

Rahul

Edited by: 118Rahul on Jun 4, 2011 10:39 AM

Edited by: 118Rahul on Jun 4, 2011 2:40 PM

[Former Member]

There are coule of problems.

Try this first under query manager:

Declare @Fromdate datetime,

Declare @Todate datetime,

Declare @Customer_num varchar(10)

SELECT OINV.DocDate AS 'Posting date', OINV.CardCode AS 'Customer no.',

OCRD.balance as 'Amount', OINV.Doctotal as ' Debit Amount' , SUM (OINV.Doctotal)-Max(OCRD.balance) as 'Credit',

FROM OINV INNER JOIN

OCRD ON OINV.CardCode = OCRD.CardCode

WHERE OINV.DocDate BETWEEN @Fromdate and @Todate and OINV.CardCode =@Customer_num

GROUP BY OINV.DocDate,OINV.CardCode,OCRD.balance, OINV.Doctotal

union all

SELECT ORCT.DocDate AS 'Posting date', ORCT.CardCode AS 'Customer no.',

OCRD.balance as 'Amount', ORCT.NoDocSum AS ' Debit Amount', SUM (ORCT.NoDocSum)-Max(OCRD.balance) as 'Credit',

ORCT.CardCode AS 'Customer no.', ORCT.CardName AS 'Customer name',

ORCT.DocDueDate AS 'Due date',ORCT.Address

FROM ORCT INNER JOIN

OCRD ON ORCT.CardCode = OCRD.CardCode

WHERE ORCT.DocDate BETWEEN @Fromdate and @Todate and ORCT.CardCode = @Customer_num

GROUP BY ORCT.DocDate, ORCT.CardCode,OCRD.balance, ORCT.NoDocSum

You must make sure the query work first before proceed further.

[Former Member]

Hi,

I am Trying this Query but it displays Error Message :-

" Column 'OINV.DocNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

"

Kindly help me to solve this problem

Regards

Rahul

[former_member206488]

Hi Rahul,

You want ti display subtract in Excel file or SQL query.

If it is SQL query: paste you query here.

if it is in Excel use =B1-A1 formula, change B1 to Debit column and A1 to Amount coulmn name.

Thanks,

Neetu

[Former Member]

Hi Neetu,

I want t o display in CRYSTAL REPORT. This is my data

DATE DocNum Amount Debit Credit

2011-04-21 9150051 497265.000000 422605.000000 497265.000000

2011-05-26 9150188 497265.000000 74660.000000 497265.000000

2011-04-07 9290020 497265.000000 NULL 497265.000000

kindly give me the Solution

Thanks

Rahul

Edited by: 118Rahul on Jun 3, 2011 4:34 PM

[former_member206488]

Create forumla in crystal report,

in forumal select Debit field- amount field and use that formula field for credit;

[Former Member]

Hi Neetu,

I am using this formula :-

Sum ({Customer_report;1. Debit Amount})-{Customer_report;1.Amount}

but its removing the whole data. its not working

Regards

rahul

[Former Member]

Hi,

This is the data....

Date DocNum Amount Debit Credit

2011-04-21 00:00:00.000 9150051 497265.000000 422605.000000 497265.000000

2011-05-26 00:00:00.000 9150188 497265.000000 74660. 000000 497265.000000

2011-04-07 00:00:00.000 9290020 497265.000000 0.000000 497265.000000

I want to display the total Sum (Debit) - 497265(Amount) in Credit column

I am using this formula :-

Sum ({Customer_report;1. Debit Amount})-{Customer_report;1.Amount}

but its removing the whole data. its not working

kindly give me the solution for above problem.

Regards

Rahul

[former_member206488]

if you want to use SUM(Debit)- Amount then , how will you display credit column value against each row?

If you use Debit -Amount then credit column will be dispalyed for each line.