Function help please

Question

Post reply

Function help please

Best-330874

SSCrazy

Points: 2350
More actions
May 29, 2007 at 4:32 pm

#117257

Hi ,
i need help on this.i need to write a function in which i want to pass year as an input and it returns me 12 records as follows.
wht i mean is i will pass year which is for instance 2007 it will give or return me 12 records as follows

JAN 2007
FEB 2007
MAR 2007
APRIL 2007
MAY 2007
JUNE 2007
JULY 2007
AUG 2007
SEPT 2007
OCT 2007
NOV 2007
DEC 2007

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

They suggested you to create permanent Calendar table.

Have you done it?

_____________
Code for TallyGenerator

Best-330874 SSCrazy Points: 2350 More actions · Answer 2

no i have not done that and i dont know how to do that,that is why iam asking here how to do it

Best-330874 SSCrazy Points: 2350 More actions · Answer 3

can somebody explain me how can i do that please

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Search the SSC forums for Calendars or Dates table and you will see many examples of how to create and use a static Calendars table.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Best-330874 SSCrazy Points: 2350 More actions · Answer 5

i tried to find it over there but no luck in that too

David McFarland SSChampion Points: 11815 More actions · Answer 6

Post the code you have so far and we can tell you what areas of BOL you need to research to figure out the rest.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

I just searched this forum for "calendar table" and it returned me 133 messages.

I found one of them from Jeff Moden containing script creating calendar table.

Are we searching in different forums?

_____________
Code for TallyGenerator

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

You can use a date table, but I doubt if you will find one that does exactly what you want, because the output you listed has an inconsistent format for the month: some are the full name and some are abbreviations.

You can use the function on this link to load a date table with many different columns of date attributes.

Date Table Function F_TABLE_DATE:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

gamma Valued Member Points: 58 More actions · Answer 9

create function dbo.MonthList(@year int)

returns table

as

/*

USAGE:

declare @year int

set @year=2007

select * from dbo.MonthList(@year )

*/

return

select Months=upper(right(convert(varchar(11),dateadd(mm,n,dateadd(yy,@year-1900,0)),113),8))

from (

select 0 union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5 union all

select 6 union all select 7 union all select 8 union all

select 9 union all select 10 union all select 11 )x(n)

It should work.

Leonid

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Notice it is always the shortimers who seem to jump right in and help?

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 11

This is bare bones but it will do what you want. I didn't write it in the form of a function, I trust you can do that. The following creates and populates a table and then just performs the query that you would have in your function.

CREATE TABLE dbo.Months(
    OrderBy     int         NOT NULL,
    ShortName   varchar(5)  NOT NULL
);

GO

insert into Months(OrderBy, ShortName)
select 0, 'Jan' union all
select 1, 'Feb' union all
select 2, 'Mar' union all
select 3, 'Apr' union all
select 4, 'May' union all
select 5, 'Jun' union all
select 6, 'Jul' union all
select 7, 'Aug' union all
select 8, 'Sep' union all
select 9, 'Oct' union all
select 10, 'Nov' union all
select 11, 'Dec'
go

Declare @Arg    char(4);    --This would be the year as passed into a function.
Set     @Arg    = '2007';

select  ShortName + ' ' + @Year as MonthList
from    Months
order by OrderBy;

I have lots of tables with an OrderBy column. There always seems to be the need to read the rows in a manipulatable order. You can add a FullName column ('January', 'February', etc) to add flexibility to your function -- return 'Jan 2007' or 'January 2007' etc.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms