|
|
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 TEMP table #temp_salesqnt1999, than
run Query that calculates quantities and put it's results into the TEMP table.
At the end we update SaleQnt1999 column using data from TEMP table.
/* create new column in Items table */
ALTER TABLE dbo.items ADD
SaleQnt1999 INT;
/* create TEMP table */
CREATE TABLE #temp_salesqnt1999
(itemcode nvarchar(25) NOT NULL,
totqnt int NULL);
/* calcualate sales quantity for year 1999 */
INSERT INTO #temp_salesqnt1999 (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;
/* create index on TEMP table to boost performance */
CREATE UNIQUE CLUSTERED INDEX indx_1
ON #temp_salesqnt1999 (itemcode);
/* update column SaleQnt1999 using calculated
quantity from TEMP table */
UPDATE a
SET a.SaleQnt1999 = b.totqnt
FROM dbo.items AS a INNER JOIN #temp_salesqnt1999 AS b
ON a.itemcode = b.itemcode;
/* drop TEMP table */
DROP TABLE #temp_salesqnt1999;
SELECT itemcode, SaleQnt1999
FROM dbo.items ORDER BY itemcode;
|
The result will be like this:
| itemcode | SaleQnt1999 |
| 0036876 | 116 |
| 5004097 | NULL |
| 5003502 | 120 |
| ... | ... |
|