creating a common table expression to transform a table

Question

I don't seem to be very good with CTE's and setting them up. I understand them but they are just outside of my skill set for creating my own for the purpose i need. I have a table with the Following design

AbsenceID|AbsenceStart|AbsenceEnd|AbsenceType|AbsenceTypeID|StaffCode
222      |01/01/2013  |03/01/2013|FD         |1            |WWF

I've established that a CTE could split the data as needed which would essentially create 3 lines. One for each date returning for example the below

AbsenceID|Absencedate|AbsenceType|AbsenceTypeID|StaffCode
222      |01/01/2013 |FD         |1            |WWF
223      |02/01/2013 |FD         |1            |WWF
224      |03/01/2013 |FD         |1            |WWF

possible duplicate of [SQL query to convert date ranges to per day records](http://stackoverflow.com/questions/5363003/sql-query-to-convert-date-ranges-to-per-day-records) (and many others, as a matter of fact) — Andriy M, Oct 05 '13 at 18:35

score 0 · Answer 1 · answered Sep 27 '13 at 13:23

You don't need CTE. It's much better to have a Days table. If you have it then just join like that:

select *
from Absences a
join Days d on d.Dat between a.AbsenceStart and a.AbsenceEnd

Also Days can be attributed and attributes can be used for filtering just like that

where d.IsHoliday = 0

sqlint · Answer 2 · 2013-09-30T18:53:11.813

It works when datediff(d, AbsenceStart, AbsenceEnd) < 2047

Here's a Demo on SqlFiddle.

select AbsenceID + number 'AbsenceID', AbsenceType, dateadd(d, v.number, AbsenceStart) 'Absencedate',
AbsenceTypeID, StaffCode
from master..spt_values v, abc a 
where type='p'
and dateadd(d, v.number, AbsenceStart) between AbsenceStart and AbsenceEnd

The second example does not have any restriction.

Here's a Demo on SqlFiddle.

with cte
as
(
select AbsenceID, AbsenceID incr,  AbsenceStart, AbsenceEnd, AbsenceType, AbsenceTypeID, StaffCode
from abc

union all

select b.AbsenceID, b.AbsenceID + 1 incr,  DATEADD(D, 1, b.AbsenceStart) AbsenceStart, 
b.AbsenceEnd, a.AbsenceType, a.AbsenceTypeID, a.StaffCode
from abc a inner join cte b on a.AbsenceID = b.AbsenceID
where b.AbsenceStart < a.AbsenceEnd
)

select incr AbsenceID, AbsenceType, AbsenceStart, AbsenceTypeID, StaffCode
from cte
order by 1
option (maxrecursion 0)

creating a common table expression to transform a table

2 Answers2