Transforming Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Transforming Dates

Author

Topic

singularity
Posting Yak Master

153 Posts

Posted - 2007-11-30 : 12:49:00

I have a table that currently looks like this:

id     admission_date     discharge_date
---------------------------------------------------
1     11/01/2007           11/03/2007
2     11/13/2007           11/14/2007

I need to transform this data, so that there is a row for every day that a patient spent in the hospital, so it would look like this:

date              id
---------------------------
11/01/2007     1
11/02/2007     1
11/03/2007     1
11/13/2007     2
11/14/2007     2

Is there quick and easy way to do this? I need to be able to do this within a view, so no cursors please.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-30 : 13:11:32

CREATE @Results TABLE
(
ID int,
Date datetime
)

DECLARE @i int,@ID int
SELECT @ID=MIN(id) FROM Table

WHILE @ID IS NOT NULL
BEGIN
SELECT @i =DATEDIFF(d,admission_date,discharge_date) FROM Table WHERE id=@ID

WHILE @i >0
BEGIN
INSERT @Results
SELECT @ID,DATEADD(d,1,admission_date) FROM Table
SET @i=@i-1
END

SELECT @ID=MIN(id) FROM Table WHERE id>@ID
END
GO

Now this

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-30 : 13:21:51

Here is another way without looping. It requires a "numbers" table. I coded a runtime numbers table but you could pre-create one if you don't already have one.



create table #t (id int, admission_date datetime, discharge_date datetime)
insert #t values (1     ,'2007-11-01'           ,'2007-11-03')
insert #t values (2     ,'2007-11-13'           ,'2007-11-14')

select	dateadd(day, numbers.n, admission_date) as [date]
	,t.[id]					as [id]

from	(--any table of numbers
	select n1+n2+n3+n4 as n
	from	(select 0 n1 union select 1) n1
	cross join (select 0 n2 union select 2) n2
	cross join (select 0 n3 union select 4) n3
	cross join (select 0 n4 union select 8) n4
	) numbers

join	#t t 
		on datediff(day, t.admission_date, t.discharge_date) >= numbers.n
order by 2, 1

drop table #t

output:

date                                                   id          
------------------------------------------------------ ----------- 
2007-11-01 00:00:00.000                                1
2007-11-02 00:00:00.000                                1
2007-11-03 00:00:00.000                                1
2007-11-13 00:00:00.000                                2
2007-11-14 00:00:00.000                                2

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources