StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You should be able to do this with a CTE in SQL 2005. Stealing Lievens data:</p> <pre> DECLARE @Attendance TABLE (EmployeeNumber INTEGER, EntryDate DATETIME, Status VARCHAR(1)) INSERT INTO @Attendance VALUES (200, '03/01/2009', 'P') INSERT INTO @Attendance VALUES (200, '03/02/2009', 'A') INSERT INTO @Attendance VALUES (200, '03/03/2009', 'A') INSERT INTO @Attendance VALUES (200, '03/04/2009', 'A') INSERT INTO @Attendance VALUES (200, '04/04/2009', 'A') INSERT INTO @Attendance VALUES (200, '04/05/2009', 'A') INSERT INTO @Attendance VALUES (201, '03/01/2009', 'A') INSERT INTO @Attendance VALUES (201, '03/02/2009', 'A') INSERT INTO @Attendance VALUES (201, '03/03/2009', 'P'); </pre> <p>Then use this CTE to extract the sequence:</p> <pre> WITH Dates ( EntryDate, EmployeeNumber, Status, Days ) AS ( SELECT a.EntryDate, a.EmployeeNumber, a.Status, 1 FROM @Attendance a WHERE a.EntryDate = (SELECT MIN(EntryDate) FROM @Attendance) -- RECURSIVE UNION ALL SELECT a.EntryDate, a.EmployeeNumber, a.Status, CASE WHEN (a.Status = Parent.Status) THEN Parent.Days + 1 ELSE 1 END FROM @Attendance a INNER JOIN Dates parent ON datediff(day, a.EntryDate, DateAdd(day, 1, parent.EntryDate)) = 0 AND a.EmployeeNumber = parent.EmployeeNumber ) SELECT * FROM Dates order by EmployeeNumber, EntryDate </pre> <p>Although as a final note the sequence does seem strange to me, depending on your requirements there may be a better way of aggregating the data? Never the less, this will produce the sequence you require</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload