It is time for us to get 'graphical' and this challenge is to draw a graph using TSQL.
Izenda Reports is the official sponsor of this challenge. Izenda Reports and Dashboards is the easiest and fastest way to create ad hoc reports from SQL Server data bases.
Sample Data
Seq Data
----------- -----------
1 2
2 3
3 4
4 4
5 5
6 4
7 3
8 4
9 4
10 4
11 3
12 2
13 2
14 3
15 3
16 4
17 4
18 5
Here is how your graph should look like.
5|
4| _/\ __ _/
3| / \/ \ _/
2|/ \_/
1|
0|__________________
0123456789012345678
Scripts
Use the following script to generate the sample data
SET NOCOUNT ON
IF OBJECT_ID('TC36_Data','U') IS NOT NULL DROP TABLE TC36_Data
GO
CREATE TABLE TC36_Data (
Seq INT,
Data INT
)
INSERT INTO TC36_Data(Seq, Data) SELECT 1, 2
INSERT INTO TC36_Data(Seq, Data) SELECT 2, 3
INSERT INTO TC36_Data(Seq, Data) SELECT 3, 4
INSERT INTO TC36_Data(Seq, Data) SELECT 4, 4
INSERT INTO TC36_Data(Seq, Data) SELECT 5, 5
INSERT INTO TC36_Data(Seq, Data) SELECT 6, 4
INSERT INTO TC36_Data(Seq, Data) SELECT 7, 3
INSERT INTO TC36_Data(Seq, Data) SELECT 8, 4
INSERT INTO TC36_Data(Seq, Data) SELECT 9, 4
INSERT INTO TC36_Data(Seq, Data) SELECT 10, 4
INSERT INTO TC36_Data(Seq, Data) SELECT 11, 3
INSERT INTO TC36_Data(Seq, Data) SELECT 12, 2
INSERT INTO TC36_Data(Seq, Data) SELECT 13, 2
INSERT INTO TC36_Data(Seq, Data) SELECT 14, 3
INSERT INTO TC36_Data(Seq, Data) SELECT 15, 3
INSERT INTO TC36_Data(Seq, Data) SELECT 16, 4
INSERT INTO TC36_Data(Seq, Data) SELECT 17, 4
INSERT INTO TC36_Data(Seq, Data) SELECT 18, 5
SELECT * FROM TC36_Data
Notes
- The "seq" column controls the flow of the graph. You should process the points in that order.
- Value of the "data" column should be used to build your graph representation. The graph could start with any value between 0 and 5.
- The difference between two contiguous readings will only ever be –1, 0 or 1
- The highest value the "data" column can hold is 5 and lowest value is 0
- There will be no upper limit on the number of rows in the source table
- There will be at least 2 rows in the source table
- The final result should consist of 7 rows of output consisting of a single VARCHAR column
- The solution should be a single query that starts with a "SELECT", "WITH" or ";WITH"
- If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your code.
- Read the Submission Guidelines and make sure that your solution follows them.
- Use this forum for any questions related to TSQL Challenge 36. You need to be a member of TSQL Challenge Group to be able to post questions. Click here to join the group if you are not already a member.
- Click here to submit your solution
| Izenda Reports and Dashboards can be integrated into an ISV’s existing application or utilized by any department using SQL Server that needs to provide users a secure, easy-to-use reporting tool.
Our customers are blown away at how quickly they can have Izenda Reports integrated into their applications. And their end-users are thrilled how easily they can create ad-hoc reports – without “ruining” the templates or breaking any security.
Click here to attend a free webinar or click here to download a trial. |
 |
About the author