Query Help

• Jared

  SSC Guru

  Points: 61793

  More actions

  June 13, 2012 at 11:57 am

  #1500571

  Ah yes... Sorry Wayne! REFERENCE_ID, not ID...

  Jared
  CE - Microsoft

• Jared

  SSC Guru

  Points: 61793

  More actions

  June 13, 2012 at 12:03 pm

  #1500574

  I;m not sure that you can do a DENSE_RANK OVER(ORDER BY grp) because grp will not always be unique to all groups. It is in this set of data, but I could produce a set of data (I think) that would result in 2 different TECH_MODE_ID's having the same grp.

  Jared
  CE - Microsoft

• Jared

  SSC Guru

  Points: 61793

  More actions

  June 13, 2012 at 12:09 pm

  #1500579

  Run this:UPDATE sample SET tech_mode_id = 6 WHERE ID = 3

  Then tell me if your code still works. Not defending mine because I know it is a mess... Just pointing this out.

  Jared
  CE - Microsoft

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 13, 2012 at 12:17 pm

  #1500583

  SQLKnowItAll (6/13/2012)

  ---

  Run this:UPDATE sample SET tech_mode_id = 6 WHERE ID = 3

  Then tell me if your code still works. Not defending mine because I know it is a mess... Just pointing this out.

  You mean like this:

  WITH

  SAMPLE (REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME) AS

  (

  SELECT 1, 6, CONVERT(DATETIME,'2012-05-03 20:29:22'),CONVERT(DATETIME,'2012-05-03 20:29:52') UNION ALL

  SELECT 1, 5, '2012-05-03 20:29:53','2012-05-03 20:29:55' UNION ALL

  SELECT 1, 6, '2012-05-03 20:29:56','2012-05-03 20:30:03' UNION ALL

  SELECT 1, 13, '2012-05-03 20:30:04','2012-05-03 20:30:05' UNION ALL

  SELECT 1, 5, '2012-05-03 20:30:06','2012-05-03 20:30:09' UNION ALL

  SELECT 1, 5, '2012-05-03 20:30:10','2012-05-03 20:30:19' UNION ALL

  SELECT 1, 5, '2012-05-03 20:30:20','2012-05-03 20:34:50'

  ),

  cte2 AS

  (

  SELECT * , Grp = ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID ORDER BY BEGIN_TIME) -

  ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID, TECH_MODE_ID ORDER BY BEGIN_TIME)

  FROM SAMPLE

  ), cte3 AS

  (

  SELECT REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME,

  [Rank] = DENSE_RANK() OVER( ORDER BY Grp)

  FROM cte2

  )

  SELECT REFERENCE_ID,

  TECH_MODE_ID,

  BEGIN_TIME = MIN(BEGIN_TIME),

  END_TIME = MAX(END_TIME)

  FROM cte2

  GROUP BY REFERENCE_ID, TECH_MODE_ID, Grp

  ORDER BY REFERENCE_ID, BEGIN_TIME;

  Here is my output:

  REFERENCE_IDTECH_MODE_IDBEGIN_TIMEEND_TIME

  162012-05-03 20:29:22.0002012-05-03 20:29:52.000

  152012-05-03 20:29:53.0002012-05-03 20:29:55.000

  162012-05-03 20:29:56.0002012-05-03 20:30:03.000

  1132012-05-03 20:30:04.0002012-05-03 20:30:05.000

  152012-05-03 20:30:06.0002012-05-03 20:34:50.000

