5/31/2013

Select * from subquery

SELECT t.*, a+b AS total_sum FROM(SELECT SUM(column1)AS a, SUM(column2)AS b FROMtable) t

update cust
set CUSTSALESGRADE = SALESGRADE,CUSTGPGRADE=GPGRADE
from
(select INV_CUSTID,
      (CASE
            WHEN SUM(INV_SUBTOTAL)  < 5000                   THEN 'F'
            WHEN SUM(INV_SUBTOTAL) BETWEEN 5000 AND 15000    THEN 'E'
            WHEN SUM(INV_SUBTOTAL) BETWEEN 15000  AND 50000  THEN 'D'
            WHEN SUM(INV_SUBTOTAL) BETWEEN 50000  AND 100000 THEN 'C'
            WHEN SUM(INV_SUBTOTAL) BETWEEN 100000 AND 200000 THEN 'B'
            WHEN SUM(INV_SUBTOTAL) > 200000 THEN 'A'
      END) SALESGRADE,  
      (CASE
            WHEN SUM(INV_SUBTOTAL)-SUM(INV_COST)  < 5000                   THEN 'F'
            WHEN SUM(INV_SUBTOTAL)-SUM(INV_COST) BETWEEN 5000 AND 15000    THEN 'E'
            WHEN SUM(INV_SUBTOTAL)-SUM(INV_COST) BETWEEN 15000  AND 50000  THEN 'D'
            WHEN SUM(INV_SUBTOTAL)-SUM(INV_COST) BETWEEN 50000  AND 100000 THEN 'C'
            WHEN SUM(INV_SUBTOTAL)-SUM(INV_COST) BETWEEN 100000 AND 200000 THEN 'B'
            WHEN SUM(INV_SUBTOTAL)-SUM(INV_COST) > 200000 THEN 'A'
      END)  as GPGRADE
      from INV
      where INV_DATE>DATEADD(year,-1,(dateadd(month, datediff(month, -1, getdate()) - 1, -1) + 1))
      GROUP BY INV_CUSTID) sales
 inner join cust on sales.inv_custid = custid