I want to create a function with minimum lines of code.  I know how to do if
then statement but there are too many cases to cover,
I dont know if there is a better way to do this. could you guys help?



if I pass 2004 year,  and quarter 1

FN_FindPreviousQuarters ( 2004, 1)


I want my function to return AS a table


qid   year   qtr
1.    2004   1
2.    2003   4
3.    2003   3
4.    2003   2




FN_FindPreviousQuarters ( 2005, 4)

qid   year   qtr
1.    2005   4
2.    2005   3
3.    2005   2
4.    2005   1

You can also specify the number of quarters to be returned, just check this
here out, should work for you:


CREATE FUNCTION dbo.LastQuarters
(
        @Year varchar(50),
        @Quarter varchar(50),
        @NumberofQuarters varchar(50) = 4
)
RETURNS @tquarters TABLE
(
        qid varchar(50),
        [Year] varchar(50),
        [Quarter] varchar(50)
)
AS
BEGIN
DECLARE @Startdate varchar(50)
DECLARE @I INT

SET @I = 0

SET @StartDate = @Year + RIGHT('0' + CONVERT(varchar(50),(@Quarter-1)*3
+1),2) + '01'

WHILE @I < @NumberofQuarters
BEGIN
        INSERt INTO @tquarters
        Select
                CAST(@I as Varchar(50)) as qid,
                DATEPART(yyyy,DATEADD(qq,(-1)*@i,@StartDate)) as [Year],
                DATEPART(qq,DATEADD(qq,(-1)*@i,@StartDate)) as [Quarter]
        Set @I = @I +1
END

RETURN
END
GO

Select * from dbo.LastQuarters(2004,1,4)

Show quoteHide quote

See if this helps:

create function dbo.ufn_f1(
@y int,
@q int
)
returns @t table (y int, q int)
as
begin

declare @t1 table (pk int primary key, y int, q int)

insert into @t1
select pk, y, q
from
    (
    select 1 as pk, 0 as y, 4 as q
    union all
    select 2 as pk, 0 as y, 3 as q
    union all
    select 3 as pk, 0 as y, 2 as q
    union all
    select 4 as pk, 0 as y, 1 as q
    union all
    select 5 as pk, 1 as y, 4 as q
    union all
    select 6 as pk, 1 as y, 3 as q
    union all
    select 7 as pk, 1 as y, 2 as q
    ) as t1

insert into @t (y, q)
select
    @y - y,
    q
from
    @t1 as a
where
    pk between (select b.pk from @t1 as b where b.y = 0 and b.q = @q)
    and (select b.pk from @t1 as b where b.y = 0 and b.q = @q) + 3

return

end
go

select * from dbo.ufn_f1(2004, 1) order by y desc, q desc
select * from dbo.ufn_f1(2005, 4) order by y desc, q desc
go

drop function dbo.ufn_f1
go


AMB

Show quoteHide quote

can't return zero when no rows exist using aggregate function sum
Trigger not executing stored proc ...
Redundant Power Supply
BCP question
Information about an SQL server