Q:

Replacing While loop with Select query

I have following SQL statements in SQL Server 2008
DECLARE @Interval table(StartInterval DATETIME,
            EndInterval   DATETIME ) 

        WHILE (DATEADD(mi,1440,@STARTDATE) <= @ENDDATE)
        BEGIN
          INSERT INTO @Interval
            SELECT @STARTDATE, DATEADD(mi,1440,@STARTDATE)

          SET @STARTDATE = DATEADD(mi,1440,@STARTDATE)
        END

@StartDate and @EndDate are two parameters.  I am populating a table variable using a while loop and use this table variable further in my stored procedure to join with other tables.  But I want to avoid temp table or a table variable.  Is there any way I could get the same result (achieved with while loop) with a select query without any iterations or loops?  so I could do something like
Select * from (logic that replace while loop) Result inner join some table on col1=col2

Desired result
 StartInterval          EndInterval
2013-10-25 00:00:00.000 2013-10-26 00:00:00.000
2013-10-26 00:00:00.000 2013-10-27 00:00:00.000
2013-10-27 00:00:00.000 2013-10-28 00:00:00.000

A:

Please try using CTE:
declare @StartInterval datetime, @EndInterval   datetime
select @StartInterval='01-June-2013', @EndInterval   =GETDATE();

with T as (
    select 
        @StartInterval as StartInterval, 
        DATEADD(mi, 1440, @StartInterval) EndInterval
    union all
    SELECT 
        EndInterval, DATEADD(mi,1440,EndInterval)
    FROM T
    WHERE 
        EndInterval <= @EndInterval
)select * from T
OPTION (MaxRecursion 0);

OR
select StartInterval, DATEADD(mi, 1440, StartInterval) EndInterval
from(
    select DATEADD(mi, (ROW_NUMBER() OVER (ORDER BY OBJECT_ID)-1)*1440, @StartInterval) StartInterval
    FROM master.sys.all_columns 
)x where StartInterval <=@EndInterval

