Problem with query

• dobrzak-134971

  Right there with Babe

  Points: 737

  More actions

  March 17, 2006 at 5:30 am

  #627259

  Hi Ryan,

  it seems that works, I'm going to check this against my table which is  huge, so it might takes a lot of time.

  Best regards,

  dobrzak

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  March 17, 2006 at 5:37 am

  #627261

  Thanks dobrzak  

  I'm not convinced there's not a more elegant/efficient approach - that's just the best I've thought of so far.

  Since your table is huge, you might want to consider using temporary tables (rather than table variables) because they can be indexed - and then add appropriate indexes, of course...

   

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• Vladan

  SSC-Insane

  Points: 22072

  More actions

  March 17, 2006 at 6:48 am

  #627292

  Same as Ryan, I'm not sure how efficient this code is, but it should work - if what you said is correct, I mean if there are no gaps (time periods when no price is valid).

  CREATE TABLE prices (Item int, StartDate smalldatetime, EndDate smalldatetime, Price int)

  ----your base table----

  INSERT INTO prices (item, startdate, enddate, price)

  SELECT 1, '20040101', '20040131', 100

  UNION

  SELECT 1, '20040201', '20040229', 100

  UNION

  SELECT 1, '20040301', '20040331', 150

  UNION

  SELECT 2, '20040101', '20040131', 200

  UNION

  SELECT 2, '20040201', '20040229', 200

  UNION

  SELECT 1, '20040401', '20040430', 250

  UNION

  SELECT 1, '20040501', '20040531', 250

  UNION

  SELECT 1, '20040601', '20040630', 250

  UNION

  SELECT 1, '20040701', '20040731', 100

  UNION

  SELECT 1, '20040801', '20040831', 100

  ---results table---

  CREATE TABLE #history (Item int, StartDate smalldatetime, EndDate smalldatetime, Price int)

  /*now insert only those rows, when item is new or price has changed*/

  INSERT INTO #history  (item, startdate, enddate, price)

  SELECT pr.item, pr.StartDate, pr.enddate, pr.price

  FROM prices pr

  LEFT JOIN prices pstart ON pstart.item = pr.item

   AND pstart.price = pr.price

   AND pstart.EndDate = pr.StartDate - 1

  WHERE pstart.item IS NULL

  /*find the EndDate; based on assumption there are no gaps it is start of next price period minus 1 day. If the price was valid for one month only, we already have the result in the table - COALESCE = don't overwrite it with NULL*/

  UPDATE h

  SET EndDate = COALESCE((select MIN(StartDate) from #history h1 where h1.item = h.item and h1.StartDate > h.StartDate)-1,EndDate)

  FROM #history h

  SELECT * FROM #history ORDER BY Item, StartDate

  --cleanup--

  drop table prices

  drop table #history

  Well, that's all. It works for me, but you'll have to test it on your data, and maybe add some index on the temp table.