December 7, 2016 at 10:55 am
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-2
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?
December 7, 2016 at 11:07 am
dianerstein 8713 (12/7/2016)
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-2
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?
I do not fully understand your problem. Are you saying that you want to retain a single parameter, yet allow it to contain multiple values? Or that you wish to derive the string '15-16' from '16-17'? Something else?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 7, 2016 at 12:20 pm
dianerstein 8713 (12/7/2016)
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-2
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?
SELECT *
FROM YourTable
WHERE test1 LIKE LEFT(@test, 2) + '%'
OR test1 LIKE '%' + LEFT(@test, 2)
Frankly, the design of the entire thing isn't sounding like it's in very good shape, but if you can't get anyone to invest in making changes that would actually improve things, then the above code might get you where you need to go.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
December 7, 2016 at 12:21 pm
I only want 1 parameter value that can be obtained from the query where the parameter value = '16-17'. However I want to include logic that will also pick the previous school year. In this case the previous school year is value = '15-16'.
This is my goal since the logic is used in ssrs reports. If the parameter value would take in more than one value, the embedded subreports in the ssrs reports change would be extremely too high.
December 7, 2016 at 12:39 pm
dianerstein 8713 (12/7/2016)
I only want 1 parameter value that can be obtained from the query where the parameter value = '16-17'. However I want to include logic that will also pick the previous school year. In this case the previous school year is value = '15-16'.This is my goal since the logic is used in ssrs reports. If the parameter value would take in more than one value, the embedded subreports in the ssrs reports change would be extremely too high.
Have you seen my prior post? You were probably posting at around the same time as I was, and mine happened to hit first.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
December 7, 2016 at 1:02 pm
dianerstein 8713 (12/7/2016)
I only want 1 parameter value that can be obtained from the query where the parameter value = '16-17'. However I want to include logic that will also pick the previous school year. In this case the previous school year is value = '15-16'.This is my goal since the logic is used in ssrs reports. If the parameter value would take in more than one value, the embedded subreports in the ssrs reports change would be extremely too high.
Here is a horrible hack to derive the second string to be used in your WHERE condition:
DECLARE @test1 VARCHAR(10) = '16-17';
DECLARE @test2 VARCHAR(10) = STUFF(CAST(CAST(REPLACE(@test1, '-', '') AS INT) - 101 AS VARCHAR(9)), 3, 0, '-');
SELECT
, @test2;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 7, 2016 at 1:44 pm
Here's an approach similar to Phil's, but both will fail if the value starts with '0'.
DECLARE @test1 VARCHAR(10) = '16-17';
DECLARE @test2 VARCHAR(10) = CAST( LEFT( @test1, 2) - 1 AS char(2)) + '-' + LEFT( @test1, 2);
SELECT
, @test2;
You could avoid a variable, but that might trick the optimizer.
declare @test1 varchar(10) = '16-17'.
select * from test1 IN( @test-2, CAST( LEFT( @test-2, 2) - 1 AS char(2)) + '-' + LEFT( @test-2, 2))
December 7, 2016 at 7:07 pm
declare @Year varchar(10) = '16'.
select *
FROM test1 t1
INNER JOIN test t2 WHERE t2.col LIKE '%-' + @Year
WHERE t1.Col LIKE @Year + '-%'
_____________
Code for TallyGenerator
December 7, 2016 at 7:12 pm
But better go with this:
declare @Year int = 16
declare @ThisYear varchar(10), @LastYear varchar(10)
SELECT @ThisYear = CONVERT(VARCHAR(10), @Year) + '-' + CONVERT(VARCHAR(10), @Year + 1),
@LastYear = CONVERT(VARCHAR(10), @Year-1) + '-' + CONVERT(VARCHAR(10), @Year)
PRINT @ThisYear PRINT @LastYear
select *
FROM test1 t1
INNER JOIN test t2 on t2.col = @LastYear
WHERE t1.Col = @ThisYear
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply