Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

The T-SQL Quiz Expand / Collapse
Author
Message
Posted Monday, May 28, 2007 2:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:16 AM
Points: 13, Visits: 552

I happened to have a UDF in my test box that returns a set of numbers, so it was very easy for me to put this together:

select case when n % 3 = 0 and n % 5 = 0 then 'BizzBuzz' when n % 3 = 0 then 'Bizz' when n % 5 = 0 then 'Buzz' else cast(n as varchar(10)) end from Numbers(1,100)

 

The UDF is something I found on Mr Steve Kass's home page. In turn, he gives credit to Mr Itzik Ben-Gan.

create function Numbers(
  @from as bigint,
  @to as bigint
) returns table with schemabinding as return
  with t0(n) as (
    select 1 union all select 1
  , t1(n) as (
    select 1 from t0 as a, t0 as b
  , t2(n) as (
    select 1 from t1 as a, t1 as b
  , t3(n) as (
    select 1 from t2 as a, t2 as b
  , t4(n) as (
    select 1 from t3 as a, t3 as b
  , t5(n) as (
    select 1 from t4 as a, t4 as b
  , Numbers(n) as (
    select row_number() over (order by n) as n
    from t5
  
    select @from + n - 1 as n
    from Numbers
    where n <= @to - @from + 1

Try it also with a million numbers. It is pretty fast!




Post #369284
Posted Monday, May 28, 2007 5:02 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:10 PM
Points: 4,576, Visits: 8,349
Jeff, a little note:
there was no requirement for proper commenting, there was a requirement for 2 min solution.
Cannot say about anybody else, but my typing would not give me a chance to fit the time requirement if I would start typing comments.

And for scalabale solution I would go with Numbers table as well, but for that request spt_values got more than enough numbers. So why waste time on it, if total number could be limited by some business logic.
Post #369292
Posted Monday, May 28, 2007 5:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:58 AM
Points: 2,397, Visits: 3,407
declare @ int set @=1while @<101begin print ISNULL(NULLIF(CASE WHEN @%3=0THEN'Fizz'ELSE''END+CASE WHEN @%5=0THEN'Buzz'ELSE''END,''),@)set @=@+1 end



N 56°04'39.16"
E 12°55'05.25"
Post #369298
Posted Monday, May 28, 2007 5:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:58 AM
Points: 2,397, Visits: 3,407

More here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79902&whichpage=1

 




N 56°04'39.16"
E 12°55'05.25"
Post #369299
Posted Monday, May 28, 2007 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 28, 2007 10:11 AM
Points: 3, Visits: 1

Deadline missed, budget exceeded, client unhappy.

The problem was simple, the solution was not. 

Post #369314
Posted Monday, May 28, 2007 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 12, 2007 2:11 PM
Points: 1, Visits: 1

Ok, fed up reading the thread. I would have failed all the interviewers just simplely in comparing the following two code pieces:

--====== A ========

Case when @i % 3 = 0 and @i % 5 = 0 then 'BizzBuzz'

when @i % 3 = 0 then 'Bizz'

when @i % 5 = 0 then 'Buzz'

else Str(@i)

End

--====== B ========

Case when @i % 5 = 0

case when @i % 3 = 0 then 'BizzBuzz' else 'Buzz' end

when @i % 3 = 0 then 'Bizz'

else Str(@i)

End

The method B will save half of the modular calculation!

 

Post #369316
Posted Monday, May 28, 2007 8:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 11, 2009 6:35 AM
Points: 4, Visits: 2
Hi All
Below is my entry

declare @count int, @message nvarchar(10), @message1 nvarchar(10), @message2 nvarchar(10)
set @count = 1


while @count <= 100
BEGIN
set @message = '' set @message1 = '' set @message2 = ''
IF @count%3 = 0
set @message1 = 'BIZZ'
IF @count%5 = 0
set @message2 = 'BUZZ'

set @message = @message1 + @message2
select @count, @message

set @count = @count + 1

END

sorry i have to get back to work so i did not check how quickly it run
Post #369318
Posted Monday, May 28, 2007 9:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636

Heh... you read into it, ol' friend... no where in the post does there say anything about a time limit. 

[Edit] Ok, I see where you came up with the 2 minute limit... wasn't in Grant's article but in one of the links provided...

If there were a 2 minute time limit for such a thing and it was during an interview, then yes... skip the comments 'cause that's not what they're looking for, but consider finding a job somewhere else because they've just set the stage for how they're gonna operate all the time.

So far as the total number being limited by some business logic... how many times has that happened to you only to have that business logic change? 

I'll say it again, write performance enabled, scalable, documented code  even for the simplest things... unless you really enjoy treading water

 



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #369325
Posted Monday, May 28, 2007 9:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636

Heh... looking at it the wrong way...

Original budget/deadline was wrong because you didn't bid right... Client necessarily changes scale... wants to know why idiots wrote code that wasn't scalable... all future contracts lost because client thinks people who don't have their best interest at heart wrote the code. 

And, wanna tell be why the following would take more than 2 minutes?  It's still documented , scalable, nasty fast, and it still uses setbased thinking... customer happy, inteviewer happy, boss happy, and my peers don't get the work because I had the for-thought to write code anticipating a change...

--===== Limit the number of rows to be built
    SET ROWCOUNT 1000000

--===== Create and populate the table on the fly
  SELECT IDENTITY(INT,1,1) AS RowNum,
         CAST(NULL AS VARCHAR(10)) AS DesiredResult
   INTO #Nums
   FROM Master.dbo.SysColumns sc1 WITH (NOLOCK),
        Master.dbo.SysColumns sc2 WITH (NOLOCK)

--===== Restore the number of rows to return to normal
     -- Note: Can replace with TOP @variable in SQL 2k5
    SET ROWCOUNT 0

--===== Produce the desired results according to the
     -- requirements
 SELECT CASE
             WHEN RowNum % 15 = 0 THEN 'BizzBuzz' --Divisible by 15
             WHEN RowNum %  3 = 0 THEN 'Bizz'     --Divisible by 3
             WHEN RowNum %  5 = 0 THEN 'Buzz'     --Divisible by 5
             ELSE CAST(RowNum AS VARCHAR(10))
        END AS DesiredResult
   FROM #Nums
  ORDER BY RowNum



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #369327
Posted Monday, May 28, 2007 9:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636
And, you left out a THEN necessary to make it work   And, although you think B will save half of the modular calculation, the code runs at the same speed as what you have for A.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #369330
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse