SQL Server Duplication and correct assignment of Joins

Vote count:

0

I want the resulting table to be like the example Table I have provided, even though the only month provided was 5, I want the table to display all the 12 months even if the Amount was to be 0 for the other months.

This is the query I'm using,

Select Distinct T1.EmployeeID, T1.Name, T.MonthID, T1.TotalAmount, T1.TotalQuantity From 
(Select 
    S.EmployeeID,
    E.Name,
    YEAR(MAX([Date])) As [Year],
    MONTH(MAX([Date])) As [Montht],
    IsNull(SUM(Amount), 0) As TotalAmount,
    IsNull(SUM(Quantity), 0) As TotalQuantity
From Sales S Inner Join Employee E On E.EmployeeID = S.EmployeeID 
Group By 
    S.EmployeeID,
    E.Name,
    YEAR([Date]),
    MONTH([Date])) As T1 
    Inner Join
    (Select 'January' as Month , 1 as MonthID
    UNION select 'February' as Month , 2 as MonthID
    UNION select 'March' as Month , 3 as MonthID
    UNION select 'April' as Month , 4 as MonthID
    UNION select 'May' as Month , 5 as MonthID
    UNION select 'June' as Month , 6 as MonthID
    UNION select 'July' as Month , 7 as MonthID
    UNION select 'August' as Month , 8 as MonthID
    UNION select 'September' as Month , 9 as MonthID
    UNION select 'October' as Month , 10 as MonthID
    UNION select 'November' as Month , 11 as MonthID
    UNION select 'December' as Month , 12 as MonthID) As T 
    On T1.Montht <> T.MonthID
    Where T.MonthID Not In
(Select 
    MONTH(MAX([Date])) As [Montht]
    From Sales S Inner Join Employee E 
    On E.EmployeeID = S.EmployeeID And E.EmployeeID = 1  
    Group By S.EmployeeID,
    E.Name,
    YEAR([Date]), 
    MONTH([Date]))
Order By T1.Name

---

Employee Table (Example, not full table),

+--+--+------+
|ID|  Name   |
+--+---------+
|1 |John Doe |
+--+---------+
|2 |Jane Doe |
+--+---------+

Sales Table (Example, not full table),

+--+------+---------+-------+--------+
|ID|SaleID| Date    |Amount |Quantity|
+--+------+---------+-------+--------+
|1 |  1   |5-14-2014|300    |12      |
+--+------+---------+-------+--------+
|1 |  2   |5-16-2014|600    |4       |
+--+------+---------+-------+--------+
|2 |  3   |5-14-2014|452    |10      |
+--+------+---------+-------+--------+
|2 |  4   |5-16-2014|356    |2       |
+--+------+---------+-------+--------+

I'm getting this result, http://ift.tt/1r9Qg2p

The problem with that result is the duplication, and that those 3 values (The ones duplicating) are only suppose to be displayed on the 4, 5, 6

Like so,

+-------+-----------+
|MonthID|TotalAmount|
+-------+-----------+
|4      |757.00     |
+-------+-----------+
|5      |834.00     |
+-------+-----------+
|6      |880.00     |
+-------+-----------+

While since the other months would have a value of 0.

asked 40 secs ago

Prosperity

1