T SQL QUERY TO GROUPING SEQUENTIAL DATES IN SQL SERVER.

• kbhanu15

  SSCarpal Tunnel

  Points: 4498

  More actions

  January 7, 2014 at 2:44 am

  #1677993

  Hi ALl,

  Thank you very much this query is giving good performance.

  Thanks

  Bhanu

• Mitesh Oswal

  SSCrazy

  Points: 2798

  More actions

  February 21, 2014 at 4:24 am

  #1691046

  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

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  February 21, 2014 at 5:45 am

  #1691066

  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)