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


The T-SQL Quiz


The T-SQL Quiz

Author
Message
mrsnake
mrsnake
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: 604

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!





Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5822 Visits: 11394
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.
SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431
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"
SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431

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

 




N 56°04'39.16"
E 12°55'05.25"
jeff preston-223188
jeff preston-223188
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1

Deadline missed, budget exceeded, client unhappy.

The problem was simple, the solution was not. 


duanxn-306048
duanxn-306048
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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!

 


George Ndicu
George Ndicu
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44977 Visits: 39869

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44977 Visits: 39869

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44977 Visits: 39869
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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