Tuesday, 20 August 2013

Inserting zero-valued rows

Inserting zero-valued rows

I have the following structure. All I want to achieve is that if for some
reason one of the categories does not exist then rather than there being
no row for that category there should be a row with zeros for x/y/z.
The following seems a bit convoluted - what is the correct way to achieve
this?
TRUNCATE TABLE dbo.xxx;
INSERT INTO dbo.xxx
values
('cat_1',0.0,0.0,0.0),
('cat_2',0.0,0.0,0.0),
('cat_3',0.0,0.0,0.0),
('cat_4',0.0,0.0,0.0);
--:: category 1
INSERT INTO dbo.xxx
SELECT Category = 'cat_1',
x = SUM(x),
y = SUM(y),
z = SUM(z)
FROM dbo.yyy x
WHERE DateKey >= period1;
--:: category 2
INSERT INTO dbo.xxx
SELECT Category = 'cat_2',
x = SUM(x),
y = SUM(y),
z = SUM(z)
FROM dbo.yyy x
WHERE DateKey >= period2;
--:: category 3
INSERT INTO dbo.xxx
SELECT Category = 'cat_3',
x = SUM(x),
y = SUM(y),
z = SUM(z)
FROM dbo.yyy x
WHERE DateKey >= period3;
--:: category 4
INSERT INTO dbo.xxx
SELECT Category = 'cat_4',
x = SUM(x),
y = SUM(y),
z = SUM(z)
FROM dbo.yyy x
WHERE DateKey >= period4;
SELECT Category,
x = SUM(x),
y = SUM(y),
z = SUM(z)
INTO #temp
FROM dbo.xxx
GROUP BY Category;
TRUNCATE TABLE dbo.xxx;
INSERT INTO dbo.xxx
SELECT * FROM #temp;

No comments:

Post a Comment