Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jeffbond
Starting Member
16 Posts |
 Posted - 2008-12-08 : 10:47:41
|
| Hi,I need to group car sales by quarter from the previous 4 quartersI produced the following but it does not seem to include December.What is the correct SQL pls? select datepart("yy",created_on) as year, datepart("q",created_on) as quarter, product, count(t1.car_id) as cars from @cars t1 where t1.product in ('ford','wolkswagen','saab') and created_on >= dateadd("q",-4,getdate()) and datepart("q",created_on) < datepart("q",dateadd("q",1,created_on)) group by datepart("yy",created_on), datepart("q",created_on), product order by year, quarter asc |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-12-08 : 11:09:21
|
| [code]datepart("q",created_on) < datepart("q",dateadd("q",1,created_on))[/code] will get rid off all 4th quarters. |
 |
|
|
jeffbond
Starting Member
16 Posts |
Posted - 2008-12-08 : 11:22:54
|
| I replaced the date filter with the following : and created_on < dateadd("q",1,created_on) I think it is working |
|
|
|
crosan
Starting Member
13 Posts |
Posted - 2008-12-08 : 11:25:49
|
| From the query, I don't see how you'd be returning any fourth quarter results. Your where clause:created_on >= dateadd("q",-4,getdate())and datepart("q",created_on) < datepart("q",dateadd("q",1,created_on))Tells the SQL engine to:give me any rows that have a created_on date later than todays date last year, this means if it was run today it will strip any data in the fourth quarter of last year that is earlier than Dec 8, 2007ANDThe value of the quarter the created_on column falls into is less than value of the following quarter. Once you hit the fourth quarter, the quarter value returns to 1 which will always be less than 4. |
|
|
|
|
|
|
|
|
SQL group by quarter