Select [GLMovement].[DBId] as [ResDBId], [GLMovement].[GLPeriodId] as [ResGLPeriodId]
, [GLMovement].[GLAccountId] as [ResGLAccountId], [GLMovement].[CurrencyId] as [ResCurrencyId]
, Round(Sum(Case When [GLMovement].[CD] = ‘D’ Then [GLMovement].[Amount] Else 0 End),2) as [ResCalcDTotal]
, Round(Sum(Case When [GLMovement].[CD] = ‘C’ Then [GLMovement].[Amount] Else 0 End),2) as [ResCalcCTotal]
, [GLYEBalance].[DTotal] as [ResDTotal], [GLYEBalance].[CTotal] as [ResCTotal]
from (([GLMovement] LEFT JOIN [GLYEBalance]
ON ([GLMovement].[DBId] = [GLYEBalance].[DBId]
and [GLMovement].[GLPeriodId] = [GLYEBalance].[GLPeriodId]
and [GLMovement].[GLAccountId] = [GLYEBalance].[GLAccountId]
and [GLMovement].[CurrencyId] = [GLYEBalance].[CurrencyId]))
INNER JOIN [GLPeriod] ON
([GLMovement].[GLPeriodId] = [GLPeriod].[Id] and [GLMovement].[DBId] = [GLPeriod].[DBId]))
where [GLPeriod].[YEStatus] in (1,2)
Group by [GLMovement].[DBId], [GLMovement].[GLPeriodId]
, [GLMovement].[GLAccountId], [GLMovement].[CurrencyId]
, [GLYEBalance].[DTotal], [GLYEBalance].[CTotal]
having Round(Sum(Case When [GLMovement].[CD] = ‘D’ Then [GLMovement].[Amount] Else 0 End),2) <> [GLYEBalance].[DTotal]
or Round(Sum(Case When [GLMovement].[CD] = ‘C’ Then [GLMovement].[Amount] Else 0 End),2) <> [GLYEBalance].[CTotal]
or [GLYEBalance].[DTotal] Is Null
or [GLYEBalance].[CTotal] Is Null
Union All
Select [GLYEBalance].[DBId] as [ResDBId], [GLYEBalance].[GLPeriodId] as [ResGLPeriodId]
, [GLYEBalance].[GLAccountId] as [ResGLAccountId], [GLYEBalance].[CurrencyId] as [ResCurrencyId]
, 0 as [ResCalcDTotal], 0 as [ResCalcCTotal]
, [GLYEBalance].[DTotal] as [ResDTotal], [GLYEBalance].[CTotal] as [ResDTotal]
from [GLYEBalance] INNER JOIN [GLPeriod]
ON ([GLYEBalance].[GLPeriodId] = [GLPeriod].[Id] and [GLYEBalance].[DBId] = [GLPeriod].[DBId])
where ([GLPeriod].[YEStatus] in (1,2)
and (((Not Round([GLYEBalance].[StartBalance] + [GLYEBalance].[DTotal] – [GLYEBalance].[CTotal],2) = [GLYEBalance].[EndBalance])
and Not [GLYEBalance].[Type] in (3,4))
or (Not Round([GLYEBalance].[DTotal] – [GLYEBalance].[CTotal],2) = [GLYEBalance].[EndBalance]
and [GLYEBalance].[Type] in (3,4)))) or Not Round([GLYEBalance].[EndBalance], 2) = [GLYEBalance].[EndBalance]
Union All
Select distinct [GLMovement].[DBId] as [ResDBId]
, [GLMovement].[GLPeriodId] as [ResGLPeriodId]
, [GLMovement].[GLAccountId] as [ResGLAccountId]
, [GLMovement].[CurrencyId] as [ResCurrencyId]
, 0 as [ResCalcDTotal]
, 0 as [ResCalcCTotal]
, 0 as [ResDTotal]
, 0 as [ResCTotal]
from ([GLMovement] LEFT JOIN [GLYEBalance]
ON ([GLMovement].[DBId] = [GLYEBalance].[DBId]
and [GLMovement].[GLPeriodId] = [GLYEBalance].[GLPeriodId]
and [GLMovement].[GLAccountId] = [GLYEBalance].[GLAccountId]
and [GLMovement].[CurrencyId] = [GLYEBalance].[CurrencyId]))
LEFT JOIN [GLPeriod]
ON ([GLMovement].[DBId] = [GLPeriod].[DBId]
and [GLMovement].[GLPeriodId] = [GLPeriod].[Id])
where [GLPeriod].[YEStatus] in (1,2)
and [GLYEBalance].[DBId] Is Null
Union All
Select
[GLYEBalance].[DBId] as [ResDBId]
, [GLYEBalance].[GLPeriodId] as [ResGLPeriodId]
, [GLYEBalance].[GLAccountId] as [ResGLAccountId]
, [GLYEBalance].[CurrencyId] as [ResCurrencyId]
, Round(Sum(Case When [GLMovement].[CD] = ‘D’ Then [GLMovement].[Amount] Else 0 End),2) as [ResCalcDTotal]
, Round(Sum(Case When [GLMovement].[CD] = ‘C’ Then [GLMovement].[Amount] Else 0 End),2) as [ResCalcCTotal]
, [GLYEBalance].[DTotal] as [ResDTotal]
, [GLYEBalance].[CTotal] as [ResCTotal]
from ([GLYEBalance] LEFT JOIN [GLMovement]
ON ([GLYEBalance].[DBId] = [GLMovement].[DBId]
and [GLYEBalance].[GLPeriodId] = [GLMovement].[GLPeriodId]
and [GLYEBalance].[GLAccountId] = [GLMovement].[GLAccountId]
and [GLYEBalance].[CurrencyId] = [GLMovement].[CurrencyId])
LEFT JOIN [GLPeriod]
ON ([GLYEBalance].[GLPeriodId] = [GLPeriod].[Id]
and [GLYEBalance].[DBId] = [GLPeriod].[DBId]))
where [GLPeriod].[YEStatus] in (1,2)
Group by
[GLYEBalance].[DBId]
, [GLYEBalance].[GLPeriodId]
, [GLYEBalance].[GLAccountId]
, [GLYEBalance].[CurrencyId]
, [GLYEBalance].[DTotal]
, [GLYEBalance].[CTotal]
having Round(Sum(Case When [GLMovement].[CD] = ‘D’ Then [GLMovement].[Amount] Else 0 End),2) <> [GLYEBalance].[DTotal]
or Round(Sum(Case When [GLMovement].[CD] = ‘C’ Then [GLMovement].[Amount] Else 0 End),2) <> [GLYEBalance].[CTotal]
order by [ResDBId], [ResGLPeriodId], [ResCurrencyId]
