Select [Org].[DBId] as [ResDBId]
, [Org].[GLAccountId] as [ResGLAccountId]
, [Org].[CurrencyId] as [ResCurrencyId]
, [Org].[Type] as [ResType]
, [Dest].[CyStartBalance] as [ResCyStartBalance]
, [Org].[CyEndBalance] as [ResCyEndBalance]
, [Org].[Value] as [ResOptionValue] from
(Select [GLYEBalance].[DBId], [GLYEBalance].[GLAccountId], [GLYEBalance].[CurrencyId], [GLYEBalance].[Type], [GLYEBalance].[StartDate], [GLYEBalance].[EndDate], [GLYEBalance].[CyStartBalance], [GLYEBalance].[CyEndBalance], [Options].[Value]
from (([GLYEBalance] INNER JOIN [GLPeriod]
ON ([GLYEBalance].[GLPeriodId] = [GLPeriod].[Id] and [GLYEBalance].[DBId] = [GLPeriod].[DBId]))
LEFT JOIN [Options]
ON ([Options].[DBId] = [GLYEBalance].[DBId] and [Options].[Variable] = 'RetainedEarnings_GLAccountId' and [Options].[Value] = [GLYEBalance].[GLAccountId]))
where [GLPeriod].[YEStatus] in (1, 2) and Not [GLYEBalance].[Type] in (3, 4)) as [Org]
INNER JOIN [GLYEBalance] as [Dest]
ON ([Org].[DBId] = [Dest].[DBId] and [Org].[GLAccountId] = [Dest].[GLAccountId] and [Org].[CurrencyId] = [Dest].[CurrencyId] and [Org].[EndDate] + 1 = [Dest].[StartDate])
where Not [Org].[CyEndBalance] = [Dest].[CyStartBalance] and [Org].[Value] Is Null
Union All
Select [GLYEBalance].[DBId] as [ResDBId]
, [GLYEBalance].[GLAccountId] as [ResGLAccountId]
, [GLYEBalance].[CurrencyId] as [ResCurrencyId]
, [GLYEBalance].[Type] as [ResType]
, [GLYEBalance].[CyStartBalance] as [ResCyStartBalance]
, [GLYEBalance].[CyEndBalance] as [ResCyEndBalance]
, '' as [ResOptionValue]
from [GLYEBalance] INNER JOIN [GLPeriod]
ON ([GLYEBalance].[GLPeriodId] = [GLPeriod].[Id] and [GLYEBalance].[DBId] = [GLPeriod].[DBId])
where Not [GLPeriod].[YEStatus] = 0 and [CyStartBalance] + [CyDTotal] - [CyCTotal] - [CyEndBalance] > 0.01
order by [ResDBId]
