SQL Logic Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Logic Problem

Author

Topic

JWyndham
Starting Member

1 Post

Posted - 2013-11-05 : 18:46:29

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-05 : 20:26:44

Select 0.33333 x 100,
.666667 x .25 x 100,
.666667 x .75 x 100

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-05 : 23:17:15

;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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-06 : 01:47:28

~~why hopper B is 16 and hopper C is 51~~

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-06 : 02:08:14

[code]
;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
[/code]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-06 : 03:10:36

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 05:39:33

[code]
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

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-06 : 12:51:57

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 13:07:17

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-06 : 20:38:06

cheating is not an issue but the result is...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources