Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conversion of a plain English problem into T-SQL


Conversion of a plain English problem into T-SQL

Author
Message
rob mcnicol
rob mcnicol
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 679
Comments posted to this topic are about the item Conversion of a plain English problem into T-SQL
matt.bowler
matt.bowler
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1359 Visits: 602
Thanks for that - some interesting points. I suspect that this is the modus operandii for a lot of people. It is when the problem space gets too large and complex that we need some of the more formalised structures to help manage the project.
dennis.hafstrom
dennis.hafstrom
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 62
I do realize this was not the purpose of the article but it made me think of it so here's a recursive version. It'll stop after 100 recursions unless the maxrecursion option is changed of course.

DECLARE @Start BIGINT;
SET @Start = 10;

WITH cte AS (
SELECT @Start [Value]
UNION ALL
SELECT CASE [Value] % 2 WHEN 0 THEN [Value] / 2 ELSE ([Value] * 3) + 1 END
FROM [cte]
WHERE CASE [Value] % 2 WHEN 0 THEN [Value] / 2 ELSE ([Value] * 3) + 1 END != 1
)
SELECT *
FROM [cte];


rob mcnicol
rob mcnicol
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 679
matt.bowler (10/3/2011)
It is when the problem space gets too large and complex that we need some of the more formalised structures to help manage the project.


Yep, agreed. This approach is probably best used by one person working on one thing at a time. I would imagine that using this approach on a large project would result in a fair old mess.

That said, I do recall times when I have used this approach to prototype some of the individual components before starting a larger project to help confirm that the project was viable. Also I found that the prototypes were a handy reference point for isolating and troubleshooting problems in the larger project once the build started.

Thanks for the comment, much appreciated.
rob mcnicol
rob mcnicol
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 679
dennis.hafstrom (10/3/2011)
I do realize this was not the purpose of the article but it made me think of it so here's a recursive version.


nice :-)
ABHILASH DHONGDI
ABHILASH DHONGDI
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 102
might find this interesting...

IF OBJECT_ID('dbo.fn_TestCollatzConjecture') > 0 
DROP FUNCTION dbo.fn_TestCollatzConjecture
GO
CREATE FUNCTION dbo.fn_TestCollatzConjecture ( @StartNumber BIGINT )
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @Min BIGINT
DECLARE @Levels INT
DECLARE @Max BIGINT
DECLARE @Ret VARCHAR(200) ;
WITH ApplyCollatzTransform
AS ( SELECT @StartNumber AS NewNumber ,
0 AS Level
UNION ALL
SELECT CASE WHEN NewNumber % 2 = 0
THEN NewNumber / 2
ELSE ( NewNumber * 3 ) + 1
END ,
Level + 1
FROM ApplyCollatzTransform ACT
WHERE NewNumber > 1
)
SELECT @Min = MIN(NewNumber) ,
@Levels = MAX(Level) ,
@Max = MAX(NewNumber)
FROM ApplyCollatzTransform
OPTION ( MAXRECURSION 0 )

IF @Min = 1
SET @Ret = 'Reached 1 after ' + CONVERT(VARCHAR(20), @Levels)
+ ' Level(s). Hit Max of ' + CONVERT(VARCHAR(20), @Max)
+ ' before getting there...'
ELSE
SET @Ret = 'Did not hit 1 after ' + CONVERT(VARCHAR(20), @Levels)
+ ' Level(s)... Giving up...'

RETURN @Ret

END
go


DECLARE @StartNumber BIGINT

SET @StartNumber = 27

SELECT dbo.fn_TestCollatzConjecture(@StartNumber)


rob mcnicol
rob mcnicol
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 679
ABHILASH DHONGDI (10/3/2011)
might find this interesting...

IF OBJECT_ID('dbo.fn_TestCollatzConjecture') > 0 
DROP FUNCTION dbo.fn_TestCollatzConjecture
etc...



most impressive abhilash, thanks. just to be curious, did you write it using the approach i suggested? or was it written line by line as we see it now?
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1745 Visits: 1949
With regards to your discussion of the iterative process of building the code, one step I didn't see was how to stop the infinite loop if the Collatz Conjecture does NOT reach the value of 1! In the generic sense, this would be the error-catching phase of the development of the code.

p.s. ABHILASH DHONGDI - Clever use of MAXRECURSION 0! Nicely done!
toonjamie
toonjamie
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 Visits: 288
My comment conveniently ignores the
one of the things I am interested in is the count of how many repetitions of the sequence it took to reach 1
but I enjoyed the problem/solution and wanted to comment so, hey ...

Because the tool we're using to solve this poser is SQL, I'd like to see the solution 'learning' about numbers that it knows will eventually reach 1 and therefore cutting corners. So I'd INSERT into a ProvedNumbers table once I hit 1 or hit a number that was already in ProvedNumbers.

e.g. I've learned that 8 goes 4, then 2, then 1. So when I tackle 10 I can stop at 10,5,16,8 and add 10 to ProvedNumbers. This will then help when I do 12 and 13 and they reach 10.

Rob - I look forward to the product of more "particularly idle evenings" !

Jamie.
Isle of Man



rob mcnicol
rob mcnicol
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 679
Carla Wilson-484785 (10/3/2011)
With regards to your discussion of the iterative process of building the code, one step I didn't see was how to stop the infinite loop if the Collatz Conjecture does NOT reach the value of 1! In the generic sense, this would be the error-catching phase of the development of the code.


yep fair point carla. i don't tend to put in error catching when building a prototype unless i am struggling with the prototype itself. when it comes time to rework/rebuild/productionise it then yes, error trapping is crucial.

as it regards to this particular problem - do you have something in mind for stopping the infinite loop?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search