Transpose with expanding data - 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)

Transpose with expanding data

Author

Topic

caisys
Starting Member

16 Posts

Posted - 2007-12-19 : 08:02:35

Hi,
I have a vacations table like this:

id,startday,endday,description
-------------------------------
1,2008-01-01,2008-01-03,New Year Vacation
2,2008-01-15,2008-01-16,Mid Month Vacation
3,2008-01-25,2008-01-25,Another vacation

I want to run a query to select vacations in Jan 08 that would return
VacationDay,Description
-----------------------
2008-01-01,New Year Vacation
2008-01-02,New Year Vacation
2008-01-03,New Year Vacation
2008-01-15,Mid Month Vacation
2008-01-16,Mid Month Vacation
2008-01-25,Another Vacation

I know the F_TABLE_DATE function can fill in the dates for each vacation record but how can i repeat the function for each record passing the startdate and end date then union all in one result set?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 08:09:25

[code]-- Prepare sample data
DECLARE @Sample TABLE (ID INT, StartDay DATETIME, EndDay DATETIME, Description VARCHAR(100))

INSERT @Sample
SELECT 1, '2008-01-01', '2008-01-03', 'New Year Vacation' UNION ALL
SELECT 2, '2008-01-15', '2008-01-16', 'Mid Month Vacation' UNION ALL
SELECT 3, '2008-01-25', '2008-01-25', 'Another vacation'

-- Show the expected output
SELECT f.Date,
p.Description
FROM @Sample AS p
CROSS APPLY F_TABLE_DATE(p.StartDay, p.EndDay) AS f[/code]

E 12°55'05.25"
N 56°04'39.16"

matty
Posting Yak Master

161 Posts

Posted - 2007-12-20 : 01:03:09

;
with cte(VaccationDay,EndDay,Description)
as
(
select StartDay,EndDay,Description from @Sample where datepart(mm,StartDay)=12 and datepart(yyyy,StartDay)=2008
union all
select VaccationDay+1,EndDay,Description from cte c where VaccationDay < EndDay
)

select VaccationDay,Description from cte
where datepart(mm,VaccationDay)=12 and datepart(yyyy,VaccationDay)=2008
order by VaccationDay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources