/*
I hope that everybody had a good Christmas. I apologize for the lack of new puzzles but I have been using up the remains of my Holiday. Since it is the New Year I thought we should start it off with a nice easy but fun puzzle.
Recently I have been struggling writing some internal Red Gate reports and while trying to get Reporting Services to draw bar graphs I thought wouldn’t it be much easier if we could just use SQL Server to generate all these reports internally. So this week’s puzzle is simple. Given some sales results generate an ASCII art bar chart representing the results. Whereas previous puzzles had either a right or a wrong answer with this one I would like to encourage a bit of competition so let’s see who can product the best looking ASCII art chart.
Since space is limited I am only going to give people 4 columns to put on the chart. One column for each quarter of the year. The sales data is in a table cunningly labeled @SalesResults. This table has a column for which quarter of the year the results belong to which is a number from 1 to 4. There is also an AmountSold column for the amount of things sold in that quarter. I am also giving you a helper table called Sequence which just contains the numbers 0 to 100. A sequence table is the Swiss Army Knife of all database tables and is a very useful thing in any database. As usual with these puzzles you should be done with one top level select statement though you can use as many derived tables subselects and unions as you like. Defiantly no DDL, variables or Transact SQL Control statements like IF and WHILE. My solution is as follows:
20 |
|
|
|
|
| ----
| | |
| | |
| ---- | | ----
| | | | | | |
10 | | | | | | |
| | | ---- | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
0 | | | | | | | | |
---------------------------------------------
Q1 Q2 Q3 Q4
Have fun.
Lionel
*/
SET NOCOUNT ON
DECLARE @Sequence TABLE
(
i INT NOT NULL
)
DECLARE @i INT
SET @i=0
WHILE(@i < 100)
BEGIN
INSERT INTO @Sequence(i) VALUES (@i)
SET @i = @i + 1
END
DECLARE @SalesResults TABLE
(
[Quarter] INT PRIMARY KEY CHECK([Quarter] BETWEEN 1 AND 4),
[AmountSold] INT NOT NULL
)
INSERT INTO @SalesResults([Quarter], [AmountSold])
SELECT 1, 12
INSERT INTO @SalesResults([Quarter], [AmountSold])
SELECT 2, 9
INSERT INTO @SalesResults([Quarter], [AmountSold])
SELECT 3, 15
INSERT INTO @SalesResults([Quarter], [AmountSold])
SELECT 4, 12
SET NOCOUNT OFF