http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8
/* the way shown in that blog */
SELECT CompanyName, SUM((UnitPrice*Quantity)) As TotalAmt,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-1)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-2)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-3)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-4)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-5)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-6)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-7)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-8)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-9)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-10)))),
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-11)))),
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-12))))
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
Group By Customers.CompanyName
GO
/* MY WAY -- takes 1 less step compared to previous in Query Execution Plan */
SELECT CompanyName, SUM((UnitPrice*Quantity)) As TotalAmt,
SUM( CASE DatePart(mm,OrderDate) WHEN 1 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 2 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 3 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 4 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 5 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 6 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 7 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 8 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 9 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 10 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 11 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 12 THEN (UnitPrice*Quantity) ELSE 0 END )
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
Group By Customers.CompanyName
GO
Trackback