I need to take the value and make it say ">30 Days"

I need to take the value and make it say ">30 Days"

Here is what my statement looks like

CASE WHEN Cast(CHEMVAL.value AS NUMERIC(5, 1)) = 0 THEN Cast(CHTKVOL.value AS NUMERIC(5, 1)) ELSE Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1)) END as [Days Left at Current Rate]

 if the value is 30 or greater just display ">30 Days"
Sam RunyonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
I think a CASE in the CASE can be added like:

CASE WHEN Cast(CHEMVAL.value AS NUMERIC(5, 1)) = 0 
	THEN Cast(CHTKVOL.value AS NUMERIC(5, 1)) 
	ELSE 
	CASE WHEN (Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1)) ) >= 30 
		THEN ‘>30 Days’  ELSE Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1)) END
END as [Days Left at Current Rate]

Open in new window

0
Scott PletcherSenior DBACommented:
That will fail because you can't convert the string ">30 Days" to a numeric value.  You'd need to cast the computation to a string to avoid that issue.
0
Sam RunyonAuthor Commented:
Scott could you help with the context of the problem.. I am new.. learning at a slow place. :)

Sam
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
That will fail because you can't convert the string ">30 Days" to a numeric value.  You'd need to cast the computation to a string to avoid that issue.
reason being some of the rows still returning numeric values (for those < 30), hence it will prompt with a converting error.

if the value is 30 or greater just display ">30 Days"

it should shown as ">=30 Days" , inclusive of 30.

something like this should worked:

CASE WHEN 
	CASE WHEN Cast(CHEMVAL.value AS NUMERIC(5, 1)) = 0 THEN 
		Cast(CHTKVOL.value AS NUMERIC(5, 1)) 
	ELSE 
		Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1)) 
	END >= 30 THEN '>= 30 Days'
ELSE
	concat(cast(
		CASE WHEN Cast(CHEMVAL.value AS NUMERIC(5, 1)) = 0 THEN 
			Cast(CHTKVOL.value AS NUMERIC(5, 1)) 
		ELSE 
			Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1)) 
		END
	as varchar(100)) , ' Days')
END	as [Days Left at Current Rate]

Open in new window

0
Mark WillsTopic AdvisorCommented:
You cant mix-n-match data types in a column....

So if you want a string, then any result in that column must also be a string (varchar)

   CASE WHEN Cast(CHEMVAL.value AS NUMERIC(5, 1)) = 0 
        THEN case when Cast(CHTKVOL.value AS NUMERIC(5, 1)) > 30 
                  then '>30 Days' 
                  else cast(Cast(CHTKVOL.value AS NUMERIC(5, 1)) as varchar) 
                  end  
        ELSE case when Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1)) > 30 
                  then  '>30 Days' 
                  else cast( Cast(CHTKVOL.value AS NUMERIC(5, 1)) / Cast(CHEMVAL.value AS NUMERIC(5, 1)) as varchar) 
                  end  
        END as [Days Left at Current Rate]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vadim RappCommented:
Your choices are:

1. either you generate the complete string you want to show in the end in SQL; i.e. currently you probably append the word "days" somewhere in the UI or report, wherever it goes into in the end. So now you'd have to remove that, generate the complete string in SQL, and in the UI show only what came from SQL

or

2. Generate those ">30 days" in your report or UI rather than in SQL.

#2 is probably more straightforward. If you tell what is that end-user UI, we can tell more specifically how to achieve that. For example, in SSRS report it would be easily achieved by an expression like =Iif(DaysLeftatCurrentRate.Value<=30, DaysLeftatCurrentRate.Value, ">30 days")
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.