x

How to distinct select a yyyymmdd format by month?

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

more ▼

asked Jan 17, 2010 at 10:36 AM in Default

stevie gravatar image

stevie
2 1 1 1

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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?

more ▼

answered Jan 18, 2010 at 11:40 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jan 17, 2010 at 10:52 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 17, 2010 at 10:06 PM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

(comments are locked)
10|1200 characters needed characters left

select datepart(month,CONVERT(datetime,SelDate)) as SelMonth

more ▼

answered Jan 18, 2010 at 01:54 PM

Laura Grimes gravatar image

Laura Grimes
22 1 1 1

(comments are locked)
10|1200 characters needed characters left

Thank you very much! Very helpfull. (i need to figure out how to login as the person i used to ask the question..)

steve

more ▼

answered Jan 18, 2010 at 05:12 PM

stevie gravatar image

stevie
2 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x43

asked: Jan 17, 2010 at 10:36 AM

Seen: 2597 times

Last Updated: Jan 17, 2010 at 10:36 AM