SQLTeam.com | Weblogs | Forums

T-sql 2012 possible join to same table


#1

In a t-sql 2012 column I have a column called test1 defines as (varchar(10),not null) in a table called tab. The values in this column look like '16-17' and '15-16'.

Right now the logic looks like
declare @test1 varchar(10) = '16-17'.
select * from test1 = @test

This logic is in an ssrs report where it would be too much trouble to change the parameter values due to the embedded subreports.

Now I need to select data where rows can equal '15-16' and '16-17'. Basically I need to pick the current school year ('16-17') and the previous school year('15-16').

Thus could you show me the t-sql on how to accomplish this goal without changing the parameter value logic?


#2

Would this do?

declare @test1 varchar(10) = ',15-16,16-17,'.
select * from MyTable where @test LIKE '%,' + test1 + ',%'

it will probably run like a dog though :frowning:


#3

No that will not do. I have to keep the parameter logic the same.


#4

Maybe:

DECLARE @test1 varchar(10) = '16-17';
SELECT *
FROM YourTable
WHERE
(
    test1 = @test1
    OR test1 = RIGHT('0' + CAST(CAST(LEFT(@test1, 2) AS int) - 1 AS varchar(10)), 2) + '-' + LEFT(@test, 2)
);

or

DECLARE @test1 varchar(10) = '16-17';
SELECT *
FROM YourTable
WHERE test1 = @test1
UNION ALL
SELECT *
FROM YourTable
WHERE test1 = RIGHT('0' + CAST(CAST(LEFT(@test1, 2) AS int) - 1 AS varchar(10)), 2) + '-' + LEFT(@test, 2);

#5

I very much doubt that you can, if not then you have two options: the most optimal query or the smallest possible change to the code (which you might be able to implement with a global Find and Replace, ratyer than doing them all manually by hand)

My suggestion was on the basis of the later option ...


#6

Hmmm ... you could do

declare @test1 varchar(10) = '16-17', @test2 varchar(10) = '15-16'
INSERT INTO #TEMP
select * from MYTABLE WHERE test = @test1
INSERT INTO #TEMP
select * from MYTABLE WHERE test = @test2

dunno if that is an option (i.e. store the results from two separate iterations of the query, and then display those)


#7

At some point in time - which you define - your query needs to include results for both the current year and the previous year. After some point in time - you probably want the query to only return the current year selected...

I would solve this in my code using something along the lines of:

Declare @previousYear char(5);
If {condition to include previous year}
Begin
     Select @previousYear = {year value} From dbo.{year definition table} Where {year value} < @currentYear;
End
Else
Begin
     Set @previousYear = ''
End

Select *
From dbo.MyTable
Where test in (@currentYear, @previousYear);

If you don't have a table that defines each year - then you have to calculate the previous year value from the current year value. Your condition for including the previous year probably is based on current date - so it could be as simple as

IF month(current_timestamp) > '03'

An alternative method would be to include that check directly:

Select *
From dbo.MyTable
Where test = @currentYear
Or (month(current_timestamp) > '03' And test = @previousYear);

In the above you would still need to define @previousYear either through a calculation or a lookup.