Ik haal met onderstaande SQL-code Exact tabellen op. Op maanden met actuals krijg ik dubbele regels en daardoor dubbeltellingen op Budget. Aan de rowid’s van ReportingBalance zie ik dat er dubbelingen ontstaan.
Bij deze administratie wordt geen gebruik gemaakt van kostenplaatsen en -dragers (zijn leeg).
Voorbeeld
SELECT
COALESCE(
[glaccountsbulk].[code],
[budgets].[glaccountcode],
[glaccountclassificationmappings].[glaccountcode] ,
[reportingbalance].[glaccountcode]
) AS [Grootboekrek],
COALESCE(
[glaccountsbulk].[division] ,
[budgets].[division] ,
[glaccountclassificationmappings].[division] ,
[glclassifications.a].[division] ,
[glclassifications.b].[division] ,
[reportingbalance].[division]
) AS Divi,
COALESCE(
[glaccountsbulk].[divisionname] ,
[budgets].[divisionname]
) AS Bedrijfsnaam,
COALESCE(
[budgets].[reportingyear],
[reportingbalance].[reportingyear]
) AS Jaar,
COALESCE(
[budgets].[reportingperiod] ,
[reportingbalance].[reportingperiod]
) AS Maand,
sum([reportingbalance].[amount]) AS [amount],
sum([budgets].[amountdc]) AS [amountdc],
[reportingbalance].[balancetype] AS [balancetype (reportingbalance)],
[glaccountsbulk].[balancetype] AS [balancetype],
[budgets].[budgetscenariodescription] AS [budgetscenariodescription],
[glaccountclassificationmappings].[classification] AS [classification],
[glaccountclassificationmappings].[classificationcode] AS [classificationcode],
[glaccountclassificationmappings].[classificationdescription] AS [classificationdescription],
[glclassifications.b].[code] AS [code (glclassifications) #1],
[glclassifications.a].[code] AS [code (glclassifications)],
[glclassifications.b].[description] AS [description (glclassifications) #1],
[glclassifications.a].[description] AS [description (glclassifications)],
[glaccountsbulk].[description] AS [description],
[glaccountclassificationmappings].[glaccount] AS [glaccount (glaccountclassificationmappings)],
[reportingbalance].[glaccount] AS [glaccount (reportingbalance)],
[budgets].[glaccount] AS [glaccount],
[glaccountclassificationmappings].[glaccountdescription] AS [glaccountdescription (glaccountclassificationmappings)],
[reportingbalance].[glaccountdescription] AS [glaccountdescription (reportingbalance)],
[budgets].[glaccountdescription] AS [glaccountdescription],
[glaccountclassificationmappings].[glschemecode] AS [glschemecode],
[budgets].[rowid$] AS [rowid$ (budgets)],
[glaccountclassificationmappings].[rowid$] AS [rowid$ (glaccountclassificationmappings)],
[glclassifications.b].[rowid$] AS [rowid$ (glclassifications) #1],
[glclassifications.a].[rowid$] AS [rowid$ (glclassifications)],
[reportingbalance].[rowid$] AS [rowid$ (reportingbalance)],
[glaccountsbulk].[rowid$] AS [rowid$]
FROM [dbo].[glaccountsbulk] [glaccountsbulk]
LEFT JOIN [dbo].[glaccountclassificationmappings] [glaccountclassificationmappings] ON (([glaccountsbulk].[division] = [glaccountclassificationmappings].[division]) AND ([glaccountsbulk].[code] = [glaccountclassificationmappings].[glaccountcode]))
LEFT JOIN [dbo].[glclassifications] [glclassifications.a] ON (([glaccountclassificationmappings].[division] = [glclassifications.a].[division]) AND (LEFT([glaccountclassificationmappings].[classificationcode],(CASE WHEN 9 >= 0 THEN 9 ELSE NULL END)) = [glclassifications.a].[code]))
LEFT JOIN [dbo].[budgets] [budgets] ON (([glaccountsbulk].[division] = [budgets].[division]) AND ([glaccountsbulk].[code] = [budgets].[glaccountcode]))
LEFT JOIN [dbo].[reportingbalance] [reportingbalance] ON (([budgets].[division] = [reportingbalance].[division]) AND ([budgets].[glaccountcode] = [reportingbalance].[glaccountcode]) AND ([budgets].[reportingyear] = [reportingbalance].[reportingyear]) AND ([budgets].[reportingperiod] = [reportingbalance].[reportingperiod]))
LEFT JOIN [dbo].[glclassifications] [glclassifications.b] ON (([glaccountclassificationmappings].[division] = [glclassifications.b].[division]) AND (LEFT([glaccountclassificationmappings].[classificationcode],(CASE WHEN 6 >= 0 THEN 6 ELSE NULL END)) = [glclassifications.b].[code]))
group by
[glaccountsbulk].[code],
[budgets].[glaccountcode],
[glaccountclassificationmappings].[glaccountcode] ,
[reportingbalance].[glaccountcode] ,
[glaccountsbulk].[division] ,
[budgets].[division] ,
[glaccountclassificationmappings].[division] ,
[glclassifications.a].[division] ,
[glclassifications.b].[division] ,
[reportingbalance].[division] ,
[glaccountsbulk].[divisionname] ,
[budgets].[divisionname] ,
[budgets].[reportingyear],
[reportingbalance].[reportingyear] ,
[budgets].[reportingperiod] ,
[reportingbalance].[reportingperiod] ,
[budgets].[rowid$] ,
[glaccountclassificationmappings].[rowid$] ,
[glclassifications.b].[rowid$] ,
[glclassifications.a].[rowid$] ,
[reportingbalance].[rowid$] ,
[glaccountsbulk].[rowid$] ,
[reportingbalance].[balancetype] ,
[glaccountsbulk].[balancetype] ,
[budgets].[budgetscenariodescription] ,
[glaccountclassificationmappings].[classification] ,
[glaccountclassificationmappings].[classificationcode] ,
[glaccountclassificationmappings].[classificationdescription] ,
[glclassifications.b].[code] ,
[glclassifications.a].[code] ,
[glclassifications.b].[description] ,
[glclassifications.a].[description] ,
[glaccountsbulk].[description] ,
[glaccountclassificationmappings].[glaccount] ,
[reportingbalance].[glaccount] ,
[budgets].[glaccount] ,
[glaccountclassificationmappings].[glaccountdescription] ,
[reportingbalance].[glaccountdescription] ,
[budgets].[glaccountdescription] ,
[glaccountclassificationmappings].[glschemecode]
Bron Budgets en ReportingBalance zien er zo uit:
budgets
reportingbalance
