{"id":11786,"date":"2022-07-04T14:06:46","date_gmt":"2022-07-04T13:06:46","guid":{"rendered":"https:\/\/sdna.lu\/?p=11786"},"modified":"2022-09-01T14:40:51","modified_gmt":"2022-09-01T13:40:51","slug":"query-select-invalid-glyebalance","status":"publish","type":"post","link":"https:\/\/sdna.lu\/?p=11786","title":{"rendered":"Query: Select invalid GLYEBalance"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>Select [Org].[DBId] as [ResDBId]<br>, [Org].[GLAccountId] as [ResGLAccountId]<br>, [Org].[CurrencyId] as [ResCurrencyId]<br>, [Org].[Type] as [ResType]<br>, [Dest].[CyStartBalance] as [ResCyStartBalance]<br>, [Org].[CyEndBalance] as [ResCyEndBalance]<br>, [Org].[Value] as [ResOptionValue] from<br>(Select [GLYEBalance].[DBId], [GLYEBalance].[GLAccountId], [GLYEBalance].[CurrencyId], [GLYEBalance].[Type], [GLYEBalance].[StartDate], [GLYEBalance].[EndDate], [GLYEBalance].[CyStartBalance], [GLYEBalance].[CyEndBalance], [Options].[Value]<br>from (([GLYEBalance] INNER JOIN [GLPeriod]<br>ON ([GLYEBalance].[GLPeriodId] = [GLPeriod].[Id] and [GLYEBalance].[DBId] = [GLPeriod].[DBId]))<br>LEFT JOIN [Options]<br>ON ([Options].[DBId] = [GLYEBalance].[DBId] and [Options].[Variable] = 'RetainedEarnings_GLAccountId' and [Options].[Value] = [GLYEBalance].[GLAccountId]))<br>where [GLPeriod].[YEStatus] in (1, 2) and Not [GLYEBalance].[Type] in (3, 4)) as [Org]<br>INNER JOIN [GLYEBalance] as [Dest]<br>ON ([Org].[DBId] = [Dest].[DBId] and [Org].[GLAccountId] = [Dest].[GLAccountId] and [Org].[CurrencyId] = [Dest].[CurrencyId] and [Org].[EndDate] + 1 = [Dest].[StartDate])<br>where Not [Org].[CyEndBalance] = [Dest].[CyStartBalance] and [Org].[Value] Is Null<br>Union All<br>Select [GLYEBalance].[DBId] as [ResDBId]<br>, [GLYEBalance].[GLAccountId] as [ResGLAccountId]<br>, [GLYEBalance].[CurrencyId] as [ResCurrencyId]<br>, [GLYEBalance].[Type] as [ResType]<br>, [GLYEBalance].[CyStartBalance] as [ResCyStartBalance]<br>, [GLYEBalance].[CyEndBalance] as [ResCyEndBalance]<br>, '' as [ResOptionValue]<br>from [GLYEBalance] INNER JOIN [GLPeriod]<br>ON ([GLYEBalance].[GLPeriodId] = [GLPeriod].[Id] and [GLYEBalance].[DBId] = [GLPeriod].[DBId])<br>where Not [GLPeriod].[YEStatus] = 0 and [CyStartBalance] + [CyDTotal] - [CyCTotal] - [CyEndBalance] &gt; 0.01<br>order by [ResDBId]<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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] = &#8216;RetainedEarnings_GLAccountId&#8217; and [Options].[Value] = [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[487],"tags":[],"class_list":["post-11786","post","type-post","status-publish","format-standard","hentry","category-query"],"_links":{"self":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11786","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\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11786"}],"version-history":[{"count":7,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11786\/revisions"}],"predecessor-version":[{"id":11798,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11786\/revisions\/11798"}],"wp:attachment":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11786"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11786"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11786"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}