TSQL -> Update calculated value using indexed view

The mission is to calculate quantity of items sold in 1999 and add the resulting number into Items table. In order to execute the task, we first add column SaleQnt1999 to the Items table, than create view vw_solditemsqnt1999 based on sql statement that calculates quantities. Than put unique index on itemcode column of the view. At the end we update SaleQnt1999 column using JOIN between Items table and the view.

/* create new column in Items table */
ALTER TABLE dbo.items ADD SaleQnt1999 INT;

/* create view that calcualates sales quantities for year 1999 */
CREATE VIEW vw_solditemsqnt1999 WITH SCHEMABINDING AS
SELECT
a.itemcode, SUM(isNull(a.qnt,0)) AS totqnt,
COUNT_BIG(*) AS cb
FROM dbo.InvLines AS a INNER JOIN dbo.Invoice AS b
ON a.dockey = b.dockey
WHERE Year(b.docdate)=1999
GROUP BY a.itemcode
ORDER BY a.itemcode;

/* SCHEMABINDING prevents database objects, that are mentioned in the view definition, from being changed and anables creation of the CLUSTERED INDEX on one of it's columns */

/* create index on itemcode column */
CREATE UNIQUE CLUSTERED INDEX indx_1
ON vw_solditemsqnt1999 (itemcode);

/* update column SaleQnt1999 using calculated quantity from the view */
UPDATE a SET a.SaleQnt1999 = b.totqnt
FROM dbo.items AS a INNER JOIN vw_solditemsqnt1999 AS b
ON a.itemcode = b.itemcode;

SELECT itemcode, SaleQnt1999 FROM dbo.items ORDER BY itemcode;


The result will be like this:
itemcodeSaleQnt1999
0036876116
5004097NULL
5003502120
......


More reading about Designing Indexed Views from microsoft technet



sqlexamples.info