SQL Statement for Current Year?

[vidus]

Hello, I cant figure this one out, its probably really simple too..

I want this:

SQLQUOTES = "SELECT SUM(Value) as QuoSum FROM Jobs WHERE Active='True' OR Active='False'"

... to only return results where QuoteDate = this year only, 2010.

I would also like to know how to return for this month only...

Can anyone help?

 

[ByteMyzer]

SELECT SUM(Value) as QuoSum
FROM Jobs
WHERE YEAR(QuoteDate) = YEAR(GETDATE())
AND MONTH(QuoteDate) = MONTH(GETDATE())

 

[vidus]

SELECT SUM(Value) as QuoSum
FROM Jobs
WHERE YEAR(QuoteDate) = YEAR(GETDATE())
AND MONTH(QuoteDate) = MONTH(GETDATE())

Exactly what I was looking for! Thanks!

 

[ByteMyzer]

You're welcome, I'm glad it worked for you.

 

[namliam]

Is your quotedate an indexed field? In which case you want to use a between statement not a year() or Month()

See here for a whole bunch of 'fun date' stuff:

Select 
  cast(floor(cast(                                       getdate()                           as float) ) as datetime) Today
, cast(floor(cast(                                       getdate()                  + 1      as float) ) as datetime) Tomorrow
, cast(floor(cast(                                       getdate() - day(getdate())          as float) ) as datetime) LastOfPrevMonth
, cast(floor(cast(                                       getdate() - day(getdate()) + 1      as float) ) as datetime) FirstOfThisMonth
, cast(floor(cast(dateadd("M",1                         ,getdate() - day(getdate()) + 1) - 1 as float) ) as datetime) LastOfThisMonth
, cast(floor(cast(dateadd("M",1                         ,getdate() - day(getdate()) + 1)     as float) ) as datetime) FirstOfNextMonth
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1     ,getdate() - day(getdate()) + 1) - 1 as float) ) as datetime) LastOfPrevYear
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1     ,getdate() - day(getdate()) + 1)     as float) ) as datetime) FirstOfThisYear
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1 + 12,getdate() - day(getdate()) + 1) - 1 as float) ) as datetime) LastOfThisYear
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1 + 12,getdate() - day(getdate()) + 1)     as float) ) as datetime) FirstOfNextYear
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1 + (cast(month(getdate()) / 4 as integer)    ) * 3,getdate() - day(getdate()) + 1)     as float) ) as datetime) FirstOfThisQuarter
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1 + (cast(month(getdate()) / 4 as integer) + 1) * 3,getdate() - day(getdate()) + 1) - 1 as float) ) as datetime) LastOfThisQuarter

Could do a whole bunch more, but you get the idea I think?

 

[SQL_Hell]

Is your quotedate an indexed field? In which case you want to use a between statement not a year() or Month()

See here for a whole bunch of 'fun date' stuff:

Select 
  cast(floor(cast(                                       getdate()                           as float) ) as datetime) Today
, cast(floor(cast(                                       getdate()                  + 1      as float) ) as datetime) Tomorrow
, cast(floor(cast(                                       getdate() - day(getdate())          as float) ) as datetime) LastOfPrevMonth
, cast(floor(cast(                                       getdate() - day(getdate()) + 1      as float) ) as datetime) FirstOfThisMonth
, cast(floor(cast(dateadd("M",1                         ,getdate() - day(getdate()) + 1) - 1 as float) ) as datetime) LastOfThisMonth
, cast(floor(cast(dateadd("M",1                         ,getdate() - day(getdate()) + 1)     as float) ) as datetime) FirstOfNextMonth
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1     ,getdate() - day(getdate()) + 1) - 1 as float) ) as datetime) LastOfPrevYear
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1     ,getdate() - day(getdate()) + 1)     as float) ) as datetime) FirstOfThisYear
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1 + 12,getdate() - day(getdate()) + 1) - 1 as float) ) as datetime) LastOfThisYear
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1 + 12,getdate() - day(getdate()) + 1)     as float) ) as datetime) FirstOfNextYear
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1 + (cast(month(getdate()) / 4 as integer)    ) * 3,getdate() - day(getdate()) + 1)     as float) ) as datetime) FirstOfThisQuarter
, cast(floor(cast(dateadd("M",-Month(getdate()) + 1 + (cast(month(getdate()) / 4 as integer) + 1) * 3,getdate() - day(getdate()) + 1) - 1 as float) ) as datetime) LastOfThisQuarter

Could do a whole bunch more, but you get the idea I think?

I have never had problems doing this on an indexed column, why do you suggest this approach?

 

[namliam]

Assuming QuoteDate is an indexed column...
If you use

WHERE YEAR(QuoteDate) = YEAR(GETDATE())
AND MONTH(QuoteDate) = MONTH(GETDATE())

The index is on the FULL date, not on the year or month... The query will work but will not use the index... So why have the index in the first place?
Because if you do use the index the results should be returned (slightly) faster.
Using a syntax simular too:

Where QuoteDate between #01/Aug/2010# and #31/Aug/2010#

You will be using the index... which is why I am a 'query your data' kinda guy...
If your column is a number query a number
If your column is a string query a string... Even if it contains a number... again Int(YourString) = 1 will not use indexes, while YourString = '1' will... Then however you ahve to account (possibly) for leading zero's and stuff... but hey...

And offcourse column is a date, query a date, not a year, not a day... dates.... Now there are some (practical?) limitations, i.e. get me all data for ever wednesday in the last 10 years... This will end up a full table anyways, regardless of how you query it...