March 20, 2012 at 4:33 pm
Generate a report with two columns:
-Column 1: contiguous integers from 1 - 100
-Column 2: if Column 1 is a multiple of 3 then "x"
if Column 1 is a multiple of 5 then "y"
if Column 1 is a multiple of 3 and 5 then "xy"
otherwise, NULL
March 20, 2012 at 4:42 pm
How about this?
; WITH Tens (N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
)
, Hundreds(N) AS
(
SELECT T1.N
FROM Tens T1
CROSS JOIN Tens T2
)
, Numbers(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N)
FROM Hundreds
)
SELECT N , Col2 = CASE WHEN N % 3 = 0 AND N % 5 <> 0 THEN 'X'
WHEN N % 5 = 0 AND N % 3 <> 0 THEN 'y'
WHEN N % 3 = 0 AND N % 5 = 0 THEN 'XY'
ELSE NULL
END
FROM Numbers
March 20, 2012 at 4:44 pm
This is a pretty popular problem and can be googled for numerous solutions. Here's a slick one courtesy of the DBA I work with:
;WITH fb as(
select * from
(VALUES (null),(null),('x'),(null),('y'),('x'),(null),(null),('x'),('y'),(null),('x'),(null),(null),('xy'))
fb(fb)
),
x(n) AS
(
SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS x(n)
)
select top 100
ROW_NUMBER() over (order by (select null)) num,
fb.fb let
from x,x x1,x x2,fb
March 20, 2012 at 4:47 pm
homework or interview??
i have the answer just want to know why
EDIT well since its posted here is mine
with E1(N) as (Select 1 UNION ALL Select 1 UNION ALL Select 1 UNION ALL Select 1 UNION ALL
Select 1 UNION ALL Select 1 UNION ALL Select 1 UNION ALL Select 1 UNION ALL
Select 1 UNION ALL Select 1),
E2(N) AS (Select 1 FROM E1 a, E1 b),
CTE(N) AS (SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) From E2)
SELECT c.N, CASE WHEN c.n % 3 = 0 AND c.n % 5 = 0 then 'xy'
WHEN c.N % 3 = 0 THEN 'x'
WHEN c.N % 5 = 0 THEN 'y' END
FROM CTE c
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 20, 2012 at 4:50 pm
capn.hector (3/20/2012)
homework or interview??i have the answer just want to know why
EDIT well since its posted here is mine
I figured as much as well, but if they can't even figure out how to google a common problem like this one, they probably won't be lasting too long in the class/job.
March 20, 2012 at 4:54 pm
roryp 96873 (3/20/2012)
capn.hector (3/20/2012)
homework or interview??i have the answer just want to know why
EDIT well since its posted here is mine
I figured as much as well, but if they can't even figure out how to google a common problem like this one, they probably won't be lasting too long in the class/job.
True. of course seeing the answers roryp 96873 you kinda cheat. ingenious but still cheating in my opinion.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 20, 2012 at 4:57 pm
capn.hector (3/20/2012)
roryp 96873 (3/20/2012)
capn.hector (3/20/2012)
homework or interview??i have the answer just want to know why
EDIT well since its posted here is mine
I figured as much as well, but if they can't even figure out how to google a common problem like this one, they probably won't be lasting too long in the class/job.
True. of course seeing the answers roryp 96873 you kinda cheat. ingenious but still cheating in my opinion.
I can't take much of the credit for it, cheating or otherwise though. Like I said, the DBA here threw out a little clinic on this problem and posted quite a few different solutions. Most were tested for performance on a million plus rows, but this one was the coolest one IMO.
March 20, 2012 at 5:03 pm
Its Assignment thanks for help....:-):-)
March 20, 2012 at 5:07 pm
http://blog.sqlauthority.com/2009/02/02/sql-server-t-sql-script-for-fizzbuzz-logic/
after i coded it and people said its a common problem i googled it. found that first result. sometimes a google search is better than asking on a forum because you will actually learn something.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 20, 2012 at 5:38 pm
CELKO (3/20/2012)
This guy is trying to get us to do his job interview. Let's hunt him down and keep him from hurting his employer by letting them know what a cheat he is!
Geez.. why would you call him(or her) a cheat for asking a question for which the OP did not have an answer? Of all the books you wrote, dint you even consult one single person for yoru doubts ? every single line was only from your view point and brain?
i'm just a noob, a very very low level programmer in the T-SQL community, i have no skillset/expertise/knowledge like you to comment on your astronomical knowledge or on ur books..but i never hurt or call people names.. CHEAT is a very big word.. this is not correct...
March 20, 2012 at 6:08 pm
ColdCoffee (3/20/2012)
CELKO (3/20/2012)
This guy is trying to get us to do his job interview. Let's hunt him down and keep him from hurting his employer by letting them know what a cheat he is!Geez.. why would you call him(or her) a cheat for asking a question for which the OP did not have an answer? Of all the books you wrote, dint you even consult one single person for yoru doubts ? every single line was only from your view point and brain?
i'm just a noob, a very very low level programmer in the T-SQL community, i have no skillset/expertise/knowledge like you to comment on your astronomical knowledge or on ur books..but i never hurt or call people names.. CHEAT is a very big word.. this is not correct...
Actually, it IS a cheat CC. The OP hasn't shown a thing to indicate that (s)he's even tried and the very question (it IS the old "FizzBuzz" interview question) indicates it was for an interview or some homework just as Joe said. Now, to be clear, I don't have a problem with helping folks with such questions but they have to at least try first and show that they've tried. This person didn't even try a While loop. Shoot... this person didn't even try Google. 😉
My normal answer to a question like this would be "Show me what you're tried, first".
--Jeff Moden
March 20, 2012 at 6:12 pm
mamatha17.r (3/20/2012)
Its Assignment thanks for help....:-):-)
So... do you actually understand any of the solutions given or are you going to worsen the cheat?
--Jeff Moden
March 20, 2012 at 6:23 pm
ColdCoffee (3/20/2012)
CELKO (3/20/2012)
This guy is trying to get us to do his job interview. Let's hunt him down and keep him from hurting his employer by letting them know what a cheat he is!Geez.. why would you call him(or her) a cheat for asking a question for which the OP did not have an answer? Of all the books you wrote, dint you even consult one single person for yoru doubts ? every single line was only from your view point and brain?
i'm just a noob, a very very low level programmer in the T-SQL community, i have no skillset/expertise/knowledge like you to comment on your astronomical knowledge or on ur books..but i never hurt or call people names.. CHEAT is a very big word.. this is not correct...
i might agree with you that cheat may be strong. how about lazy, incompetent, or to inexperienced for what ever course he is taking. i saw the problem and had the base approach to the answer in 2 seconds. another 3-4 min coding and post the answer. after others had beat me to it.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 21, 2012 at 1:03 pm
For the heck of it, here is another solution:
WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select
N,
nullif(case when N % 3 = 0 then 'x' else '' end + case when N % 5 = 0 then 'y' else '' end, '')
from
cteTally
where
N <= 100
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply