|
|
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:
| itemcode | SaleQnt1999 |
| 0036876 | 116 |
| 5004097 | NULL |
| 5003502 | 120 |
| ... | ... |
|