title: Slowly changing dimension by date only
body: <p>I have a database table using the concept of data warehousing of slowly changing dimension to keep track of old versions.
  So, I implemented it with the <a href="http://en.wikipedia.org/wiki/Log_trigger" rel="nofollow">Log Trigger</a> mechanism.</p>
  
  <p>My table is like this:</p>
  
  <pre><code>CREATE TABLE "T_MyTable" (
      "Id" INT NOT NULL DEFAULT NULL,
      "Description" NVARCHAR(255) NULL DEFAULT NULL )
  </code></pre>
  
  <p>and I created an hystory table </p>
  
  <pre><code>CREATE TABLE "T_MyTableHistory" (
      "Id" INT NOT NULL DEFAULT NULL,
      "Description" NVARCHAR(255) NULL DEFAULT NULL,
      StartDate DATETIME,
      EndDate   DATETIME )
  </code></pre>
  
  <p>Then, with a trigger like this, I get the history:</p>
  
  <pre><code>CREATE TRIGGER TableTrigger ON T_MyTable FOR DELETE, INSERT, UPDATE AS
  
  DECLARE @NOW DATETIME
  SET @NOW = CURRENT_TIMESTAMP
  
  UPDATE T_MyTableHistory
     SET EndDate = @now
    FROM T_MyTableHistory, DELETED
   WHERE T_MyTableHistory.Id = DELETED.Id
     AND T_MyTableHistory.EndDate IS NULL
  
  INSERT INTO T_MyTableHistory (Id, Description, StartDate, EndDate)
  SELECT Id, Description, @NOW, NULL
    FROM INSERTED
  </code></pre>
  
  <p>And, to query the history table, I use</p>
  
  <pre><code>SELECT  Id, Description
      FROM T_MyTableHistory
      WHERE @DATE &gt;= StartDate
      AND (@DATE &lt; EndDate OR EndDate IS NULL)
  </code></pre>
  
  <p>Now, my question is this: my customer will actually query the history table by <strong>date only</strong> (i.e. without the time of the day), so I need to get the record version at that date.
  I thought about two options:</p>
  
  <ol>
  <li><p>change the trigger (how?) to record only one "history" record per date.</p></li>
  <li><p>keep the trigger as-is, recording all the changes in the database (including date and time), but then query the history table to get the latest version of a particular date (how?)</p></li>
  </ol>
  
  <p>My feeling is that the second option is easier to implement, otherwise the trigger could become complicated (INSERT or UPDATE, depending on the presence of the history record for current date).</p>
  
  <p>I'd need some help in choosing the right direction, and I'd like to have an example of the SQL query needed, in the chosen option.</p>