Get value for specified date.

• tona3743

  Newbie

  Points: 4

  More actions

  June 22, 2011 at 11:36 pm

  #239850

  Hello,

  I have a value change log table.

  CREATE TABLE #LogTable (

  CustomerID int

  , LogDate datetime

  , Value bit

  )

  Record is inserted, when value changes:

  INSERT INTO #LogTable VALUES (1001, '2011-01-02', 1)

  INSERT INTO #LogTable VALUES (1001, '2011-01-05', 0)

  INSERT INTO #LogTable VALUES (1001, '2011-01-10', 1)

  I need to get a result, which shows what value was on certain date. Result should look like:

  1001, 2011-01-02, 1

  1001, 2011-01-03, 1

  1001, 2011-01-04, 1

  1001, 2011-01-05, 0

  1001, 2011-01-06, 0

  1001, 2011-01-07, 0

  1001, 2011-01-08, 0

  1001, 2011-01-09, 0

  1001, 2011-01-10, 1

  1001, 2011-01-11, 1

  1001, 2011-01-12, 1

  If anyone has any idea how to get this result, please help.

  Thanks.

  P.S. And the bad news is that I have to run this on SQL Server 2000. 🙁

• manub22

  Hall of Fame

  Points: 3133

  More actions

  June 22, 2011 at 11:58 pm

  #1342797

  Yes, the bad thing is you have 2000, otherwise in 2005 & 2008 its would be easy to do in a single query but still with a tricky logic.

  Right now now I can't think other than creating a CURSOR or doing this in WHILE loop.

  ---

  ~manoj | LinkedIn | Facebook | YouTube
  Blog: SQLwithManoj.com