urgent

Question

Post reply

urgent

mamatha17.r

SSC-Addicted

Points: 442
More actions
March 20, 2012 at 4:33 pm

#253316

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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

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

)

, 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

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 2

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

CapnHector SSCoach Points: 16143 More actions · Answer 3

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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

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]

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 4

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.

CapnHector SSCoach Points: 16143 More actions · Answer 5

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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

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]

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 6

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.

mamatha17.r SSC-Addicted Points: 442 More actions · Answer 7

mamatha17.r

SSC-Addicted

Points: 442

March 20, 2012 at 5:03 pm

#1461960

Its Assignment thanks for help....:-):-)

CapnHector SSCoach Points: 16143 More actions · Answer 8

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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

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]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 9

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...

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 10

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

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 11

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

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CapnHector SSCoach Points: 16143 More actions · Answer 12

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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

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

), --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