 Spill
|
| Joined: 10 Dec 2008 |
| Total Posts: 2 |
| |
|
sort by finacial year
Posted: 19 Dec 2008 02:37 PM |
Hi Guys,
I have done a tricky and cast a text field as a date so i can sort by month.
CAST('01-' + ReportMonth + '-2008' AS datetime) AS month
But now i need the Month to sort by finacial year. ie. 7,8,9,10,11,12,1,2,3,4,5,6
can some one help please. |
|
|
 |
|
|
Re: sort by finacial year
Posted: 05 Jan 2009 07:33 AM |
Firstly, I don't see why you need to do the "tricky" stuff to sort by the month.
ORDER BY DATEPART(yy, [ReportMonth]), DATEPART(mm, [ReportMonth])
will sort by year and month.
For your financial year sorting, I'd suggest setting up a calendar table. Do a quick Google on "sql calendar table" and you'll find a number of examples.
|
Thanks
Phill
Colt 45 - the original point and click interface ;)
[url]http://philcart.blogspot.com/[/url] |
|
|
|
urid
|
 |
| Joined: 18 Jun 2007 |
| Total Posts: 34 |
| |
|
Re: sort by finacial year
Posted: 05 Jan 2009 09:42 PM |
I agree that you will be better of creating a calendar table. See if the following helps you
create table t (
d datetime
)
go
insert into t
select top 100 OrderDate
from Northwind..Orders
where day(OrderDate) in (1,2,15,28,29,30,31)
order by CustomerID
go
declare @fiscBegins datetime
set @fiscBegins = '17530501' -- May 1 starts fiscal year
if day(@fiscBegins) > 1 begin
print 'Fiscal year must start on the first of a month.'
return
end
select
d,
datediff(month,@fiscBegins,d)/3%4+1 as Qtr
from t
order by d
go
drop table t |
Best Regards,
Uri Dimant
SQL Server MVP
http://dimantdatabasesolutions.blogspot.com/
|
|
|
|