-- creating the test table CREATE TABLE Table1( ID int NOT NULL , [Year] int NOT NULL , Product varchar(30) NOT NULL , Price decimal(13,2) NOT NULL , Quantity decimal(13,3) NOT NULL) -- dropping the test table --DROP TABLE Table1 -- deleting the data --TRUNCATE TABLE Table1
Usually I'm commenting DROP and TRUNCATES so I can avoid the modification of data by mistake.
And now the loop, with the help of RAND and modulo the values can be varied:
-- inserting the data DECLARE @index int SET @index = 1 WHILE @index < 99 BEGIN INSERT Table1(ID, [Year], Product, Price, Quantity) VALUES (@index, 2000 + (@index % 4), 'Product ' + Cast(@index%10 as char(3)), 10 * RAND(), 100 * RAND()) SET @index = @index + 1 END --testing the output SELECT * FROM Table1 -- using the data SELECT [Year], Product, SUM(Price * Quantity) Amount FROM Table1 GROUP BY [Year], Product ORDER BY [Year], Product
No comments:
Post a Comment