the problem is in left join.
wherever i use store procedure
example :
USE [AccDatabase]
GO
/****** Object: StoredProcedure [dbo].[ReportTrialBalance] Script Date: 12/06/2012 15:51:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create>
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ReportTrialBalance]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
with TrialBalanceOutput as
(
SELECT dbo.acc_TransParent.iLedgerID, dbo.acc_TransParent.nPageno, dbo.acc_TransParent.dDate, dbo.acc_TransParent.ivhTypeID,
dbo.acc_TransParent.nRefno, dbo.acc_TransParent.cComment, dbo.acc_TransParent.lPosted, dbo.acc_TransChild.iRecnoTrans,
dbo.acc_TransChild.iLedgerID AS Expr2, dbo.acc_TransChild.nLine, dbo.acc_TransChild.iAccountID, dbo.acc_TransChild.cSubname, dbo.acc_TransChild.iSubacc,
dbo.acc_TransChild.iCurID, dbo.acc_TransChild.cDetail, dbo.acc_TransChild.nAmdebit, dbo.acc_TransChild.nAmcredit, dbo.acc_TransChild.nRatelocal,
dbo.acc_TransChild.nRateus, dbo.acc_TransChild.nAmlocal, dbo.acc_TransChild.nAmus, dbo.acc_TransChild.dValuedate, dbo.acc_TransChild.lBank,
dbo.acc_Account.cType, dbo.acc_Account.nAccountno, dbo.acc_Account.cFullname, dbo.acc_Account.lDiffexch, dbo.acc_Account.cSubacc, dbo.acc_Cur.cCur,
dbo.acc_Cur.cFullname AS Expr1, dbo.acc_Acctype.cAccType
FROM dbo.acc_TransParent INNER JOIN
dbo.acc_TransChild ON dbo.acc_TransParent.iLedgerID = dbo.acc_TransChild.iLedgerID INNER JOIN
dbo.acc_Account ON dbo.acc_TransChild.iAccountID = dbo.acc_Account.iAccountID INNER JOIN
dbo.acc_Cur ON dbo.acc_TransChild.iCurID = dbo.acc_Cur.iCurID INNER JOIN
dbo.acc_Acctype ON dbo.acc_Account.iAcctypeID = dbo.acc_Acctype.iAccTypeID
)
SELECT TrialBalanceOutput.nAccountno, TrialBalanceOutput.cCur,
SUM(TrialBalanceOutput.nAmDebit) as sumamdb,
SUM(TrialBalanceOutput.nAmcredit) as sumamcr,
sum(CASE WHEN TrialBalanceOutput.nAmdebit > 0 THEN TrialBalanceOutput.nAmlocal ELSE 0 END ) AS sumAmLocaldb,
sum(CASE WHEN TrialBalanceOutput.nAmdebit < 0 THEN TrialBalanceOutput.nAmlocal ELSE 0 END ) AS sumAmLocalcr,
sum(CASE WHEN TrialBalanceOutput.nAmdebit > 0 THEN TrialBalanceOutput.nAmus ELSE 0 END ) AS sumAmusdb,
sum(CASE WHEN TrialBalanceOutput.nAmdebit < 0 THEN TrialBalanceOutput.nAmus ELSE 0 END ) AS sumAmuscr
FROM TrialBalanceOutput
group by TrialBalanceOutput.nAccountno, TrialBalanceOutput.cCur
END
First i select information from different tabel the i will work on the result i have,
so you have to divide the work into two parts. select the information then make your conditions
Second you have left join which is wrong, it is preferable to use left right join, and the better inner join
this procedure is connect to any crystal report
so when you run report , run the procedure first
hope this will help
good luck