SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Logic Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JWyndham
Starting Member

1 Posts

Posted - 11/05/2013 :  18:46:29  Show Profile  Reply with Quote
I need help writing SQL code to produce a procedure that corresponds to the inputs and expected outputs of this visual problem.

The link to the image is supplied below

Output:

Hopper A: 33 Hopper B: 16 Hopper C: 51



Any assistance would be much appreciated!

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 11/05/2013 :  20:26:44  Show Profile  Reply with Quote
Select 0.33333 x 100,
.666667 x .25 x 100,
.666667 x .75 x 100
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
964 Posts

Posted - 11/05/2013 :  23:17:15  Show Profile  Reply with Quote

;WITH Mable AS(
	SELECT	1 Mable
	UNION ALL
	SELECT	Mable + 1
	FROM Mable
	WHERE Mable < 100
)
SELECT	
	GatesA = SUM(CASE WHEN Mable % 3 = 0 THEN 1 ELSE 0 END), 
	GatesB = SUM(CASE WHEN Mable % 4 = 0 AND Mable % 3 <> 0 THEN 1 ELSE 0 END),
	GatesC = COUNT(1) - SUM(CASE WHEN Mable % 3 = 0 THEN 1 ELSE 0 END) - SUM(CASE WHEN Mable % 4 = 0 AND Mable % 3 <> 0 THEN 1 ELSE 0 END)
FROM	Mable

Edited by - waterduck on 11/06/2013 02:09:30
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
964 Posts

Posted - 11/06/2013 :  01:47:28  Show Profile  Reply with Quote
why hopper B is 16 and hopper C is 51

Edited by - waterduck on 11/06/2013 02:09:46
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
964 Posts

Posted - 11/06/2013 :  02:08:14  Show Profile  Reply with Quote

;WITH Mable AS(
	SELECT	1 Mable
	UNION ALL
	SELECT	Mable + 1
	FROM Mable
	WHERE Mable < 100
),	GatesA AS(
SELECT	
	*, HopperA = CASE WHEN Mable % 3 = 0 THEN 1 ELSE 0 END
FROM	Mable
),	GatesB AS(
SELECT	*, HopperB = ROW_NUMBER() OVER (ORDER BY Mable) % 4
FROM	GatesA
WHERE	HopperA <> 1)
SELECT	
	SUM(CASE WHEN A.HopperA = 1 THEN 1 ELSE 0 END)
,	SUM(CASE WHEN B.HopperB = 0 THEN 1 ELSE 0 END)
,	COUNT(1) - SUM(CASE WHEN A.HopperA = 1 THEN 1 ELSE 0 END) - SUM(CASE WHEN B.HopperB = 0 THEN 1 ELSE 0 END)
FROM	GatesA A 
	LEFT JOIN GatesB B
	ON A.Mable = B.Mable
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
964 Posts

Posted - 11/06/2013 :  03:10:36  Show Profile  Reply with Quote
khtan better solution...

;WITH Marble AS(
	SELECT	1 Marble
	UNION ALL
	SELECT	Marble + 1
	FROM Marble
	WHERE Marble < 100
),Gates AS
(
 SELECT  *, 
  Hopper = CASE  WHEN Marble % 3 = 0 THEN 'A' 
     WHEN (Marble + 1) % 6 = 0 
     THEN 'B' 
     ELSE 'C' 
     END
 FROM  Marble
)
SELECT  Hopper, count(*)
FROM  Gates
group by Hopper
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/06/2013 :  05:39:33  Show Profile  Reply with Quote

DECLARE @N int = 100

SELECT SUM(CASE WHEN number %3=0 THEN 1 ELSE 0 END) AS HopperA,
SUM(CASE WHEN number % 3 !=0  AND RN % 4 =0   THEN 1 ELSE 0 END) AS HopperB,
SUM(CASE WHEN number % 3 !=0  AND RN % 4 !=0   THEN 1 ELSE 0 END) AS HopperC
FROM (SELECT number, ROW_NUMBER() OVER (ORDER BY CASE WHEN number %3=0 THEN 999 ELSE number END) AS RN
FROM master..spt_values
WHERE type='p'
AND number between 1 AND @N
)t


Output
-----------------------------------
HopperA	HopperB	HopperC
-----------------------------------
33	16	51



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 11/06/2013 :  12:51:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
I suspect that the teacher will know JWyndham cheated if he/she submits this. There's no way a newbie SQL programmer could come up with those solutions.

I suspect other students will use WHILE loop and variables, which is what newbies would do.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/06/2013 :  13:07:17  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

I suspect that the teacher will know JWyndham cheated if he/she submits this. There's no way a newbie SQL programmer could come up with those solutions.

I suspect other students will use WHILE loop and variables, which is what newbies would do.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/


Ah...just realized this is New to SQL forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
964 Posts

Posted - 11/06/2013 :  20:38:06  Show Profile  Reply with Quote
cheating is not an issue but the result is...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000