I have a SQL Server database (2012 express) with many tables. I have produced three different VIEWS based on different combinations of the underlying tables. Each of these views consists of three columns, Year, Month & Total The Total column in each of the 3 Views is of a different measure. What I want to be able to do is to combine the three Totals into a single View
I have attempted this with the following script -
SELECT b.[Year], b.[Month], b.Fees AS [Billing], f.Estimate AS [Estimate], w.Fees AS [WIP] FROM MonthlyBillingTotals AS b FULL JOIN MonthlyFeeEstimates AS f ON (b.[Year] = f.[Year] AND b.[Month] = f.[Month]) FULL JOIN MonthlyInstructionsWIP AS w ON (b.[Year] = w.[Year] AND b.[Month] = w.[Month]) ORDER BY b.[Year], b.[Month]
Originally I tried INNER JOINS but of course unless the Year / Month combo existed in the first view (MonthlyBillingTotals) then it did not appear in the combined query. I therefore tried FULL JOINS, but the problem here is that I get some NULLS in the Year and Month columns, when they do not exist in the first view (MonthlyBillingTotals).
If the data in the three Views is as follows -
Then what I want is -
And even better (if it is possible) -
with the missing months filled in