Select 1st order of every month

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select 1st order of every month

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2009-07-14 : 10:29:08

My query needs to SELECT the 1st order of every month but I'm unsure of how.

So if I have the following, I would need the orders on 03/06/09, 04/01/09, & 05/03/09:

ORDER DATE | ORDER ID
----------------------
03/06/09 | 4999
03/18/09 | 7388
04/01/09 | 8981
04/18/09 | 3319
04/21/09 | 4773
05/03/09 | 8820

Any idea how I can do this?
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 10:32:29

[code]
select *
from
(
select *, row_no = row_number() over (partition by dateadd(month, datediff(month, 0, order_date), 0)
order by order_date)
from yourtable
) o
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]