Split date range without Calendar table

Hi sqlsaga,

Don't you think we already tried that? :) We are also developing DW solution but nature of DW is not transactional (bank, store, etc.) but instead we have in-progress processes that lasts for some period of time (20minutes, 3h, etc). These kind of calculations are possible in the SSAS but only for AdventureWorks with 60k data :) Also, because of the nature of clients reports, we ended up using pure T-SQL because that was only possible solution.

Knowing that, we tried to create time slicing report (for example from 01 Jan till 31 Jan in 1h slice periods):

This basically means that we will end up cross joining with huge Fact1 table 744 times which is slow part. We have EE SQL Server 2012 and we can use ColumnStore index which could give us the results faster, but inner/cross join just kills the performance.

So, the question is: 'Split date range without Calendar table'? Is it even possible?

Thanks in advance,