How to write SQL function?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SF - 24 Jul 2008 08:50 GMT

Hi,

I have the follwoing in many views, I think it would be wise to create a
function (scalar??) so that I can reuse it in the future.

SELECT ...
CASE WHEN dbo.tblEvents.EventDate BETWEEN '20061001' AND
'20070930' THEN 'FY 07' WHEN dbo.tblEvents.EventDate
BETWEEN '20071001' AND
'20080930' THEN 'FY 08' WHEN dbo.tblEvents.EventDate
BETWEEN '20081001' AND '20090930' THEN 'FY 09' END AS [Fiscal Year]
...

Could someone direct me?

SF

Reply to this Message

Erland Sommarskog - 24 Jul 2008 10:45 GMT

> I have the follwoing in many views, I think it would be wise to create a
> function (scalar??) so that I can reuse it in the future.
[quoted text clipped - 8 lines]
>
> Could someone direct me?

I would suggest that it is even better to have a table:

CREATE TABLE FYmap (thedate datetime NOT NULL,
[Fiscal Year] char(5) NOT NULL,
CONSTRAINT pk_FYmap PRIMARY KEY (thedate))

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Mariano Gomez - 24 Jul 2008 14:02 GMT

You are better off creating a fiscal period table where you can store this
information.

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

> Hi,
>
[quoted text clipped - 12 lines]
>
> SF

Reply to this Message