SQL-gasm (generating 15 minute time slots)
Time to nerd out again…
<nerd>
Threw this bad boy together to generate 15 minute time increments per row, then join it up with time values based on whether or not they fell within the time-slot:
with interval as
(
select cast(‘00:00:00’ as datetime) TimeValue —start value
union all
select CASE WHEN TimeValue = ‘1900-1-1 23:45:00’ THEN DATEADD(n,14,TimeValue) ELSE DATEADD(n,15,TimeValue) END —increment w/ case statement to account for midnight
from interval
where DATEADD(n,14,TimeValue) <= ‘1900-1-1 23:59:59’ —termination value
)
select interval.TimeValue, {workdatabase}.*
from mycte FULL OUTER JOIN {workdatabase} ON CAST ({workdatabase}.Punch_Time as datetime) BETWEEN interval.TimeValue AND DATEADD(n,14,interval.TimeValue)
ORDER BY TimeValue
OPTION (MAXRECURSION 0)
</nerd>
