Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get single row from TVF

Status
Not open for further replies.

cwsstins

MIS
Aug 10, 2004
412
0
0
US
I got some help in another thread to create this table-valued function:

SQL:
CREATE FUNCTION dbo.GetAwdYrTbl  ()
RETURNS @AwdYrTbl TABLE (
        AwdYr VARCHAR(7)
      , YearID VARCHAR(2)
      , Nbr VARCHAR(1)
      )

BEGIN


Declare @TodayYr VARCHAR (4)
Declare @TodayMn VARCHAR (2)
Declare @2Years VARCHAR (4)
Declare @LastYr   VARCHAR (4)
Declare @NextYr VARCHAR (4)
Declare @AwdYr1 VARCHAR (7)
Declare @AwdYr2 VARCHAR (7)
Declare @AwdYr3 VARCHAR (7)
Declare @AwdYr4 VARCHAR (7)


SET @TodayYr = YEAR(getdate())
SET @TodayMn = MONTH(getdate())
SET @2Years = YEAR(getdate())-2
SET @LastYr = YEAR(getdate())-1
SET @NextYr = YEAR(getdate())+1

SET @AwdYr1 = @2Years + '-' + RIGHT(@LastYr,2)
SET @AwdYr2 = @LastYr + '-' + RIGHT(@TodayYr,2)
SET @AwdYr3 = @TodayYr + '-' + RIGHT(@NextYr,2)
SET @AwdYr4 = @NextYr + '-' + RIGHT(@NextYr + 1,2)

IF @TodayMn IN (1)
  INSERT INTO @AwdYrTbl 
  SELECT @AwdYr1,RIGHT(@AwdYr1,2),'1' UNION ALL SELECT @AwdYr2,RIGHT(@AwdYr2,2),'2'
ELSE IF @TodayMn IN (2,3,4)
  INSERT INTO @AwdYrTbl 
  SELECT @AwdYr1,RIGHT(@AwdYr1,2),'1' UNION ALL SELECT @AwdYr2,RIGHT(@AwdYr2,2),'2' UNION ALL SELECT @AwdYr3,RIGHT(@AwdYr3,2),'3'
ELSE INSERT INTO @AwdYrTbl
  SELECT @AwdYr2,RIGHT(@AwdYr2,2),'1' UNION ALL SELECT @AwdYr3,RIGHT(@AwdYr3,2),'2'
  
RETURN
END
GO

This works nicely if I run something that needs to include all the values produced:
select id from table where yr in (select AwdYr FROM dbo.GetAwdYrTbl ())

But I'm wondering how I can bring back only specific rows from the function...something like this:
select id from table where yr in (select AwdYr FROM dbo.GetAwdYrTbl () WHERE Nbr = 1)

I know the WHERE clause is not correct, but that is basically what I'm looking for. How to find specific row from the function based on the Nbr column from within the function.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top