How to report period wise data ?

CREATE TABLE #empleaves(
[empid] [int] NULL,
[leavedate] [date] NULL
) ON [PRIMARY]

GO
INSERT #empleaves ([empid], [leavedate]) VALUES (1, CAST(0xCE360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (1, CAST(0xD0360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (2, CAST(0xD0360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (2, CAST(0xBA360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (2, CAST(0xBB360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (2, CAST(0xBC360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (3, CAST(0xC9360B00 AS Date))

;WITH GroupDates AS (
SELECT empid, leavedate
,rd=CAST(leavedate AS DATETIME)-ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate)
FROM #empleaves)
SELECT empid, StartingDate=MIN(leavedate), EndingDate=MAX(leavedate)
,NoOfDays=1+DATEDIFF(day, MIN(leavedate), MAX(leavedate))
FROM GroupDates
GROUP BY empid, rd
ORDER BY empid, StartingDate

DROP TABLE #empleaves

WITH Dates AS
(
SELECT
empid,
leavedate,
Rownum = CASE WHEN ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) % 2 = 0
THEN 2
ELSE 1
END,
instance = ((ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) -
CASE WHEN ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) % 2 = 0
THEN 2
ELSE 1
END)/2) + 1
FROM empleaves
), Dates2 AS
(
SELECT
empid,
instance,
LeaveDate = MAX(CASE WHEN Rownum = 1 THEN leavedate ELSE NULL END),
ReturnDate = MAX(CASE WHEN Rownum = 2 THEN leavedate ELSE NULL END)
FROM Dates
GROUP BY empid, instance
)
SELECT
*,
NoOfDays = DATEDIFF(d,leavedate,returndate)
FROM Dates2
ORDER BY empid, instance

Greg Snidow (3/5/2013)

Well, Dwain, I was about to suggest the data should be stored with some sort of identifying element as to indicate whether or not the date indicates a leave or a return, lest one be tempted to do something like this...

WITH Dates AS
	(
	SELECT 
		empid,
		leavedate,
		Rownum = CASE WHEN ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) % 2 = 0 
						  THEN 2
						  ELSE 1 
					  END,
		instance = ((ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) -
					 CASE WHEN ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) % 2 = 0 
						  THEN 2
						  ELSE 1 
					  END)/2) + 1
	  FROM empleaves
	), Dates2 AS
	(
	SELECT
		empid,
		instance,
		LeaveDate = MAX(CASE WHEN Rownum = 1 THEN leavedate ELSE NULL END),
		ReturnDate = MAX(CASE WHEN Rownum = 2 THEN leavedate ELSE NULL END)
	  FROM Dates
	 GROUP BY empid, instance
	)
SELECT
	*,
	NoOfDays = DATEDIFF(d,leavedate,returndate)
  FROM Dates2
 ORDER BY empid, instance

However, it looks like I am getting rustier the longer I dwell in the mainframe world. Anyhow, I still think a data structure like this still leaves a whole lot of room for error. For example, what would happen if someone (or some process) did not enter a return date, so what is really two consecutive dates appear to be a leave and return date, but in reality are two leave dates? Once again, thanks for the showing, me anyway, a new trick.