• Jared

  SSC Guru

  Points: 61793

  More actions

  June 13, 2012 at 12:21 pm

  #1500585

  Lynn Pettis (6/13/2012)

  ---

  SQLKnowItAll (6/13/2012)

  ---

  Run this:UPDATE sample SET tech_mode_id = 6 WHERE ID = 3

  Then tell me if your code still works. Not defending mine because I know it is a mess... Just pointing this out.

  You mean like this:

  WITH

  SAMPLE (REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME) AS

  (

  SELECT 1, 6, CONVERT(DATETIME,'2012-05-03 20:29:22'),CONVERT(DATETIME,'2012-05-03 20:29:52') UNION ALL

  SELECT 1, 5, '2012-05-03 20:29:53','2012-05-03 20:29:55' UNION ALL

  SELECT 1, 6, '2012-05-03 20:29:56','2012-05-03 20:30:03' UNION ALL

  SELECT 1, 13, '2012-05-03 20:30:04','2012-05-03 20:30:05' UNION ALL

  SELECT 1, 5, '2012-05-03 20:30:06','2012-05-03 20:30:09' UNION ALL

  SELECT 1, 5, '2012-05-03 20:30:10','2012-05-03 20:30:19' UNION ALL

  SELECT 1, 5, '2012-05-03 20:30:20','2012-05-03 20:34:50'

  ),

  cte2 AS

  (

  SELECT * , Grp = ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID ORDER BY BEGIN_TIME) -

  ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID, TECH_MODE_ID ORDER BY BEGIN_TIME)

  FROM SAMPLE

  ), cte3 AS

  (

  SELECT REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME,

  [Rank] = DENSE_RANK() OVER( ORDER BY Grp)

  FROM cte2

  )

  SELECT REFERENCE_ID,

  TECH_MODE_ID,

  BEGIN_TIME = MIN(BEGIN_TIME),

  END_TIME = MAX(END_TIME)

  FROM cte2

  GROUP BY REFERENCE_ID, TECH_MODE_ID, Grp

  ORDER BY REFERENCE_ID, BEGIN_TIME;

  Here is my output:

  REFERENCE_IDTECH_MODE_IDBEGIN_TIMEEND_TIME

  162012-05-03 20:29:22.0002012-05-03 20:29:52.000

  152012-05-03 20:29:53.0002012-05-03 20:29:55.000

  162012-05-03 20:29:56.0002012-05-03 20:30:03.000

  1132012-05-03 20:30:04.0002012-05-03 20:30:05.000

  152012-05-03 20:30:06.0002012-05-03 20:34:50.000

  Where's the ranking? 🙂

  Jared
  CE - Microsoft

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 13, 2012 at 12:22 pm

  #1500588

  Jared and Wayne,

  Do you notice that cte3 isn't even used in the final select? The final select is using cte2.

• Jared

  SSC Guru

  Points: 61793

  More actions

  June 13, 2012 at 12:24 pm

  #1500590

  Lynn Pettis (6/13/2012)

  ---

  Jared and Wayne,

  Do you notice that cte3 isn't even used in the final select? The final select is using cte2.

  I'm so confused because I mixed up what the op wants as the final output. I think I have it all now though... Yes, I noticed that too 🙂

  Jared
  CE - Microsoft

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 13, 2012 at 12:26 pm

  #1500591

  SQLKnowItAll (6/13/2012)

  ---

  Lynn Pettis (6/13/2012)

  ---

  SQLKnowItAll (6/13/2012)

  ---

  Run this:UPDATE sample SET tech_mode_id = 6 WHERE ID = 3

  Then tell me if your code still works. Not defending mine because I know it is a mess... Just pointing this out.

  You mean like this:

  WITH

  SAMPLE (REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME) AS

  (

  SELECT 1, 6, CONVERT(DATETIME,'2012-05-03 20:29:22'),CONVERT(DATETIME,'2012-05-03 20:29:52') UNION ALL

  SELECT 1, 5, '2012-05-03 20:29:53','2012-05-03 20:29:55' UNION ALL

  SELECT 1, 6, '2012-05-03 20:29:56','2012-05-03 20:30:03' UNION ALL

  SELECT 1, 13, '2012-05-03 20:30:04','2012-05-03 20:30:05' UNION ALL

  SELECT 1, 5, '2012-05-03 20:30:06','2012-05-03 20:30:09' UNION ALL

  SELECT 1, 5, '2012-05-03 20:30:10','2012-05-03 20:30:19' UNION ALL

  SELECT 1, 5, '2012-05-03 20:30:20','2012-05-03 20:34:50'

  ),

  cte2 AS

  (

  SELECT * , Grp = ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID ORDER BY BEGIN_TIME) -

  ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID, TECH_MODE_ID ORDER BY BEGIN_TIME)

  FROM SAMPLE

  ), cte3 AS

  (

  SELECT REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME,

  [Rank] = DENSE_RANK() OVER( ORDER BY Grp)

  FROM cte2

  )

  SELECT REFERENCE_ID,

  TECH_MODE_ID,

  BEGIN_TIME = MIN(BEGIN_TIME),

  END_TIME = MAX(END_TIME)

  FROM cte2

  GROUP BY REFERENCE_ID, TECH_MODE_ID, Grp

  ORDER BY REFERENCE_ID, BEGIN_TIME;

  Here is my output:

  REFERENCE_IDTECH_MODE_IDBEGIN_TIMEEND_TIME

  162012-05-03 20:29:22.0002012-05-03 20:29:52.000

  152012-05-03 20:29:53.0002012-05-03 20:29:55.000

  162012-05-03 20:29:56.0002012-05-03 20:30:03.000

  1132012-05-03 20:30:04.0002012-05-03 20:30:05.000

  152012-05-03 20:30:06.0002012-05-03 20:34:50.000

  Where's the ranking? 🙂

  Good question, which of the following is the actual output needed?

  -- Expected out put if order by BEGIN_TIME:

  1, 6, '2012-05-03 20:29:22','2012-05-03 20:29:52'

  1, 5, '2012-05-03 20:29:53','2012-05-03 20:30:03' <red>(here first five min date, second five max date)</red>

  1, 13, '2012-05-03 20:30:04','2012-05-03 20:30:05'

  1, 5, '2012-05-03 20:30:06','2012-05-03 20:34:50' <red>(here first five min date, third five max date)</red>

  ID REFERENCE_ID TECH_MODE_ID BEGIN_TIME END_TIME RANK

  1 1 6 2012-05-03 20:29:22.000 2012-05-03 20:29:52.000 1

  2 1 5 2012-05-03 20:29:53.000 2012-05-03 20:29:55.000 2

  3 1 5 2012-05-03 20:29:56.000 2012-05-03 20:30:03.000 2

  4 1 13 2012-05-03 20:30:04.000 2012-05-03 20:30:05.000 3

  5 1 5 2012-05-03 20:30:06.000 2012-05-03 20:30:09.000 4

  6 1 5 2012-05-03 20:30:10.000 2012-05-03 20:30:19.000 4

  7 1 5 2012-05-03 20:30:20.000 2012-05-03 20:34:50.000:50' 4

  When the OP mentioned the RANK in the second post, was it because he had it in the info above? Is the rank actually needed or was it just a means to group the data needed?

