
SQL Case question
When using Case statement, is the following possible somehow. I want to say when TimeInCourse divided by CourseDurationSeconds = a certain number, then display an image. Can I do that? The following doesn't work obviously. Any help is appreciated. Thanks<BR><BR>SELECT Course.CourseID,<BR> Course.CourseDisplayName, <BR> Course.CourseDescription, <BR> Course.CourseInstructor,<BR> (Course.CourseDurationMinutes / 60) as CourseDurationMinutes,<BR> (Course.CourseDurationMinutes % 60) as CourseDurationSeconds, <BR> CASE DisplayImage<BR> WHEN TimeInCourse/CourseDurationSeconds = 0 THEN 'images/progress_00.gif'<BR> WHEN TimeInCourse/CourseDurationSeconds = 5 THEN 'images/progress_05.gif'<BR> END<BR> Course.SubjectID, <BR> PersonCourse.StartDate<BR>FROM Course <BR> INNER JOIN<BR> PersonCourse ON Course.CourseID = PersonCourse.CourseID <BR>where PersonCourse.PersonID = @PersonID

RE: SQL Case question
Mis using case<BR><BR>SELECT Course.CourseID,<BR>Course.CourseDisplayName,<BR>C ourse.CourseDescription,<BR>Course.CourseInstructo r,<BR>(Course.CourseDurationMinutes / 60) as CourseDurationMinutes,<BR>(Course.CourseDurationMi nutes % 60) as CourseDurationSeconds, <BR>(CASE (TimeInCourse/CourseDurationSeconds)<BR> WHEN 0 THEN 'images/progress_00.gif'<BR> WHEN 5 THEN 'images/progress_05.gif'<BR>END ) AS DisplayImage,<BR> Course.SubjectID,<BR>PersonCourse.StartDate<BR>FRO M Course<BR>INNER JOIN<BR>PersonCourse ON Course.CourseID = PersonCourse.CourseID<BR>where PersonCourse.PersonID = @PersonID<BR>

RE: Thanks Knight..How bout this?
I need to take the TimeInCourse and divide it by the CourseDurationSeconds...then Round that value to the nearest 10th. So, if its 5, I want to return 10. if its 12, I want it to return 10. But if its 16, I need it to return 20, and so forth. I see that SQL has a ROUND function. But I am not using that correctly either. Thanks for any help.

RE: Thanks Knight..How bout this?
I don't think you can do what you are wanting to do!<BR><BR>Look up round in the BOL  Books on line

RE: Thanks Knight..How bout this?
Don't think ROUND will do it, but another CASE could. I think it was Bill W (maybe?) who once posted an example around here on how to do that. It was something *vaguely* like:<BR><BR>CASE myDigit BETWEEN 1 AND 9 THEN 10<BR>CASE myDigit BETWEEN 11 AND 14 THEN 10<BR>CASE myDigit BETWEEN 16 AND etc etc....<BR><BR>That's not it but you get the idea.

RE: Thanks Knight..How bout this?
Ok, I can see that and use that.. BUT, I cannot figure out how to calculate the percentage!! Anyone want to help a math deprived man?<BR><BR>Thanks!<BR>Harry C

RE: Thanks Knight..How bout this?
Anyone help with getting a percentage of two fields?

RE: Thanks Knight..How bout this?
Jeez, been a long time and even then my math skills sucked. Let me think...<BR><BR>(variableA / variableB) X 100<BR><BR>Sounds vaguely familiar. Sorry, but I am tired and can't find my formula where I have done it before.

RE: Thanks Knight..How bout this?
You know what...I was doing that, but it wasn't working.. because in SQL you have to do this<BR><BR>(variableA as Decimal/ variableB as Decimal) X 100<BR><BR>That made it work. Thanks for the reply though<BR>

RE:In case anyone cares...
This is how it ends up and works like a charm<BR><BR> (CASE (ROUND(CAST(TimeInCourse as Decimal)/(CAST(Course.CourseDurationMinutes as Decimal)) * 100,1))<BR> WHEN 0 THEN 'progress_00.gif'<BR> WHEN 10 THEN 'progress_10.gif'<BR> WHEN 20 THEN 'progress_20.gif'<BR> WHEN 30 THEN 'progress_30.gif'<BR> WHEN 40 THEN 'progress_40.gif'<BR> WHEN 50 THEN 'progress_50.gif'<BR> WHEN 60 THEN 'progress_60.gif'<BR> WHEN 70 THEN 'progress_70.gif'<BR> WHEN 80 THEN 'progress_80.gif'<BR> WHEN 90 THEN 'progress_90.gif'<BR> ELSE<BR> 'progress_completed.gif'<BR> END) AS ProgressImage,
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

