January 7, 2014 at 2:44 am
Hi ALl,
Thank you very much this query is giving good performance.
Thanks
Bhanu
February 21, 2014 at 4:24 am
DECLARE @COMP_RATINGS TABLE
(
EID INT,
RATING VARCHAR(20),
AbsenceStartDate DATETIME,
AbsenceEndDate DATETIME
)
INSERT INTO @COMP_RATINGS VALUES
(5364914, 'b+', '2011-09-30 00:00:00', '2011-12-31 00:00:00'),
(5364914, 'bb', '2011-06-30 00:00:00' , '2011-09-30 00:00:00'),
(5364914, 'bb', '2011-12-31 00:00:00' , '2012-03-31 00:00:00'),
(5364914, 'bb', '2012-03-31 00:00:00' , '2012-06-30 00:00:00'),
(5364914, 'bb+','2009-12-31 00:00:00' , '2010-03-31 00:00:00'),
(5364914, 'bb+','2010-03-31 00:00:00' , '2010-06-30 00:00:00'),
(5364914, 'bb+','2010-06-30 00:00:00' , '2010-09-30 00:00:00'),
(5364914, 'bb+', '2010-09-30 00:00:00' , '2010-12-31 00:00:00'),
(5364914, 'bb+', '2010-12-31 00:00:00' , '2011-03-31 00:00:00'),
(5364914, 'bb+', '2011-03-31 00:00:00' , '2011-06-30 00:00:00'),
(5364914, 'bb+', '2012-06-30 00:00:00' , '2012-12-31 00:00:00'),
(5364914, 'bb+', '2012-12-31 00:00:00' , '2013-03-31 00:00:00')
;WITH CTE AS
(
Select *,ROW_NUMBER() OVER(PARTITION BY EID,RATING ORDER BY AbsenceStartDate)RN from @COMP_RATINGS
)
,cte2 as
(
select * from cte C where Rn =1
UNION ALL
select
C1.EID,C1.RATING,IIF(C2.AbsenceStartDate <= C1.AbsenceEndDate,C1.AbsenceStartDate,C2.AbsenceStartDate)AbsenceStartDate,
IIF(C2.AbsenceEndDate <= C1.AbsenceEndDate,C1.AbsenceEndDate,C2.AbsenceEndDate)AbsenceEndDate,C1.Rn+1 AS RN
from cte2 C1 INNER JOIN Cte C2 ON C2.Eid = C1.Eid And C2.RATING = C1.RATING AND C1.Rn+1 = C2.Rn
)
select Distinct EID,RATING,AbsenceStartDate,MAX(AbsenceEndDate) OVER(Partition by EID,RATING,AbsenceStartDate) from cte2
Order by 2,3
Regards,
Mitesh OSwal
+918698619998
February 21, 2014 at 5:45 am
Try
select rating, min(AbsenceStartDate), max(AbsenceEndDate)
from (select r.*, AbsenceEndDate -
(select sum ( DATEDIFF (dd,AbsenceStartDate, AbsenceEndDate))
from @COMP_RATINGS r2 where r2.RATING = r.rating and r2.AbsenceStartDate <= r.AbsenceStartDate) grp
from @COMP_RATINGS r ) g
group by rating, grp
order by rating, min(AbsenceStartDate)
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply