I got some help in another thread to create this table-valued function:
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.
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.