Post reply

Function help please

  • 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

  • May 29, 2007 at 4:41 pm

    #709512

    They suggested you to create permanent Calendar table.

    Have you done it?

    _____________
    Code for TallyGenerator

  • May 29, 2007 at 4:46 pm

    #709514

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

  • May 29, 2007 at 4:57 pm

    #709517

    can somebody explain me how can i do that please

  • May 29, 2007 at 4:59 pm

    #709518

    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

  • May 29, 2007 at 5:19 pm

    #709523

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

  • May 29, 2007 at 6:43 pm

    #709531

    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.

  • May 29, 2007 at 6:50 pm

    #709532

    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

  • May 29, 2007 at 10:16 pm

    #709549

    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

     

  • May 30, 2007 at 5:04 pm

    #709852

    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

  • May 30, 2007 at 5:44 pm

    #709861

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

  • May 30, 2007 at 8:04 pm

    #709880

    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

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

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