Im trying to write a script that will pull the information from a table called 'bookings', this contains a column that gives the length of each booking called 'hours'. Values in this column are shown as 1.0 for 60 minutes, 0.75 for 45 minutes, 0.5 for 30 minutes for example. My script needs to count the amount of bookings of a particular length and return the value, so for example I am using a Sum and Case statment for each...
SUM(case when Hours = 1 then 1 else 0 end) as '60 Mins',
SUM(case when Hours = 0.75 then 1 else 0 end) as '45 Mins',
SUM(case when Hours = 0.5 then 1 else 0 end) as '30 Mins',
SUM(case when Hours = 0.25 then 1 else 0 end) as '15 Mins'
My problem is that I need to record bookings over 60 minutes and split them into one of these four groups, an example is 1.5 hours would need to add a value of +1 to the 60 minute case statement and also +1 count to the 30 minute case statement value,
The only way I could think of doing that is to create a temporary table with columns for 60, 45, 30 and 15 count values then update this table by +1 if a result is found, so 1.25 would then update #temptable column(60) +1 and column(15)+1. Not sure how to do this, help greatly appreciated.
Thanks
Simon
Have a look at this sql fiddle. Hopefully that is enough to get you started.
select hours
,100*(hours - round(hours,0,1))
from table1
so your code would be something like
SUM(case when hours - round(hours,0,1) = 0 then 1 else 0 end) as '60 Mins',
SUM(case when hours - round(hours,0,1) = 0.75 then 1 else 0 end) as '45 Mins',
SUM(case when hours - round(hours,0,1) = 0.5 then 1 else 0 end) as '30 Mins',
SUM(case when hours - round(hours,0,1) = 0.25 then 1 else 0 end) as '15 Mins'