CODEWITHSUNDEEP
sqlsql-server
rgx71
rgx71

Reputation: 867

Getting history data from a table

I have a History table in a SQL Server database that I need to get information from different months for example I have an EmployeeHistory table, this table has a record for any update that I'm doing for each employee. During a month I can have many updates but I only only the latest one. I for example I don't do any update during a month, I show go I get the latest update for that month.

For example

ID Name Email           Date
1  A    [email protected]   2013-01-10
1  A    [email protected] 2013-01-20
1  A    [email protected]  2013-03-15

In this case I don't have any changes in February but I need to bring it with the same data as January, in this case the latest one from January should be repeated in February so the query should bring this result:

ID Name Email           Date
1  A    [email protected] 2013-01-20
1  A    [email protected] 2013-01-20
1  A    [email protected]  2013-03-15

How can I build the query for these cases?

Thank you.

Upvotes: 4

Views: 1828

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

To create a sequence of the months you can either use system table master..spt_values or your own sequence table. So you can find the values ​​for all months, regardless whether there are changes or not

SELECT DATENAME(mm, DATEADD(mm, v.number-1, '20010101')) AS mName, 
       o.Id, o.Email, o.[Date]
FROM master..spt_values v
  OUTER APPLY(
              SELECT h3.Id, h3.NAME, h3.Email, h3.Date 
              FROM (
                    SELECT h.Id, MAX(h.[Date]) AS [Date]
                    FROM dbo.EmployeeHistory h
                    WHERE MONTH(h.[Date]) <= v.number
                    GROUP BY h.Id
                    ) h2 JOIN dbo.EmployeeHistory h3
                      ON h2.Id = h3.Id AND h2.Date = h3.Date
              ) o
WHERE v.[type] = 'P' AND v.number BETWEEN 1 AND 12

For better performance, you can use this indexes:

CREATE INDEX x ON dbo.EmployeeHistory(Id, [Date]) INCLUDE(Name, Email)

enter image description here

See demo on SQLFiddle

Upvotes: 1

Scott Mitchell
Scott Mitchell

Reputation: 8759

Start by creating a table variable that specifies the months for which you want to see the most recent history record for.

DECLARE @MonthsOfInterest AS TABLE (FirstOfMonth DATE)

Next, determine what dates you want to report on. You could hard-code the dates here like so:

INSERT INTO @MonthsOfInterest ( FirstOfMonth ) VALUES('2013-08-01')
INSERT INTO @MonthsOfInterest ( FirstOfMonth ) VALUES('2013-07-01')
INSERT INTO @MonthsOfInterest ( FirstOfMonth ) VALUES('2013-06-01')
...

Or you could be more suave and programmatically load in the past 12 months, or whatever.

But once you have this table variable created and populated you're off to the races. Now it's just a matter of a LEFT JOIN:

SELECT FirstOfMonth, Email, Date
FROM @MonthsOfInterest
    LEFT JOIN HistoryTable ON
        HistoryTable.ID = (
            SELECT TOP 1 ID 
            FROM HistoryTable 
            WHERE Date < @MonthsOfInterest.FirstOfMonth 
            ORDER BY Date DESC
        )

In English, for each record in @MonthsOfInterest we join on the HistoryTable record whose Date value is less than the start of the month and who has the largest Date value.

(Note: If there are multiple records with the same Date value it will choose the one that has the least ID value most likely. You could add an additional column to the ORDER BY clause if you wanted to break ties some other way.)

Thanks

Upvotes: 1

Related Questions