-- prj. 3 + quiz 5
-- ===============

use AdventureWorks

-- 1.  months totals
SELECT TOP (100) PERCENT 
  c.CustomerID, MONTH(o.OrderDate) AS OrderMonth, SUM(s.LineTotal)
    AS LineTotal

  FROM Sales.Customer AS c 
		
    INNER JOIN Sales.SalesOrderHeader AS o 
      ON c.CustomerID = o.CustomerID

    INNER JOIN Sales.SalesOrderDetail s
      ON o.SalesOrderID = s.SalesOrderID

  GROUP BY MONTH(o.OrderDate), c.CustomerID
  ORDER BY c.CustomerID, OrderMonth

-- 2. orders totals
SELECT TOP (100) PERCENT 
  c.CustomerID, MONTH(o.OrderDate) AS OrderMonth, COUNT(c.CustomerID)
    AS OrderCountByMonth

  FROM Sales.Customer c

    INNER JOIN Sales.SalesOrderHeader o
      ON c.CustomerID = o.CustomerID

  GROUP BY MONTH(o.OrderDate), c.CustomerID
  ORDER BY  1,2,3

--  a tmp table tt
Declare @tt TABLE (
  OrderYear int,
  OrderMonth int,
  CustomerID int,
  TotAmt money,  
  OrdersTot int
  )

--  load tt
insert into @tt
Select a.OrderYear, a.OrderMonth, a.CustomerID, A.TotAmt, b.OrdersTot
from (
  SELECT TOP (100) PERCENT  YEAR(o.OrderDate)
    AS OrderYear, MONTH(o.OrderDate) AS OrderMonth,
    c.CustomerID,  SUM(o.TotalDue) AS TotAmt

    FROM Sales.Customer AS c 

      INNER JOIN Sales.SalesOrderHeader AS o
        ON c.CustomerID = o.CustomerID
    GROUP BY MONTH(o.OrderDate), c.CustomerID, YEAR(o.OrderDate)
	) a

  LEFT OUTER JOIN (
    SELECT TOP (100) PERCENT YEAR(o.OrderDate) AS OrderYear,
      MONTH(o.OrderDate) AS OrderMonth,
      count(*) as OrdersTot, c.CustomerID

      FROM Sales.Customer AS c 

      INNER JOIN Sales.SalesOrderHeader AS o 
        ON c.CustomerID = o.CustomerID
    GROUP BY MONTH(o.OrderDate), c.CustomerID, YEAR(o.OrderDate)
  ) b
  on a.customerID = b.customerID

--  element, attibute, counts

select OrderYear, CustomerID, [1] as Jan, [2] as Feb, [3] as Mar,
                              [4] as Apr, [5] as May, [6] as Jun,
                              [7] As Jul, [8] as Aug, [9] as Sep,
                              [10] as Oct, [11] as Nov, [12] as Dec
FROM 
( Select CustomerID, OrderMonth, OrderYear, OrdersTot From @tt ) p
PIVOT
(
  count( OrdersTot)
  for OrderMonth in 
  ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] )
) as pvt
for xml path

--  sums 
select OrderYear, CustomerID, [1] as Jan, [2] as Feb, [3] as Mar,
                              [4] as Apr, [5] as May, [6] as Jun,
                              [7] As Jul, [8] as Aug, [9] as Sep,
                              [10] as Oct, [11] as Nov, [12] as Dec
FROM 
( Select CustomerID, OrderMonth, OrderYear,TotAmt From @tt ) p
PIVOT
(
  sum( TotAmt)
  for OrderMonth in
  ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] )
) as pvt
for xml path