Can't get a text combined with % prompt value to work as needed

[ghbeers]

The below sql script is from my Crystal report that has one user prompt. The prompt can be (1) the wildcard %, (2) a combination of text and the wildcard % (eg WD%) or (3) the full value for the term being selected (eg 2014FA). I am having difficulty using sql to turn all of these options into the required result. I've messed with this using so many approaches and the one I currently have works for (1) and (3), but I can't get (2) to work.

Please just assume that @MYTERM is the prompt coming from the Crystal prompt. I need to select the latest TERM_END_DATE of all the terms selected like 'WD%' and assign it to @REPORTRUNDATE. I'm pretty sure that what I show for (2) below is way more complicated than necessary -- it yielded no errors, but it also did not yield any values when it should have. What am I missing to get that one date I need for option (2)?

DECLARE @MYTERM AS VARCHAR (100)
SET @MYTERM = 'WD%'

DECLARE @REPORTRUNDATE AS DATE
SET @REPORTRUNDATE =
( CASE
--(1)
WHEN @MYTERM = '%' THEN SYSDATETIME()
--(2)
WHEN (SELECT TOP 1 TT.TERM_END_DATE FROM TERMS TT WHERE TT.TERMS_ID LIKE @MYTERM ORDER BY TT.TERM_END_DATE DESC) IS NOT NULL
THEN (SELECT TOP 1 TTT.TERM_END_DATE FROM TERMS TTT WHERE TTT.TERMS_ID LIKE @MYTERM ORDER BY TTT.TERM_END_DATE DESC)

--(3)
ELSE (SELECT T.TERM_END_DATE FROM TERMS T WHERE T.TERMS_ID = @MYTERM)
END
)

Script continues to load a temporary table based on @REPORTRUNDATE, and the actual report query.

 

[fredericofonseca]

code for point 2 works fine in 2008 R2.

so its debug time for you.

what is the value of @REPORTRUNDATE after the set when you run with
@MYTERM = '%'
@MYTERM = 'WD%'
@MYTERM = 'WD' -- pick up a valid value here.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ghbeers]

The values are as follows

@MYTERM = '%', @REPORTRUNDATE is current date '2014-11-15' as it should be

@MYTERM = '2014FA', @REPORTRUNDATE is '2014-12-20' as it should be

@MYTERM = 'WD%', @REPORTRUNDATE is NULL.

@MYTERM = 'WD', which would be an invalid entry, @REPORTRUNDATE is NULL.

 

[ghbeers]

I need to add that when I just do the following it is returning the correct date. It's when I try to assign the same value to @REPORTRUNDATE, it is assigning null. Is this a date formatting problem?


DECLARE @MYTERM AS VARCHAR (100)
SET @MYTERM = 'WD%'
PRINT @MYTERM

SELECT TOP 1 TT.TERM_END_DATE FROM TERMS TT WHERE TT.TERMS_ID LIKE @MYTERM ORDER BY TT.TERM_END_DATE DESC

@REPORTRUNDATE = '2020-06-30 00:00:00.000'

 

[fredericofonseca]

what output you get here ?

declare @MYTERM varchar(10)
set @MYTERM = 'WD%'

declare @s1 varchar(100)
set @s1 = '==' + coalesce(convert(varchar(100),(select top 1 TT.TERM_END_DATE FROM @TERMS TT WHERE TT.TERMS_ID LIKE @MYTERM ORDER BY TT.TERM_END_DATE DESC)),'null date')
+ '==' + coalesce((select top 1 TT.TERMS_ID FROM @TERMS TT WHERE TT.TERMS_ID LIKE @MYTERM ORDER BY TT.TERM_END_DATE DESC),'invalid entry')
+ '=='
select @s1




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

and try (untested so there may be errors)

DECLARE @MYTERM AS VARCHAR (100)
SET @MYTERM = 'WD%'

DECLARE @REPORTRUNDATE AS DATE
SET @REPORTRUNDATE =
( CASE
  --(1)
  WHEN @MYTERM = '%' THEN SYSDATETIME()
  --(2)
  WHEN right(rtrim(@MYTERM),1) = '%' 
     then case
          when (SELECT TOP 1 TT.TERM_END_DATE 
                FROM TERMS TT 
                WHERE TT.TERMS_ID LIKE left(rtrim(@MYTERM),1,len(rtrim(@MYTERM)) - 1) + '%' 
                ORDER BY TT.TERM_END_DATE DESC) IS NOT NULL
          THEN (SELECT TOP 1 TT.TERM_END_DATE 
                FROM TERMS TT 
                WHERE TT.TERMS_ID LIKE left(rtrim(@MYTERM),1,len(rtrim(@MYTERM)) - 1) + '%' 
                ORDER BY TT.TERM_END_DATE DESC)
          else
          ELSE (SELECT T.TERM_END_DATE 
                FROM TERMS T 
                WHERE T.TERMS_ID = @MYTERM)
          end

  --(3) 
  ELSE (SELECT T.TERM_END_DATE 
      FROM TERMS T 
      WHERE T.TERMS_ID = @MYTERM)
  END
)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gk53]

use just

DECLARE @MYTERM AS VARCHAR (100)
SET @MYTERM = 'WD%'

DECLARE @REPORTRUNDATE AS DATE

if  @MYTERM = '%'
	select @REPORTRUNDATE = GETDATE()
else
begin
	select @REPORTRUNDATE = (SELECT TOP 1 TTT.TERM_END_DATE 
									FROM TERMS TTT 
									WHERE TTT.TERMS_ID LIKE @MYTERM 
									ORDER BY TTT.TERM_END_DATE DESC)
	
	select 		@REPORTRUNDATE = ISNULL(@REPORTRUNDATE,getdate())						
end

 

[ghbeers]

I ended up using the below syntax. What I previously was trying was close, but it complicated the translator a bit. Once I simplified it, it works correctly for all values in the term prompt. (WD%, %, %2014%, 2014FA, etc. Thank you all for your help. I have learned some interesting methods from the solutions you provide.

DECLARE @MYTERM AS VARCHAR (100)
SET @MYTERM = 'WD%'
PRINT @MYTERM

DECLARE @REPORTRUNDATE AS DATE
SET @REPORTRUNDATE =
( CASE
WHEN @MYTERM = '%' THEN SYSDATETIME()

WHEN (SELECT TOP 1 TERM_END_DATE FROM TERMS WHERE TERMS_ID LIKE @MYTERM ORDER BY TERM_END_DATE DESC) IS NOT NULL
THEN (SELECT TOP 1 TERM_END_DATE FROM TERMS WHERE TERMS_ID LIKE @MYTERM ORDER BY TERM_END_DATE DESC)
ELSE (SELECT T.TERM_END_DATE FROM TERMS T WHERE T.TERMS_ID = @MYTERM)
END
)