[Optimum] How to check the difference figure of P&L vs Balance Sheet

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.