|
|
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
declare TABLE variable @tb, after doing that
run Query that calculates quantities and put it's results into this variable.
At the end we update SaleQnt1999 column using data from the virtual table.
/* create new column in Items table */
ALTER TABLE dbo.items ADD
SaleQnt1999 INT;
/* declare table variable */
DECLARE @tb Table
(itemcode VARCHAR(25) Primary Key,
totqnt INT);
/* calcualate sales quantity for year 1999,
insert results into table variable */
INSERT INTO @tb (itemcode, totqnt)
SELECT a.itemcode, SUM(a.qnt) AS totqnt
FROM invlines AS a INNER JOIN invoice AS b
ON a.dockey = b.dockey
WHERE Year(b.docdate)=1999
GROUP BY a.itemcode
ORDER BY a.itemcode;
/* update column SaleQnt1999 using calculated
quantity from the virtual table */
UPDATE a
SET a.SaleQnt1999 = b.totqnt
FROM dbo.items AS a INNER JOIN @tb 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 |
| ... | ... |
|