Slowly changing dimension by date only

Question

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 Log Trigger mechanism.

My table is like this:

CREATE TABLE "T_MyTable" (
    "Id" INT NOT NULL DEFAULT NULL,
    "Description" NVARCHAR(255) NULL DEFAULT NULL )

and I created an hystory table

CREATE TABLE "T_MyTableHistory" (
    "Id" INT NOT NULL DEFAULT NULL,
    "Description" NVARCHAR(255) NULL DEFAULT NULL,
    StartDate DATETIME,
    EndDate   DATETIME )

Then, with a trigger like this, I get the history:

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

And, to query the history table, I use

SELECT  Id, Description
    FROM T_MyTableHistory
    WHERE @DATE >= StartDate
    AND (@DATE < EndDate OR EndDate IS NULL)

Now, my question is this: my customer will actually query the history table by date only (i.e. without the time of the day), so I need to get the record version at that date. I thought about two options:

1. change the trigger (how?) to record only one "history" record per date.

2. 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?)

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).

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.

Answer 1

At the end, I came up with this query:

SELECT Id, Description
    FROM T_MyTableHistory
    WHERE ( DateAdd(day, datediff(day,0, @MyDate), 0) >= StartDate ) AND 
    (( DateAdd(day, datediff(day,0, @MyDate), 0) < EndDate ) OR ( EndDate IS NULL ))

This should be faster than varchar<->datetime conversion, and it should also be locale-independent. By the way, this query should not need the TOP 1 and the ORDER BY clauses, since the function

DateAdd(day, datediff(day,0, @MyDate)

automatically returns the selected date, with "midnight" time (e.g. 20141215 00:00:00), so records with the same date are automatically cut out of the results.

References:

How to return the date part only from a SQL Server datetime datatype

Best approach to remove time part of datetime in SQL Server

Answer 2

I agree with your second opinion. It is good to save date along with time. While filtering data based on date use CONVERT() function to make sure that DATE only got compared. Also, When client enter a single date, If records have same start and end date they will not be in your filter so use Date >= StartDate and Date <= EndDate not (>= ,<)

DECLARE @Date AS DATETIME
SET @Date = '2013-07-30'

SELECT TOP 1 Id, Description
    FROM T_MyTableHistory
    WHERE CONVERT(VARCHAR(20), @DATE, 103) 
       >= CONVERT(VARCHAR(20), StartDate, 103)
    AND (CONVERT(VARCHAR(20), @DATE, 103) 
       < CONVERT(VARCHAR(20), EndDate, 103) OR EndDate IS NULL)
    ORDER BY StartDate DESC