I have a particular requirement where there is a particular column in a table that stores all the data types. This column is defined as nvarchar(max). This column holds values for datatypes BOOLEAN, DATE,TEXT, CURRENCY and NUMBER. There are other columns in this table on which I need to do group by. Based on the other columns we will only get one data type i.e CURRENCY or NUMBER or DATE or TEXT.
The sample data is attached. I want to pick maximum of currency, numbers and date based on what is the highest. for text max on character sorting.
Initially i tried
select datatype,max(value) from getmaxvalues group by datatype
but the above query gives the character max even for currency, numbers. For date it somehow works fine.
then i tried the below query. it gives conversion error
CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2))
END) AS MaxVal
GROUP BY DataType
I modified it as below. but still gives conversion error
CASE WHEN ISNUMERIC(VALUE)=1 THEN CAST(Value as DECIMAL(18,2))
WHEN ISDATE(VALUE)=1 THEN CAST(Value as DATE)
ELSE VALUE END)
group by datatype
I know that what I am trying to do is not the correct way but not sure on how to get this done.
Any help/pointers would be greatly appreciated