Reputation: 867
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
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)
See demo on SQLFiddle
Upvotes: 1
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