hi Dwain,
as per you solution its working fine, but when the no. of leaves are more then 12 its giving wrong data.
here i am giving sample input and output
CREATE TABLE #Employee_Leave
(emp_id INTEGER NOT NULL,
leave_date DATE NOT NULL,
NO_Of_Days float not null,
PRIMARY KEY (emp_id, leave_date,NO_Of_Days));
INSERT INTO #Employee_Leave (Emp_id,Leave_Date,NO_Of_Days) VALUES
(89,'11/1/2012',0.5),
(89,'11/2/2012',1),
(89,'11/5/2012',1),
(89,'11/6/2012',1),
(89,'11/7/2012',1),
(89,'11/8/2012',1),
(89,'11/9/2012',0.5),
(89,'11/10/2012',1),
(89,'11/11/2012',1),
(89,'11/12/2012',1),
(89,'11/13/2012',1),
(89,'11/14/2012',1),
(88,'11/14/2012',1),
(88,'11/15/2012',1),
(88,'11/27/2012',0.5),
(87,'11/27/2012',0.5)
-- calculate leaves
;WITH
cteGroupedDates AS
( --=== Find the unique dates and assign them to a group.
-- The group looks like a date but the date means nothing except that adjacent
-- dates will be a part of the same group.
SELECT emp_id,
UniqueDate = leave_date,
DateGroup = DATEADD(dd
,-ROW_NUMBER() OVER (
PARTITION BY emp_id ORDER BY emp_id,leave_date)
,CASE DATEPART(dw, leave_date) WHEN 2 THEN DATEADD(dd, -2, leave_date) ELSE leave_date END )
FROM #Employee_Leave
GROUP BY emp_id,leave_date
)
--===== Now, if we find the MIN and MAX date for each DateGroup, we'll have the
-- Start and End dates of each group of contiguous dates. While we're at it,
-- we can also figure out how many days are in each range of days.
SELECT emp_id,
StartDate = MIN(UniqueDate),
EndDate = MAX(UniqueDate),
[Days] = DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1,
[Type] = CASE WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 = 1 THEN 'Single'
WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 > 1 AND
DATEPART(dw, MIN(UniqueDate)) > DATEPART(dw, MAX(UniqueDate)) THEN 'Connecting'
ELSE 'Continuous' END
FROM cteGroupedDates
GROUP BY emp_id,DateGroup
ORDER BY emp_id,StartDate
output
----------
emp_idStartDateEndDateDaysType
872012-11-272012-11-271Single
882012-11-142012-11-152Continuous
882012-11-272012-11-271Single
892012-11-012012-11-1212Connecting
892012-11-062012-11-149Continuous
expected output
----------------
emp_idStartDateEndDateDaysType
872012-11-272012-11-271Single
882012-11-142012-11-152Continuous
882012-11-272012-11-271Single
892012-11-012012-11-1214Connecting
Observation : when the date is exceeding two weeks dategroup is coming 2 for which is creating two records.
hope there is a solution to fix it.:unsure:
regards,
Ghanshyam