SQLTeam.com | Weblogs | Forums

Sql query


#1

Hi All
How can i get the bottom query to return result as a table ?

declare @admission datetime
declare @LOS int
declare @start int
declare @end int

set @LOS = 4
set @admission = '20160426'

set @start = 1
set @end = @LOS

while @end >= @start begin

select DATEADD(day,@start,@admission-1)
set @start = @start+1

end


#2

If you have a numbers table in your database, you can make use of that. If you don't have one, you can use the master..spt_values (which is a MS-supplied table in the master database) like shown below:

DECLARE @admission DATETIME = '20160426'
DECLARE @LOS INT = 4

SELECT TOP ( @LOS )
        DATEADD(DAY, ROW_NUMBER() OVER 
			( ORDER BY ( SELECT  NULL ) ), @admission)
FROM    master..spt_values

#3

Thank you