A Scenario to Ponder #4
This scenario doesn't (and hopefully shouldn't) relate to any requirements, you will ever see, to be implemented at the database tier. But just for the fun of solving it, I am putting it here.
Please have a look at the image. Its a sine curve, that is generated from a query selecting 2 columns (both computed, may be from number table). One column gives the angle in degrees and the other column is the corresponding sine value, graphically represented.
The result set should have values of the angle from 0 to 360 in steps of 5. And lets say the scale of the graph is 50 (meaning you will have 50 '+' for the sine value = 1). The output given below should give a better insight.
Can anyone come up with the query? The solution can be in SQL Server 2000 or 2005.
Posts
5 comments:
Easy, mate (assume I have a table - nums (num int), populated to at least 361)
select
right(space(3) + cast(num-1 as varchar(3)),3),
right(space(50) + case when sin(radians(num-1.)) < 0 then replicate('+',50 * -sin(radians(num-1.))) else '' end,50)
+ '|' +
left(case when sin(radians(num-1.)) > 0 then replicate('+',50 * sin(radians(num-1.))) else '' end + space(50),50)
from dbo.nums
where (num-1) % 5 = 0
and num <= 361
Thats absolutely right Rob :)
And you can get the Cosine curve too by just changing the function.
Good show!!
-Omni
Here is my solution:
Fairly the same idea as Rob, but I don't use the nums table, I use CTEs to generate the numbers.
-- Sine Graph
with SineGraph as
(
select 0 as val,0 as sinval
union all
select val + 5,cast(sin(radians(val + 5.0))*50 as int) from SineGraph where val < 360
)
select cast(val as char(3)) as degrees,reverse(cast(isnull(replicate('+',-sinval),'') as char(50))) + '|' + isnull(replicate('+',sinval),'') as Graph from SineGraph;
And here are the queries to generate the Cosine and Tan graphs
-- Cos Graph
with CosGraph as
(
select 0 as val,50 as cosval
union all
select val + 5,cast(cos(radians(val + 5.0))*50 as int) from CosGraph where val < 360
)
select cast(val as char(3)) as degrees,reverse(cast(isnull(replicate('+',-cosval),'') as char(50))) + '|' + isnull(replicate('+',cosval),'') as Graph from CosGraph;
-- Tan Graph
with TanGraph as
(
select 0 as val,cast(0 as bigint) as tanval
union all
select val + 5,cast(ceiling(tan(radians(val + 5.0))*5) as bigint)from TanGraph where val < 360
)
select cast(val as char(3)) as degrees,case when (val + 90) %180 = 0 then replicate('-',123) else reverse(cast(isnull(replicate('+',-tanval),'') as char(60))) + '|' + isnull(replicate('+',tanval),'') end as Graph from TanGraph
Thats really amazing ...
Thanks
Prateek Agrawal
sir any one please tell me how to insert records automatically upto number which we like
in above example create a nums table and it contain 361 records
without typing 361 times can i insert in single time
is it possible
help me
--------------------
maild id: mganeshm143@gmail.com
=------------------
thanks in advance
Post a Comment