Time In Analog

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.