1. Open SQL Management studio and execute the following scripts.
Please refer How to install Microsoft SQL Management Studio
AND
Please refer How to Connect to Database use Microsoft SQL Server Management Studio
Copy the output to an excel file to compare.
2. P&L amount from the Balance Sheet query:
**Change the date to the date range you want to check
SELECT GA.GLAccountCode, GA.Description, SUM(IsNull(GT.Credit,0) - IsNull(GT.Debit,0)) as Amount FROM dbo.GLTransactions GT
JOIN dbo.GLAccounts GA ON GA.Id = GT.GLAccountId
JOIN dbo.Accounts A ON A.Id = GA.AccountId
WHERE A.ChartSequence < 10
AND PostingDate BETWEEN '2015-4-1' AND '2015-12-31'
AND gt.IsCancelled = 0 AND GT.IsPostDatedCheque = 0
GROUP BY GA.GLAccountCode, GA.Description
ORDER BY GA.GLAccountCode
3. P&L amount from the P&L report itself:
**Change the date to the date range you want to check
DECLARE @sdate1 date = '2015-4-1'
DECLARE @edate1 date = '2015-12-31'
DECLARE @startDate DATE, @endDate DATE
SELECT @startDate = MIN(StartDate) FROM (SELECT @sdate1 AS StartDate) D
SELECT @endDate = MAX(EndDate) FROM (SELECT @edate1 AS EndDate) D
Declare @PnLMain Table(GLAccountCode varchar(20),
Level1Code varchar(20), Level1Name nvarchar(100),
Level2Code varchar(20), Level2Name nvarchar(100),
Level3Code varchar(20), Level3Name nvarchar(100),
Code varchar(20), AccountName nvarchar(100), ChartSequence int, DRCR1 char(2))
Insert Into @PnLMain(GLAccountCode, Level1Code, Level1Name, Level2Code, Level2Name, Level3Code, Level3Name,
Code, AccountName, ChartSequence, DRCR1)
exec [dbo].[SP_GetAccountTreeUpTo3Level]
Declare @pdata Table(AccountCode varchar(20), Amount1 numeric(28,2))
Insert Into @pdata(AccountCode, Amount1)
Select G.GLAccountCode,
case when G.PostingDate between @sdate1 and @edate1 then G.Amount else 0 end as Amount1
From
(
Select GLA.GLAccountCode, GLT.PostingDate, IsNull(GLT.Credit,0) - IsNull(GLT.Debit,0) as Amount
From GLAccounts GLA
join GLTransactions GLT on GLT.GLAccountId = GLA.Id
Join Accounts A on GLA.AccountId = A.Id and A.ChartSequence < 10
Where GLT.IsCancelled = 0 and GLT.IsPostDatedCheque = 0
And PostingDate Between @startDate and @endDate
) G
Select B.Level1Code, B.Level1Name, B.Level2Code, B.Level2Name, B.Level3Code, B.Level3Name, B.Code, B.AccountName, B.DRCR1, B.ChartSequence,
ISNULL(SUM(case DRCR1 when 'DR' then 0 - PD.Amount1 else PD.Amount1 END),0) as Amount1
From @PnLMain B left join
(Select AccountCode, Sum(Amount1) as Amount1
From @pdata group by AccountCode) PD on B.GLAccountCode = PD.Accountcode
and B.Level1Code not in (Select GLAccountCode From GLAccounts GLA join SpecialAccounts SA on ISNULL(GLA.SpecialAccountId,0) = SA.Id and SpecialAccountCode IN('OST', 'CST'))
GROUP BY B.Level1Code, B.Level1Name, B.Level2Code, B.Level2Name, B.Level3Code, B.Level3Name, B.Code, B.AccountName, B.DRCR1, B.ChartSequence
HAVING ISNULL(SUM(case DRCR1 when 'DR' then 0 - PD.Amount1 else PD.Amount1 END),0) != 0
4. Compare the output from the 2 results above.