Блог

Я хочу, чтобы результирующая таблица была похожа на таблицу примеров, которую я предоставил, даже если единственным предоставленным месяцем было 5, я хочу, чтобы в таблице отображались все 12 месяцев, даже если сумма должна была быть равна 0 для других месяцев.

Это запрос, который я использую,

 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 (пример, не полная таблица),

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

Таблица продаж (пример, не полная таблица),

  -- ------ --------- ------- -------- 
|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       |
 -- ------ --------- ------- -------- 
 

Я получаю этот результат,
http://i61.tinypic.com/xnumpz.png

Проблема с этим результатом заключается в дублировании и в том, что эти 3 значения (дублирующие) должны отображаться только на 4, 5, 6

Вот так,

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

В то время как с других месяцев будет иметь значение 0.

В итоге я использовал Pivot, который великолепно решил мою проблему, это для тех, у кого такая же проблема, оглядывающаяся вокруг.

 Declare @Year As Int
Set @Year = 2014

Select [Employee ID], Name, @Year As [Year],
IsNull(January, 0)  As January,  IsNull(February, 0)  As February,  IsNull(March, 0)      As March,
IsNull(April, 0)    As April,    IsNull(May, 0)       As May,       IsNull(June, 0)       As June,
IsNull(July, 0)     As July,     IsNull(August, 0)    As August,    IsNull(September, 0)  As September,
IsNull(October, 0)  As October,  IsNull(November, 0)  As November,  IsNull(December, 0)   As December
From 
(Select 
    S.EmployeeID As [Employee ID],
    E.Name,
    YEAR([Date]) As [Year],
    DATENAME(MONTH, [Date]) As [Month], 
    IsNull(SUM(Amount), 0) As TotalAmount
    From Sales S Left Outer Join Employee E
    On S.EmployeeID = E.EmployeeID
    Group By 
    S.EmployeeID,
    E.Name, 
    YEAR([Date]),
    DATENAME(MONTH, [Date])) As MonthlySale
Pivot(SUM(TotalAmount)   
    For Month In ([January], [February], [March],
                  [April],   [May],      [June],
                  [July],    [August],   [September],
                  [October], [November], [December])) As MyPivot