Help- Very Complex SQL query

• Jeff Moden

  SSC Guru

  Points: 1004364

  More actions

  February 23, 2010 at 7:27 pm

  #1124037

  Paul White (2/23/2010)

  ---

  @Jeff:

  The reason I didn't do it here is because I tried to convert the 2005 updatable CTE as faithfully and obviously as possible. So, the CTE just became a derived table directly - I gave it no more thought once I saw it compile and run successfully in derived-table format on 2005 and 2008.

  I figured it had to be something like that. Thanks for the great feedback, Paul.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ashokdasari

  SSC-Addicted

  Points: 497

  More actions

  February 25, 2010 at 6:12 am

  #1124899

  DECLARE @tmp_Dates TABLE

  (

  Date1 datetime

  ,Date2 datetime

  )

  INSERT INTO @tmp_Dates VALUES ('1/1/2009','1/1/2009')

  INSERT INTO @tmp_Dates VALUES (NULL,'1/2/2009')

  INSERT INTO @tmp_Dates VALUES (NULL,'1/3/2009')

  INSERT INTO @tmp_Dates VALUES (NULL,'1/4/2009')

  INSERT INTO @tmp_Dates VALUES ('1/5/2009','1/5/2009')

  INSERT INTO @tmp_Dates VALUES (NULL,'1/6/2009')

  INSERT INTO @tmp_Dates VALUES (NULL,'1/7/2009')

  INSERT INTO @tmp_Dates VALUES ('1/8/2009','1/8/2009')

  INSERT INTO @tmp_Dates VALUES (NULL,'1/9/2009')

  INSERT INTO @tmp_Dates VALUES (NULL,'1/10/2009')

  INSERT INTO @tmp_Dates VALUES (NULL,'1/11/2009')

  INSERT INTO @tmp_Dates VALUES ('1/12/2009','1/12/2009')

  SELECT CASE WHEN DATE1 IS NOT NULL THEN DATE1

  ELSE (SELECT TOP 1 T2.DATE1

  FROM @tmp_Dates T2

  WHERE T1.Date2>T2.Date2 AND T2.DATE1 IS NOT NULL

  ORDER BY T2.Date2 DESC)

  END AS DATE1,

  DATE2

  FROM @tmp_Dates T1

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  February 25, 2010 at 6:23 am

  #1124904

  Jeff Moden (2/23/2010)[hr... Other than that, the only difference is less typing... the performance, execution plan, and operation are identical ....

  Let's hope they finally are equal in sql2008.

  It's a petty I've lost a whole bunch of testing scripts on my "nobackup" drive (with lots of space) and therefore cannot found this "unequality" statement :crying:

  My general advise is to use the alias as much as you can.

  So, just keep in mind, if it doesn't perform as you would expect using the table name, test it again using the alias :w00t:

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me