-- 2007.12.25, Honda.

----------------------------------------------------------------------------
TRUNCATE TABLE T2

-- METHOD 1: BY INSERT, CREATE RECORDS FROM NOTHING.
INSERT INTO T2 (FTYPE, FCODE, FN1)
SELECT FTYPE, '*', SUM(FN1)
FROM T1
GROUP BY FTYPE 

----------------------------------------------------------------------------
-- METHOD 2: UPDATE SUM(), UPDATE RECORDS BY KEY.
-- TO DO: HOW TO UPDATE MULTIPLE FIELDS?
-- NOTE: DO NOT USE ALIAS FOR TABLE OR RESULT IN ERROR.
UPDATE T2
SET FN2 =
	(SELECT SUM(FN2)
	FROM T1
	WHERE T1.FTYPE=T2.FTYPE
	)
FROM T1, T2

----------------------------------------------------------------------------

-- clear 
update t1
set t1.FtopShiptype    = null
,   t1.FTopShiptypeAmt = null
from TBL_R0712_MemberMonth as t1


-- 取得(每人每月Shiptype最大金額)
update TBL_R0712_MemberMonth
set FTopShiptypeAmt = (
	select max(FOrdAmt)
	from TBL_R0712_MemberShiptypeMonth
	where TBL_R0712_MemberMonth.sa_ss =TBL_R0712_MemberShiptypeMonth.sa_ss
	and   TBL_R0712_MemberMonth.FYear =TBL_R0712_MemberShiptypeMonth.FYear
	and   TBL_R0712_MemberMonth.FMonth=TBL_R0712_MemberShiptypeMonth.FMonth
	)

-- 取得(最大金額對應的shiptype)
update t1
set t1.FTopShiptype  =t2.shiptype
from TBL_R0712_MemberMonth as t1
	inner join TBL_R0712_MemberShiptypeMonth as t2
		on  t1.sa_ss           = t2.sa_ss
		and t1.fyear           = t2.fyear
		and t1.fmonth          = t2.fmonth
		and t1.FTopShiptypeAmt = t2.FOrdAmt


----------------------------------------------------------------------------
-- 2007.10.09, with Alias:
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD +
         ( SELECT SUM(so.SubTotal) 
           FROM   Sales.SalesOrderHeader AS so
           WHERE  so.OrderDate =
                 (SELECT MAX(OrderDate)
				  FROM Sales.SalesOrderHeader AS so2
				  WHERE so2.SalesPersonID = so.SalesPersonID
                 )
             AND Sales.SalesPerson.SalesPersonID = so.SalesPersonID
           GROUP BY so.SalesPersonID
         )