I am attempting to create a new view based off of vdvBudgetAnalysis that does not exclude any actual credit/debit history. This history is pulled from tglAcctHist. The native view as it was written does not include rows showing actual credit/debit history values when there is no corresponding entry in tglBudget. The corresponding entry in tglBudget can be “0.00,” but there must at least be something in tglBudget. This is what I am trying to change. I want to see records showing credits & debits EVEN IF THERE IS NO CORRESPONDING ENTRY AT ALL IN tglBudget for the given CompanyID/GL#/Year/Period record.
The native view is:
************************************************************************************************************************************************************
SELECT dbo.tglAccount.CompanyID, dbo.tglAcctCategory.Description AS AcctCategoryDesc, dbo.tglAccount.Description AS AcctDesc,
dbo.tglAcctGroup.Description AS AcctGroupDesc, dbo.tglAcctGroup.AcctGroupID, dbo.tglAcctType.Description AS AcctTypeDesc, dbo.tglAcctType.AcctTypeID,
dbo.tglBudget.BudgetAmt, dbo.tglBudgetType.Description AS BudgetTypeDescription, dbo.tglBudgetType.BudgetTypeID,
dbo.tglBudget.BudgetAmt - COALESCE (dbo.tglAcctHist.DebitAmt, 0) + COALESCE (dbo.tglAcctHist.CreditAmt, 0) AS BudgetVar,
CASE dbo.tglNaturalAcct.CashFlowCode WHEN 1 THEN 'Non Cash' WHEN 2 THEN 'Operating' WHEN 3 THEN 'Investing' WHEN 4 THEN 'Financing' WHEN 5 THEN 'Cash'
END AS CashFlowCodeAsText, COALESCE (dbo.tglAcctHist.CreditAmt, 0) AS CreditAmount, COALESCE (dbo.tglAcctHist.DebitAmt, 0) AS DebitAmount,
dbo.tglFiscalPeriod.FiscPer, dbo.tglFiscalPeriod.FiscYear, dbo.tglFiscalPeriod.FiscYearPer, dbo.vFormattedGLAcct.FormattedGLAcctNo AS GLAccount,
COALESCE (dbo.tglAcctHist.DebitAmt, 0) - COALESCE (dbo.tglAcctHist.CreditAmt, 0) AS NetAmount, dbo.vFormattedGLAcct.Segment1,
dbo.vFormattedGLAcct.Segment1Desc, dbo.vFormattedGLAcct.Segment2, dbo.vFormattedGLAcct.Segment2Desc, dbo.vFormattedGLAcct.Segment3,
dbo.vFormattedGLAcct.Segment3Desc, dbo.vFormattedGLAcct.Segment4, dbo.vFormattedGLAcct.Segment15Desc, dbo.vFormattedGLAcct.Segment15,
dbo.vFormattedGLAcct.Segment14Desc, dbo.vFormattedGLAcct.Segment14, dbo.vFormattedGLAcct.Segment13Desc, dbo.vFormattedGLAcct.Segment13,
dbo.vFormattedGLAcct.Segment12Desc, dbo.vFormattedGLAcct.Segment12, dbo.vFormattedGLAcct.Segment11Desc, dbo.vFormattedGLAcct.Segment11,
dbo.vFormattedGLAcct.Segment10Desc, dbo.vFormattedGLAcct.Segment10, dbo.vFormattedGLAcct.Segment9Desc, dbo.vFormattedGLAcct.Segment9,
dbo.vFormattedGLAcct.Segment8Desc, dbo.vFormattedGLAcct.Segment8, dbo.vFormattedGLAcct.Segment7Desc, dbo.vFormattedGLAcct.Segment7,
dbo.vFormattedGLAcct.Segment6Desc, dbo.vFormattedGLAcct.Segment6, dbo.vFormattedGLAcct.Segment5Desc, dbo.vFormattedGLAcct.Segment5,
dbo.vFormattedGLAcct.Segment4Desc, dbo.tglAccount.UserFld1 AS GLAcctUserFld1, dbo.tglAccount.UserFld2 AS GLAcctUserFld2,
dbo.tglAccount.UserFld3 AS GLAcctUserFld3, dbo.tglAccount.UserFld4 AS GLAcctUserFld4, dbo.tglNaturalAcct.CashFlowCode, dbo.tglAccount.GLAcctKey,
COALESCE (dbo.tglAcctHist.StatQty, 0) AS StatQty, dbo.tglAccount.StatUnit, dbo.tglAccount.GLAcctNo, dbo.tglAccount.ExtCmnt AS Comment
FROM dbo.tglBudget INNER JOIN
dbo.tglBudgetType ON dbo.tglBudget.BudgetTypeKey = dbo.tglBudgetType.BudgetTypeKey INNER JOIN
dbo.tglFiscalPeriod ON dbo.tglBudgetType.CompanyID = dbo.tglFiscalPeriod.CompanyID AND dbo.tglFiscalPeriod.FiscPer = dbo.tglBudget.FiscPer AND
dbo.tglFiscalPeriod.FiscYear = dbo.tglBudget.FiscYear INNER JOIN
dbo.tglAccount ON dbo.tglBudget.GLAcctKey = dbo.tglAccount.GLAcctKey INNER JOIN
dbo.tglNaturalAcct ON dbo.tglAccount.NaturalAcctKey = dbo.tglNaturalAcct.NaturalAcctKey INNER JOIN
dbo.tglAcctType ON dbo.tglNaturalAcct.AcctTypeKey = dbo.tglAcctType.AcctTypeKey AND dbo.tglAcctType.AcctTypeID > 400 AND
dbo.tglAcctType.AcctTypeID <> 901 INNER JOIN
dbo.tglAcctCategory ON dbo.tglAcctCategory.AcctCategoryKey = dbo.tglAcctType.AcctCategoryKey INNER JOIN
dbo.vFormattedGLAcct ON dbo.tglAccount.GLAcctKey = dbo.vFormattedGLAcct.GLAcctKey INNER JOIN
dbo.tglAcctGroup ON dbo.tglAcctGroup.AcctGroupKey = dbo.tglNaturalAcct.AcctGroupKey LEFT OUTER JOIN
dbo.tglAcctHist ON dbo.tglBudget.FiscYear = dbo.tglAcctHist.FiscYear AND dbo.tglBudget.FiscPer = dbo.tglAcctHist.FiscPer AND
dbo.tglBudget.GLAcctKey = dbo.tglAcctHist.GLAcctKey
************************************************************************************************************************************************************
I actually don't need most of the fields in the native view, so I've stripped it down to the bare essentials. I have been unable to come up with the magic JOIN clause to make it work.
SELECT tglAccount.CompanyID, tglAccount.GLAcctNo, tglAcctHist.FiscYear, tglAcctHist.FiscPer, tglAcctHist.CreditAmt, tglAcctHist.DebitAmt, tglBudget.BudgetAmt
FROM *** JOIN clause that will return one row for each CompanyID/GL#/Year/Period combination showing the credit, debit, and budget values on a single row, BUT WHICH DOES NOT OMIT ENTRIES WHEN NO BUDGET VALUE EXISTS like vdvBudgetAnalysis does. ***
The JOIN clauses that I have tried have all resulted in either too few or way too many records. I suspect that what I have been attempting to do is not possible. Can any SQL developers confirm this?
