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?
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);
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 ...
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)
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.