By PJW


2014-01-13 14:33:20 8 Comments

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 -

enter image description here

Then what I want is -

enter image description here

And even better (if it is possible) -

enter image description here

with the missing months filled in

Related Questions

Sponsored Content

48 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

35 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

41 Answered Questions

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

32 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2854041 View
  • 1950 Score
  • 32 Answer
  • Tags:   sql duplicates

30 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

13 Answered Questions

[SOLVED] MySQL Query GROUP BY day / month / year

43 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

47 Answered Questions

Sponsored Content