I have been working on this problem for my DB class for a few hours now and I can't seem to wrap my head around it.
I am working with a table MOVIES(MovieID, Title, ReleaseYear, Rating, RunningTime) and I need to figure out a way to group them according to the half hour they fall into. For example, if the running time of Movie1 is 65 minutes, it should fall into a category named 60. If it is 82 minutes long, then it should fall into the 90 minutes category. Similarly, if it is 14 minutes long, it should fall into a category of 0 minutes.
I know there is a way to do this with the cast function but I can't seem to figure it out.
If anyone was curious, I managed to get the results I wanted with the following code:
select HalfHourBlock, count(*) as HowMany
(select 30*cast((10*cast(runningtime/10 as int))/30 as int) as HalfHourBlock, 30*cast((10*cast(runningtime/10 as int))/30 as int) as HowMany
group by HalfHourBlock
order by HalfHourBlock