Find minimum and maximum start date with repeated values

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 5, 2017 at 3:21 am

  #1941214

  -- "choose priority values" and "eliminate date range dupes" could be done within the same subquery.
SELECT
 ServiceID,
 StartDate = MIN(StartDate),
 EndDate = MAX(EndDate),
 Amount
FROM ( -- d3
 SELECT -- manufacture an appropriate "GROUP BY" column
  ServiceID, StartDate, EndDate, Amount,
  Grouper = DATEADD(MONTH,0-ROW_NUMBER() OVER(PARTITION BY ServiceID, Amount ORDER BY StartDate),EndDate)
 FROM ( -- d2
  SELECT -- eliminate date range dupes
   ServiceID, StartDate, EndDate,
   Amount = MIN(Amount)
  FROM ( -- d1
   SELECT -- choose priority values
    ServiceID,
    StartDate = COALESCE(Priority1Start,StartDate),
    EndDate = CASE WHEN Priority1Start IS NOT NULL THEN Priority1End ELSE EndDate END,
    Amount = COALESCE(Priority1,NULLIF(Priority2,0),Priority3)
   FROM #Test
  ) d1
  GROUP BY ServiceID, StartDate, EndDate 
 ) d2
) d3
GROUP BY ServiceID, Grouper, Amount
ORDER BY ServiceID, StartDate


  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• jamie 82947

  SSC Enthusiast

  Points: 176

  More actions

  May 7, 2017 at 9:53 pm

  #1941498

  Thank you so much!! This is awesome and works great! After running it through all of the real data I found a few more problems however I was able to adapt your code to make it work perfectly.
  I had not used NULLIF before but that is going to come in handy. I'm also still trying to get my head around your Grouper as I've never used ROW_NUMBER OVER PARTITION before either but I'm sure I'll figure it out as I test it on various data. I love learning extra commands that I have never used before.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 8, 2017 at 2:29 am

  #1941524

  jamie 82947 - Sunday, May 7, 2017 9:53 PM

  Thank you so much!! This is awesome and works great! After running it through all of the real data I found a few more problems however I was able to adapt your code to make it work perfectly.
  I had not used NULLIF before but that is going to come in handy. I'm also still trying to get my head around your Grouper as I've never used ROW_NUMBER OVER PARTITION before either but I'm sure I'll figure it out as I test it on various data. I love learning extra commands that I have never used before.

  Excellent work Jamie for figuring out the tweaks, and many thanks for the feedback.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden