{"id":11240,"date":"2021-02-08T07:09:04","date_gmt":"2021-02-08T06:09:04","guid":{"rendered":"https:\/\/sdna.lu\/?p=11240"},"modified":"2021-04-28T20:38:30","modified_gmt":"2021-04-28T19:38:30","slug":"query-verify-glyebalance","status":"publish","type":"post","link":"https:\/\/sdna.lu\/?p=11240","title":{"rendered":"Query: Verify GLYEBalance"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Select [GLMovement].[DBId] as [ResDBId], [GLMovement].[GLPeriodId] as [ResGLPeriodId]<br>, [GLMovement].[GLAccountId] as [ResGLAccountId], [GLMovement].[CurrencyId] as [ResCurrencyId]<br>, Round(Sum(Case When [GLMovement].[CD] = &#8216;D&#8217; Then [GLMovement].[Amount] Else 0 End),2) as [ResCalcDTotal]<br>, Round(Sum(Case When [GLMovement].[CD] = &#8216;C&#8217; Then [GLMovement].[Amount] Else 0 End),2) as [ResCalcCTotal]<br>, [GLYEBalance].[DTotal] as [ResDTotal], [GLYEBalance].[CTotal] as [ResCTotal]<br>from (([GLMovement] LEFT JOIN [GLYEBalance]<br>ON ([GLMovement].[DBId] = [GLYEBalance].[DBId]<br>and [GLMovement].[GLPeriodId] = [GLYEBalance].[GLPeriodId]<br>and [GLMovement].[GLAccountId] = [GLYEBalance].[GLAccountId]<br>and [GLMovement].[CurrencyId] = [GLYEBalance].[CurrencyId]))<br>INNER JOIN [GLPeriod] ON<br>([GLMovement].[GLPeriodId] = [GLPeriod].[Id] and [GLMovement].[DBId] = [GLPeriod].[DBId]))<br>where [GLPeriod].[YEStatus] in (1,2)<br>Group by [GLMovement].[DBId], [GLMovement].[GLPeriodId]<br>, [GLMovement].[GLAccountId], [GLMovement].[CurrencyId]<br>, [GLYEBalance].[DTotal], [GLYEBalance].[CTotal]<br>having Round(Sum(Case When [GLMovement].[CD] = &#8216;D&#8217; Then [GLMovement].[Amount] Else 0 End),2) &lt;> [GLYEBalance].[DTotal]<br>or Round(Sum(Case When [GLMovement].[CD] = &#8216;C&#8217; Then [GLMovement].[Amount] Else 0 End),2) &lt;> [GLYEBalance].[CTotal]<br>or [GLYEBalance].[DTotal] Is Null<br>or [GLYEBalance].[CTotal] Is Null<br>Union All<br>Select [GLYEBalance].[DBId] as [ResDBId], [GLYEBalance].[GLPeriodId] as [ResGLPeriodId]<br>, [GLYEBalance].[GLAccountId] as [ResGLAccountId], [GLYEBalance].[CurrencyId] as [ResCurrencyId]<br>, 0 as [ResCalcDTotal], 0 as [ResCalcCTotal]<br>, [GLYEBalance].[DTotal] as [ResDTotal], [GLYEBalance].[CTotal] as [ResDTotal]<br>from [GLYEBalance] INNER JOIN [GLPeriod]<br>ON ([GLYEBalance].[GLPeriodId] = [GLPeriod].[Id] and [GLYEBalance].[DBId] = [GLPeriod].[DBId])<br>where ([GLPeriod].[YEStatus] in (1,2)<br>and (((Not Round([GLYEBalance].[StartBalance] + [GLYEBalance].[DTotal] &#8211; [GLYEBalance].[CTotal],2) = [GLYEBalance].[EndBalance])<br>and Not [GLYEBalance].[Type] in (3,4))<br>or (Not Round([GLYEBalance].[DTotal] &#8211; [GLYEBalance].[CTotal],2) = [GLYEBalance].[EndBalance]<br>and [GLYEBalance].[Type] in (3,4)))) or Not Round([GLYEBalance].[EndBalance], 2) = [GLYEBalance].[EndBalance]<br>Union All<br>Select distinct [GLMovement].[DBId] as [ResDBId]<br>, [GLMovement].[GLPeriodId] as [ResGLPeriodId]<br>, [GLMovement].[GLAccountId] as [ResGLAccountId]<br>, [GLMovement].[CurrencyId] as [ResCurrencyId]<br>, 0 as [ResCalcDTotal]<br>, 0 as [ResCalcCTotal]<br>, 0 as [ResDTotal]<br>, 0 as [ResCTotal]<br>from ([GLMovement] LEFT JOIN [GLYEBalance]<br>ON ([GLMovement].[DBId] = [GLYEBalance].[DBId]<br>and [GLMovement].[GLPeriodId] = [GLYEBalance].[GLPeriodId]<br>and [GLMovement].[GLAccountId] = [GLYEBalance].[GLAccountId]<br>and [GLMovement].[CurrencyId] = [GLYEBalance].[CurrencyId]))<br>LEFT JOIN [GLPeriod]<br>ON ([GLMovement].[DBId] = [GLPeriod].[DBId]<br>and [GLMovement].[GLPeriodId] = [GLPeriod].[Id])<br>where [GLPeriod].[YEStatus] in (1,2)<br>and [GLYEBalance].[DBId] Is Null<br>Union All<br>Select<br>[GLYEBalance].[DBId] as [ResDBId]<br>, [GLYEBalance].[GLPeriodId] as [ResGLPeriodId]<br>, [GLYEBalance].[GLAccountId] as [ResGLAccountId]<br>, [GLYEBalance].[CurrencyId] as [ResCurrencyId]<br>, Round(Sum(Case When [GLMovement].[CD] = &#8216;D&#8217; Then [GLMovement].[Amount] Else 0 End),2) as [ResCalcDTotal]<br>, Round(Sum(Case When [GLMovement].[CD] = &#8216;C&#8217; Then [GLMovement].[Amount] Else 0 End),2) as [ResCalcCTotal]<br>, [GLYEBalance].[DTotal] as [ResDTotal]<br>, [GLYEBalance].[CTotal] as [ResCTotal]<br>from ([GLYEBalance] LEFT JOIN [GLMovement]<br>ON ([GLYEBalance].[DBId] = [GLMovement].[DBId]<br>and [GLYEBalance].[GLPeriodId] = [GLMovement].[GLPeriodId]<br>and [GLYEBalance].[GLAccountId] = [GLMovement].[GLAccountId]<br>and [GLYEBalance].[CurrencyId] = [GLMovement].[CurrencyId])<br>LEFT JOIN [GLPeriod]<br>ON ([GLYEBalance].[GLPeriodId] = [GLPeriod].[Id]<br>and [GLYEBalance].[DBId] = [GLPeriod].[DBId]))<br>where [GLPeriod].[YEStatus] in (1,2)<br>Group by<br>[GLYEBalance].[DBId]<br>, [GLYEBalance].[GLPeriodId]<br>, [GLYEBalance].[GLAccountId]<br>, [GLYEBalance].[CurrencyId]<br>, [GLYEBalance].[DTotal]<br>, [GLYEBalance].[CTotal]<br>having Round(Sum(Case When [GLMovement].[CD] = &#8216;D&#8217; Then [GLMovement].[Amount] Else 0 End),2) &lt;> [GLYEBalance].[DTotal]<br>or Round(Sum(Case When [GLMovement].[CD] = &#8216;C&#8217; Then [GLMovement].[Amount] Else 0 End),2) &lt;> [GLYEBalance].[CTotal]<br>order by [ResDBId], [ResGLPeriodId], [ResCurrencyId]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Select [GLMovement].[DBId] as [ResDBId], [GLMovement].[GLPeriodId] as [ResGLPeriodId], [GLMovement].[GLAccountId] as [ResGLAccountId], [GLMovement].[CurrencyId] as [ResCurrencyId], Round(Sum(Case When [GLMovement].[CD] = &#8216;D&#8217; Then [GLMovement].[Amount] Else 0 End),2) as [ResCalcDTotal], Round(Sum(Case When [GLMovement].[CD] = &#8216;C&#8217; 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] = [&hellip;]<\/p>\n","protected":false},"author":19,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[487],"tags":[],"class_list":["post-11240","post","type-post","status-publish","format-standard","hentry","category-query"],"_links":{"self":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11240","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/users\/19"}],"replies":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11240"}],"version-history":[{"count":15,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11240\/revisions"}],"predecessor-version":[{"id":11508,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11240\/revisions\/11508"}],"wp:attachment":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11240"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11240"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11240"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}