tshad (4/24/2013)
I have a table with a month value and year value both ints.I need two things, the latest date and the date 24 months before.
I came up with these to get the latest date and trying to find the most efficient.
SELECT MAX(CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' +
CAST(YearValue AS varchar))) AS ValueDate
FROM dbo.myTable
This gets me a plan of
Stream Aggregate (Aggregate) -Cost 7%
Compute Scalar - Cost 1%
Clustered Index Scan - Cost 91%
The other option is:
SELECT TOP 1
CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' +
CAST(YearValue AS varchar)) AS ValueDate
FROM dbo.myTable
ORDER BY ValueDate DESC
Sort (top N Sort) -Cost 92%
Compute Scalar - Cost 0%
Clustered Index Scan - Cost 8%
Which one is the most efficient? The costs seem equivelant.
The whole query (depending on which select I use) would be:
DECLARE @ValueDate smalldatetime
SELECT @ValueDate =
(SELECT TOP 1
CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' +
CAST(YearValue AS varchar)) AS ValueDate
FROM dbo.myTable
ORDER BY ValueDate DESC)
SELECT DATEADD(month, -23, @ValueDate)
Thanks,
Tom
A better thing to do would be to add a persisted calculated column to do the datetime conversion and then index that. Things will be much faster that way.
As a bit of a sidebar, a more efficient method for doing the calculation would be to get rid of all the VARCHAR conversions and stick to integer math. It's pretty simple, too.
DateTimeValue = DATEADD(mm,YearValue*12-22801+MonthValue,0)
--Jeff Moden
Change is inevitable... Change for the better is not.