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.
Author |
Topic |
kcarbone1970
Yak Posting Veteran
52 Posts |
Posted - 2013-08-09 : 02:25:19
|
Hello, I need to add a case statement to my query that based on the outcome of each case statement will produce an overall single outcome of (yes or no). So im couting the number of classes a particular student has taken in each category (math, language, science, etc) and they need to be at a certain number in each category i.e., each category needs to be at => 6. Finally if they meet each category with => 6 They recieve a Yes. So this is my query thus far and im struggling with the syntax for those case statements...PLEASE HELP
SELECT (SELECT COUNT(HIS_1.CN) AS COUNT1 FROM CRS INNER JOIN HIS AS HIS_1 ON CRS.CN = HIS_1.CN INNER JOIN STU ON HIS_1.PID = STU.ID WHERE (STU.ID = @ID) AND (CRS.U1 = 'A')) AS HISTORY, (SELECT COUNT(HIS.CN) AS COUNT2 FROM CRS AS CRS_1 INNER JOIN HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN STU AS STU_1 ON HIS.PID = STU_1.ID WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'B')) AS ELA, (SELECT COUNT(HIS.CN) AS COUNT2 FROM CRS AS CRS_1 INNER JOIN HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN STU AS STU_1 ON HIS.PID = STU_1.ID WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'C')) AS MATH, (SELECT COUNT(HIS.CN) AS COUNT2 FROM CRS AS CRS_1 INNER JOIN HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN STU AS STU_1 ON HIS.PID = STU_1.ID WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'D')) AS SCIENCE, (SELECT COUNT(HIS.CN) AS COUNT2 FROM CRS AS CRS_1 INNER JOIN HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN STU AS STU_1 ON HIS.PID = STU_1.ID WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'E')) AS FL, (SELECT COUNT(HIS.CN) AS COUNT2 FROM CRS AS CRS_1 INNER JOIN HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN STU AS STU_1 ON HIS.PID = STU_1.ID WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'F')) AS VA, (SELECT COUNT(HIS.CN) AS COUNT2 FROM CRS AS CRS_1 INNER JOIN HIS AS HIS ON CRS_1.CN = HIS.CN INNER JOIN STU AS STU_1 ON HIS.PID = STU_1.ID WHERE (STU_1.ID = @ID) AND (CRS_1.U1 = 'G')) AS Prep |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-09 : 06:02:43
|
why not wrap all in a single select?likeSELECT COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY,COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA,COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH,COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE,COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL,COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA,COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep FROM CRS INNER JOIN HIS ON CRS.CN = HIS.CN INNER JOIN STU ON HIS.PID = STU.ID WHERE (STU.ID = @ID) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-08-09 : 12:01:30
|
[code]SELECT ID, HISTORY, ELA, MATH, SCIENCE, FL, VA, Prep, CASE WHEN HISTORY >= 6 AND ELA >= 6 AND MATH >= 6 AND SCIENCE >= 6 AND FL >= 6 AND VA >= 6 AND Prep >= 6 THEN 'Yes' ELSE 'No' END AS Yes_No FROM ( SELECT STU.ID, COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY, COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA, COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH, COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE, COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL, COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA, COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep FROM dbo.CRS INNER JOIN dbo.HIS ON CRS.CN = HIS.CN INNER JOIN dbo.STU ON HIS.PID = STU.ID WHERE STU.ID = @ID) AS derived[/code] |
|
|
kcarbone1970
Yak Posting Veteran
52 Posts |
Posted - 2013-08-10 : 15:38:29
|
Thanks Scott that worked great!...how would one go about to just show yes or no?? |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-10 : 16:47:41
|
quote: Originally posted by kcarbone1970 Thanks Scott that worked great!...how would one go about to just show yes or no??
Like this?quote: Originally posted by ScottPletcher
SELECT CASE WHEN HISTORY >= 6 AND ELA >= 6 AND MATH >= 6 AND SCIENCE >= 6 AND FL >= 6 AND VA >= 6 AND Prep >= 6 THEN 'Yes' ELSE 'No' END AS Yes_No FROM ( SELECT STU.ID, COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY, COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA, COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH, COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE, COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL, COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA, COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep FROM dbo.CRS INNER JOIN dbo.HIS ON CRS.CN = HIS.CN INNER JOIN dbo.STU ON HIS.PID = STU.ID WHERE STU.ID = @ID) AS derived
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-08-12 : 11:03:03
|
Yep, that should do it. |
|
|
|
|
|
|
|