• Jared

  SSC Guru

  Points: 61793

  More actions

  June 13, 2012 at 12:30 pm

  #1500595

  Ha! I think Wayne's is right without the CTE. I did all that work to display rank when all I needed to do was droup by the new group and do a max and min...

  I think this is the final code, thanks to Wayne.

  ;WITH --semi-colon just in case

  cte2 AS

  (

  SELECT * , Grp = ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID ORDER BY BEGIN_TIME) -

  ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID, TECH_MODE_ID ORDER BY BEGIN_TIME)

  FROM SAMPLE

  )

  SELECT REFERENCE_ID,

  TECH_MODE_ID,

  BEGIN_TIME = MIN(BEGIN_TIME),

  END_TIME = MAX(END_TIME)

  FROM cte2

  GROUP BY REFERENCE_ID, TECH_MODE_ID, Grp

  ORDER BY REFERENCE_ID, BEGIN_TIME;

  Jared
  CE - Microsoft

• WayneS

  SSC Guru

  Points: 95460

  More actions

  June 13, 2012 at 12:41 pm

  #1500599

  Gotta love it when my messed up solution (unused cte) actually worked and solved it.

  ... Actually, I just plain forgot to remove it. I think they call that A-G-E???

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 13, 2012 at 12:44 pm

  #1500603

  WayneS (6/13/2012)

  ---

  Gotta love it when my messed up solution (unused cte) actually worked and solved it.

  ... Actually, I just plain forgot to remove it. I think they call that A-G-E???

  Hey, watch it! You are still younger than me! :w00t:

• Jared

  SSC Guru

  Points: 61793

  More actions

  June 13, 2012 at 12:46 pm

  #1500608

  Lynn Pettis (6/13/2012)

  ---

  WayneS (6/13/2012)

  ---

  Gotta love it when my messed up solution (unused cte) actually worked and solved it.

  ... Actually, I just plain forgot to remove it. I think they call that A-G-E???

  Hey, watch it! You are still younger than me! :w00t:

  And I'm probably younger than both of you and not nearly as "on the ball." 🙂

  Jared
  CE - Microsoft

• WayneS

  SSC Guru

  Points: 95460

  More actions

  June 13, 2012 at 12:49 pm

  #1500610

  Lynn Pettis (6/13/2012)

  ---

  WayneS (6/13/2012)

  ---

  Gotta love it when my messed up solution (unused cte) actually worked and solved it.

  ... Actually, I just plain forgot to remove it. I think they call that A-G-E???

  Hey, watch it! You are still younger than me! :w00t:

  Are you sure??? (I think my youngest is older than your oldest...)

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 13, 2012 at 12:54 pm

  #1500615

  WayneS (6/13/2012)

  ---

  Lynn Pettis (6/13/2012)

  ---

  WayneS (6/13/2012)

  ---

  Gotta love it when my messed up solution (unused cte) actually worked and solved it.

  ... Actually, I just plain forgot to remove it. I think they call that A-G-E???

  Hey, watch it! You are still younger than me! :w00t:

  Are you sure??? (I think my youngest is older than your oldest...)

  I think I was older than you when I had my oldest.

  Actually, I think we had this conversation before. Unfortunately, I think I deleted the PM's.