Help with CTE please.

• johnnycougar

  SSC Journeyman

  Points: 83

  More actions

  April 1, 2014 at 9:54 am

  #285291

  Hello,

  I am trying but failing miserably with this CTE. Thank you very much for your assistance.

  .

  DECLARE @t TABLE (rowId INT IDENTITY, GradeId INT, DateOfMfg DATETIME)

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (107, '2014-04-01 08:02:27.203')

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (107, '2014-04-01 08:12:32.113')

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (107, '2014-04-01 08:34:13.547')

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (123, '2014-04-01 09:52:39.107')

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (123, '2014-04-01 10:02:57.220')

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (123, '2014-04-01 11:05:17.307')

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (107, '2014-04-01 12:12:27.203')

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (107, '2014-04-01 13:02:32.113')

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (107, '2014-04-01 13:34:13.547')

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (123, '2014-04-01 14:52:39.107')

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (123, '2014-04-01 15:02:57.220')

  INSERT INTO @t (GradeId, DateOfMfg) VALUES (123, '2014-04-01 15:05:17.307')

  .

  Trying to group the GradeId column, detecting changes in the group. At each group, I need to capture the first DateOfMfg as the BeginDate, then when the GradeId changes, the DateOfMfg as the EndDate.

  GradeIdBeginDtEndDt

  1072014-04-01 08:02:27.2032014-04-01 08:34:13.547

  1232014-04-01 09:52:39.1072014-04-01 11:05:17.307

  1072014-04-01 12:12:27.2032014-04-01 13:34:13.547

  1232014-04-01 14:52:39.1072014-04-01 15:05:17.307

  .

  Here is my horrible attempt at a CTE. I thought I was close but definitely not.

  ;WITH CTE1

  as

  (

  SELECT r.GradeId, r.DateofMfg, datediff(MINUTE, '19000101', r.DateofMfg)

  - ROW_NUMBER() OVER (Partition by r.GradeId order by r.GradeId, r.DateofMfg) as GroupID

  FROM @t r

  )

  SELECT

  GradeId,

  min(DateofMfg) as startdate,

  max(DateofMfg) as enddate

  FROM CTE1

  GROUP BY GradeId, GroupID

  .

  Thank you all for your time. -John

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  April 1, 2014 at 10:12 am

  #1702122

  You were really close to the solution.

  WITH CTE1

  as

  (

  SELECT r.GradeId,

  r.DateofMfg,

  ROW_NUMBER() OVER (order by r.DateofMfg)

  - ROW_NUMBER() OVER (Partition by r.GradeId order by r.DateofMfg) as GroupID

  FROM @t r

  )

  SELECT

  GradeId,

  min(DateofMfg) as startdate,

  max(DateofMfg) as enddate

  FROM CTE1

  GROUP BY GradeId, GroupID

  ORDER BY startdate

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• johnnycougar

  SSC Journeyman

  Points: 83

  More actions

  April 1, 2014 at 10:48 am

  #1702144

  Thank you very much. That worked perfectly.