| Author |
 Topic  |
|
|
ditch
Flowing Fount of Yak Knowledge
South Africa
1437 Posts |
 Posted - 03/14/2004 : 10:18:35
|
I was bored, so I tried it.
It's not perfect (The results panes fonts mess with it a bit).
Best viewed if your QA's font is set to one of the Courier fonts and the results are viewed as text rather than grid.
I still want to get it right without using any cursors at all.
I will attempt this later.
Maybe someone else can improve on this though.
Here it is:
--**********************************************************
CREATE PROCEDURE AnalogTime AS
SET NOCOUNT ON
CREATE TABLE #ClockTimeDeg(Radius INT, ClockTimeHRS DECIMAL(18, 3), ClockTimeMin INT, Deg DECIMAL(18, 8), XCoOrd DECIMAL(18, 8), YCoOrd DECIMAL(18, 8), DISPLAY VARCHAR(2));
DECLARE @Mins INT
DECLARE @Hrs DECIMAL(18, 3)
DECLARE @Deg DECIMAL(18,8)
DECLARE @XCoOrd INT
DECLARE @YCoOrd INT
DECLARE @CurXCoOrd INT
DECLARE @CurYCoOrd INT
DECLARE @PrevYCoOrd INT
DECLARE @Radius DECIMAL(18, 8)
DECLARE @InsString VARCHAR(1000)
DECLARE @Display VARCHAR(2)
DECLARE @CurrKey INT
DECLARE @PrevKey INT
DECLARE @CurrTime DATETIME
DECLARE @HrsVal DECIMAL(18, 8)
DECLARE @MinsVal INT
SET @CurrTime = GETDATE()
SET @MinsVal = DATEPART(MINUTE, @CurrTime)
SET @HrsVal = CAST(DATEPART(HOUR, @CurrTime) AS DECIMAL(18, 8)) + (CAST((@MinsVal / 12) AS DECIMAL(18, 2)) * 0.2)
IF @HrsVal >= 13
BEGIN
SET @HrsVal = @HrsVal - 12
END
SET @Radius = 1
WHILE @Radius <= 24
BEGIN
--******Store the x, y co-ords of the clocks hrs and minutes using trig functions
--******Clock Radius = 24
--****** The length of the Y, coords is halved because it seems like the results panel provides more height to each character than width.
SET @Deg = 90
SET @Hrs = 12
SET @Mins = 0
WHILE @Mins < 60
BEGIN
INSERT INTO #ClockTimeDeg
SELECT @Radius, @Hrs, @Mins, @Deg,
CAST(24 + (@Radius * (CASE WHEN TAN(RADIANS(@Deg)) = 0 THEN -24 ELSE ((SIN(RADIANS(@Deg))) / TAN(RADIANS(@Deg))) END)) + 0.499999999 AS INT),
(48 - CAST(24 + @Radius * (SIN(RADIANS(@Deg))) + 0.499999999 AS INT) / 2),
CASE WHEN (@HrsVal = @Hrs AND @Radius < 11) OR (@MinsVal = @Mins AND @Radius < 21) THEN '*'
ELSE CASE WHEN @Radius = 23 AND @Hrs = CAST(@Hrs as INT) THEN LTRIM(STR(@HRS, 2)) ELSE ' ' END END
SET @Hrs = @Hrs + 0.2
IF @Hrs = 13
BEGIN
SET @HRS = 1
END
SET @Mins = @Mins + 1
IF @Deg > 353
BEGIN
SET @Deg = 0
END
ELSE
BEGIN
SET @Deg = @Deg + 6
END
END
SET @Radius = @Radius + 1
END
DECLARE DispCur
CURSOR FOR
SELECT CASE WHEN XCoOrd <= 0 THEN 48 ELSE XCoOrd END, YCoOrd, Display
FROM #ClockTimeDeg
WHERE Display<> ' '
ORDER BY 2, 1 DESC
CREATE TABLE #Results(RowNo INT, AnalogClock VARCHAR(1000))
OPEN DispCur
FETCH NEXT FROM DispCur INTO @CurXCoOrd, @CurYCoOrd, @Display
SET @PrevYCoOrd = 0
SET @PrevKey = 0
SET @XCoOrd = 1
SET @InsString = ''
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CurYCoOrd <> @PrevYCoOrd
BEGIN
INSERT INTO #Results
SELECT @CurYCoOrd, @InsString
SET @InsString = ''
SET @PrevYCoOrd = @CurYCoOrd
SET @XCoOrd = 50
END
WHILE @XCoOrd > @CurXCoOrd
BEGIN
SET @InsString = @InsString + ' '
SET @XCoOrd = @XCoOrd - 1
END
SET @InsString = @InsString + @Display
SET @XCoOrd = @XCoOrd - 1
FETCH NEXT FROM DispCur INTO @CurXCoOrd, @CurYCoOrd, @Display
END
CLOSE DispCur
DEALLOCATE DispCur
INSERT INTO #Results
SELECT @CurYCoOrd, @InsString
SELECT AnalogClock
FROM #Results
ORDER BY RowNo
DROP TABLE #ClockTimeDeg
DROP TABLE #Results
GO
exec analogtime
--**************************************************
Any suggestions and improvements welcome.
Duane. |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 03/14/2004 : 10:33:40
|
ROFLMAO
That's classic.
Good one 
Damian |
 |
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 03/14/2004 : 12:54:39
|
| BRILLIANT!!! |
|
|
|
ditch
Flowing Fount of Yak Knowledge
South Africa
1437 Posts |
Posted - 03/15/2004 : 05:26:58
|
quote:
Originally posted by Merkin
ROFLMAO
Thanks Guys.
What does ROFLMAO Mean?
Duane. |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
Switzerland
413 Posts |
|
|
ditch
Flowing Fount of Yak Knowledge
South Africa
1437 Posts |
Posted - 03/15/2004 : 06:20:17
|
Thanks Frank
Duane. |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
Switzerland
413 Posts |
Posted - 03/15/2004 : 06:23:34
|
No problem!
What about offering this to Microsoft as future enhancement by putting it on the wish list?
--Frank
http://www.insidesql.de |
|
|
|
harshal_in
Aged Yak Warrior
India
633 Posts |
Posted - 03/15/2004 : 06:26:41
|
Simply GREAT!!
He is a fool for five minutes who asks , but who does not ask remains a fool for life! |
|
|
|
mohdowais
Sheikh of Yak Knowledge
United Arab Emirates
1456 Posts |
|
|
Frank Kalis
Constraint Violating Yak Guru
Switzerland
413 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 03/15/2004 : 22:25:31
|
The frontiers of SQL are best explored by those with time on their hands.
(What I mean is... I'm not sure myself. Go figure.) |
|
|
|
ditch
Flowing Fount of Yak Knowledge
South Africa
1437 Posts |
|
|
anolis
Starting Member
Norway
14 Posts |
Posted - 05/14/2004 : 14:09:08
|
Great!!
I've got a function that stores the current time in a table. When you create a job, it can do that every second. Anyone interested?
Anolis,
Developer,
MCAD for .NET |
|
|
|
Ratboy
Starting Member
2 Posts |
Posted - 06/08/2004 : 08:34:01
|
I just came across your post and was amazed to see that someone else out that was as bored as I was to create an analogue clock with the current time! If you're interested, here was my attempt:
/** Init variables **/
DECLARE @curX decimal(20, 10)
DECLARE @curY decimal(20, 10)
DECLARE @strLine varchar(100)
DECLARE @intDistance int
DECLARE @curA decimal(20, 10)
DECLARE @curB decimal(20, 10)
DECLARE @curC decimal(20, 10)
DECLARE @curHourHandLen decimal(9, 4)
DECLARE @curHourHandX1 decimal(20, 10)
DECLARE @curHourHandY1 decimal(20, 10)
DECLARE @curHourHandX2 decimal(20, 10)
DECLARE @curHourHandY2 decimal(20, 10)
DECLARE @curMinuteHandLen decimal(9, 4)
DECLARE @curMinuteHandX1 decimal(20, 10)
DECLARE @curMinuteHandY1 decimal(20, 10)
DECLARE @curMinuteHandX2 decimal(20, 10)
DECLARE @curMinuteHandY2 decimal(20, 10)
DECLARE @curAngle numeric(11, 10)
DECLARE @intQuarter smallint
DECLARE @curHour decimal(20, 10)
DECLARE @curDistanceFromCenter decimal(20, 10)
DECLARE @curCenterX decimal(20, 10)
DECLARE @curCenterY decimal(20, 10)
/** Setup variables **/
SET @curHourHandLen = 20
SET @curMinuteHandLen = 42
SET @curCenterX = 50
SET @curCenterY = 50
/** Work out the hand positions **/
-- They all start from the center
SET @curHourHandX1 = @curCenterX
SET @curHourHandY1 = @curCenterY
SET @curMinuteHandX1 = @curCenterX
SET @curMinuteHandY1 = @curCenterY
-- Work out how far round the clock the hour is and set the position from that
SET @curHour = DATEPART(hour, GETDATE())
IF @curHour > 12
SET @curHour = @curHour - 12
SET @curHour = @curHour + (CONVERT(numeric(11, 9), DATEPART(minute, GETDATE())) / 60)
SET @curAngle = (PI() * 2) * (CONVERT(numeric(11, 9), @curHour) / 12)
IF @curAngle >= 0 AND @curAngle <= PI() / 2
BEGIN
SET @intQuarter = 1
SET @curHourHandX2 = @curHourHandX1 + (@curHourHandLen * SIN(@curAngle))
SET @curHourHandY2 = @curHourHandY1 - (@curHourHandLen * COS(@curAngle))
END
ELSE IF @curAngle > PI() / 2 AND @curAngle <= PI()
BEGIN
SET @intQuarter = 2
SET @curAngle = PI() - @curAngle
SET @curHourHandX2 = @curHourHandX1 + (@curHourHandLen * SIN(@curAngle))
SET @curHourHandY2 = @curHourHandY1 + (@curHourHandLen * COS(@curAngle))
END
ELSE IF @curAngle > PI() AND @curAngle <= (PI() * 1.5)
BEGIN
SET @intQuarter = 3
SET @curAngle = @curAngle - PI()
SET @curHourHandX2 = @curHourHandX1 - (@curHourHandLen * SIN(@curAngle))
SET @curHourHandY2 = @curHourHandY1 + (@curHourHandLen * COS(@curAngle))
END
ELSE IF @curAngle > (PI() * 1.5) AND @curAngle <= (PI() * 2)
BEGIN
SET @intQuarter = 4
SET @curAngle = (PI() * 2) - @curAngle
SET @curHourHandX2 = @curHourHandX1 - (@curHourHandLen * SIN(@curAngle))
SET @curHourHandY2 = @curHourHandY1 - (@curHourHandLen * COS(@curAngle))
END
ELSE
BEGIN
PRINT 'Could not work out the hour hand''s quarter segment'
PRINT @curAngle
RETURN
END
-- Work out how far round the clock the minute is and set the position from that
SET @curAngle = (PI() * 2) * (CONVERT(numeric(11, 9), DATEPART(minute, GETDATE())) / 60)
IF @curAngle >= 0 AND @curAngle <= PI() / 2
BEGIN
SET @intQuarter = 1
SET @curMinuteHandX2 = @curMinuteHandX1 + (@curMinuteHandLen * SIN(@curAngle))
SET @curMinuteHandY2 = @curMinuteHandY1 - (@curMinuteHandLen * COS(@curAngle))
END
ELSE IF @curAngle > PI() / 2 AND @curAngle <= PI()
BEGIN
SET @intQuarter = 2
SET @curAngle = PI() - @curAngle
SET @curMinuteHandX2 = @curMinuteHandX1 + (@curMinuteHandLen * SIN(@curAngle))
SET @curMinuteHandY2 = @curMinuteHandY1 + (@curMinuteHandLen * COS(@curAngle))
END
ELSE IF @curAngle > PI() AND @curAngle <= (PI() * 1.5)
BEGIN
SET @intQuarter = 3
SET @curAngle = @curAngle - PI()
SET @curMinuteHandX2 = @curMinuteHandX1 - (@curMinuteHandLen * SIN(@curAngle))
SET @curMinuteHandY2 = @curMinuteHandY1 + (@curMinuteHandLen * COS(@curAngle))
END
ELSE IF @curAngle > (PI() * 1.5) AND @curAngle <= (PI() * 2)
BEGIN
SET @intQuarter = 4
SET @curAngle = (PI() * 2) - @curAngle
SET @curMinuteHandX2 = @curMinuteHandX1 - (@curMinuteHandLen * SIN(@curAngle))
SET @curMinuteHandY2 = @curMinuteHandY1 - (@curMinuteHandLen * COS(@curAngle))
END
ELSE
BEGIN
PRINT 'Could not work out the minute hand''s quarter segment'
PRINT @curAngle
RETURN
END
/** Loop through Y axis **/
SET @curY = 0
WHILE @curY <= 100
BEGIN
/** Loop through X axis **/
SET @curX = 0
SET @strLine = ''
WHILE @curX <= 100
BEGIN
/** Need to work out how far away the coordinate is from the hour hand **/
-- Find lengths A, B and C for triangle made by hour hand's line and the coordinate
-- C is the hour hand, coordinate is inbetween A and B
SET @curA = SQRT(POWER(ABS(@curX - @curHourHandX1), 2) + POWER(ABS(@curY - @curHourHandY1), 2))
SET @curB = SQRT(POWER(ABS(@curHourHandX2 - @curX), 2) + POWER(ABS(@curHourHandY2 - @curY), 2))
SET @curC = SQRT(POWER(ABS(@curHourHandX2 - @curHourHandX1), 2) + POWER(ABS(@curHourHandY2 - @curHourHandY1), 2))
IF @curB = 0 OR @curC = 0
SET @curAngle = 0
ELSE
SET @curAngle = (POWER(@curB, 2) + POWER(@curC, 2) - POWER(@curA, 2)) / (2 * @curB * @curC)
SET @curAngle = ACOS(@curAngle)
-- If coordinate is perpendicular to line, get the length of the perpendicular
-- otherwise the length from the coordinate to the end of the line
IF @curA * COS(@curAngle) < 0 OR @curA * COS(@curAngle) > @curHourHandLen
SET @intDistance = @curA
ELSE IF @curB * COS(@curAngle) < 0 OR @curB * COS(@curAngle) > @curHourHandLen
SET @intDistance = @curB
ELSE
SET @intDistance = CONVERT(int, @curB * SIN(@curAngle))
/** Need to work out how far away the coordinate is from the minute hand **/
-- Find lengths A, B and C for triangle made by minute hand's line and the coordinate
-- C is the minute hand, coordinate is inbetween A and B
SET @curA = SQRT(POWER(ABS(@curX - @curMinuteHandX1), 2) + POWER(ABS(@curY - @curMinuteHandY1), 2))
SET @curB = SQRT(POWER(ABS(@curMinuteHandX2 - @curX), 2) + POWER(ABS(@curMinuteHandY2 - @curY), 2))
SET @curC = SQRT(POWER(ABS(@curMinuteHandX2 - @curMinuteHandX1), 2) + POWER(ABS(@curMinuteHandY2 - @curMinuteHandY1), 2))
IF @curB = 0 OR @curC = 0
SET @curAngle = 0
ELSE
SET @curAngle = (POWER(@curB, 2) + POWER(@curC, 2) - POWER(@curA, 2)) / (2 * @curB * @curC)
SET @curAngle = ACOS(@curAngle)
-- If coordinate is perpendicular to line, get the length of the perpendicular
-- otherwise the length from the coordinate to the end of the line
IF @curA * COS(@curAngle) < 0 OR @curA * COS(@curAngle) > @curMinuteHandLen
BEGIN
IF @curA < @intDistance
SET @intDistance = CONVERT(int, @curA)
END
ELSE IF @curB * COS(@curAngle) < 0 OR @curB * COS(@curAngle) > @curMinuteHandLen
BEGIN
IF @curB < @intDistance
SET @intDistance = CONVERT(int, @curB)
END
ELSE
IF CONVERT(int, @curB * SIN(@curAngle)) < @intDistance
SET @intDistance = CONVERT(int, @curB * SIN(@curAngle))
/** Finally add a circle round the whole thing **/
SET @curDistanceFromCenter = SQRT(POWER(ABS(@curX - @curCenterX), 2) + POWER(ABS(@curY - @curCenterY), 2))
IF ABS(CONVERT(int, @curDistanceFromCenter - 46)) < @intDistance
SET @intDistance = CONVERT(int, @curDistanceFromCenter - 46)
/** Find out what's at this coordinate **/
IF @curX = 50 AND @curY = 50 SET @strLine = @strLine + 'O' else
SET @strLine = @strLine +
CASE @intDistance
WHEN 0 THEN '#'
WHEN 1 THEN ':'
WHEN 2 THEN '·'
ELSE ' '
END
SET @curX = @curX + 1
--if @intdistance = 0 then set @curtemp = @curAngle
END
PRINT @strLine
SET @curY = @curY + 1
END |
|
|
|
ditch
Flowing Fount of Yak Knowledge
South Africa
1437 Posts |
Posted - 06/08/2004 : 09:08:53
|
Nice One
Great minds think alike hey
Duane. |
|
|
|
Ratboy
Starting Member
2 Posts |
Posted - 06/08/2004 : 09:29:50
|
| True - I reckon if we work together we could get a radio alarm going too... |
|
|
|
maya_zakry
Constraint Violating Yak Guru
Malaysia
377 Posts |
Posted - 06/15/2007 : 04:58:58
|
just found this link.. this is really kewl.. how could someone bored came out witnh this.. ahhaha
~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~ |
|
|
| |
Topic |
|
|
|
Time in Analog
