Hello,
please consider the following procedure:
SELECT SelDate, TotalAA, TotalAG
FROM (SELECT TOP 20 SelDate, TotalAA, TotalAG
FROM History
WHERE (UserId = @UserId)
ORDER BY Datum DESC) AS derivedtbl
ORDER BY SelDate
'SelDate' is a date (string) in yyyymmdd format. I would like to select a distinct month. Any suggestions?
Thanks in advance,
steve
The string format is ISO compliant so why not CAST it to a date type and then use the MONTH function? To get all January sales use:
SELECT SelDate, TotalAA, TotalAG
FROM (SELECT TOP 20 SelDate, TotalAA, TotalAG
FROM History
WHERE (UserId = @UserId)
ORDER BY Datum DESC) AS derivedtbl
where MONTH(CAST(seldate as DATEITME)) = 1
ORDER BY SelDate
or is the function in the WHERE going to limit performance badly?
Maybe I'm being over-simplistic, but
LEFT(SelDate, 6) + '01'
Would return you the varchar representation of the first day of the month.
You could do it in a more strongly typed manner:
DATEADD(m, DATEDIFF(m, 0, convert(datetime, SelDate)), 0)
That would return you the first day of the month as a datetime type
Your comment "I would like to select a distinct month" is potentially confusing. I think you mean that you want to filter your results to a specific month, rather than choosing the Distinct months that are returned.
Because your SelDate field is a string, you may like to pass in your month in a YYYYMM format, and use a filter like:
WHERE SelDate LIKE @Month + '%'
This will let you put an index on the SelDate field (actually, for the query provided, this index would be better).
CREATE INDEX ixHistoryByMonth ON (UserID, SelDate) INCLUDE (TotalAA, TotalAG, Datum);
It's worth indexing first on UserID, because you filter on that directly. Next would be SelDate, which is like the phone directory filtering next on FirstName. Unfortunately, the query wants the data in Datum order, so this other query might be preferable in some ways, as the data from the index will come back in order, just not filtered sufficiently. This second index will let the TOP be done more efficiently.
CREATE INDEX ixHistoryByMonth2 ON (UserID, Datum) INCLUDE (TotalAA, TotalAG, SelDate);
The choice between indexes here depends entirely on the statistics of your system, and it could be worth having both - or even asking yourself why you want the TOP 20 by Datum, reordered by SelDate.
The WHERE clause on SelDate is the nicest way of filtering the data to a specific month though, as it is most compatible with indexing.
select datepart(month,CONVERT(datetime,SelDate)) as SelMonth
Thank you very much! Very helpfull. (i need to figure out how to login as the person i used to ask the question..)
steve
