March 1, 2011 at 12:38 pm
Hi,
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
SELECT
DataType,MAX(
CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2))
ELSE
Value
END) AS MaxVal
FROM GetMaxValues
GROUP BY DataType
I modified it as below. but still gives conversion error
select
DataType,MAX(
CASE WHEN ISNUMERIC(VALUE)=1 THEN CAST(Value as DECIMAL(18,2))
WHEN ISDATE(VALUE)=1 THEN CAST(Value as DATE)
ELSE VALUE END)
FROM
GetMaxValues
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
Thanks,
Ravi
March 1, 2011 at 1:04 pm
First off...yes this is the WRONG way to do things.
You won't be able to do it in a single field, but you can do it with multiple fields (i think):
Using your code sample, try the following for each type of field you want:
SELECT
DataType,
MAX(
CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2))
ELSE NULL END) AS MaxNumber,
MAX(
CASE WHEN DataType in ('Date') THEN CAST(Value AS Datetime)
ELSE NULL END) AS MaxDate,
FROM GetMaxValues
GROUP BY DataType
Hope this works!
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 1, 2011 at 1:16 pm
Thanks for the quick reply.
That works. but the problem is I am getting the multiple columns. I think I need to figure out a way to merge these multiple columns in to one.
Appreciate your help anyway.
Thanks,
Ravi
March 1, 2011 at 1:26 pm
ok then...do this... (use the query i sent you as a subquery...then collapse the columns) -- typing as i think
SELECT DataType,
MAX(CASE
WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(MaxNumer as varchar)
WHEN DataType in ('Date') THEN CAST(MaxDate as varchar)
ELSE NULL END) as MaxFieldValue
FROM (
SELECT
DataType,
MAX(
CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2))
ELSE NULL END) AS MaxNumber,
MAX(
CASE WHEN DataType in ('Date') THEN CAST(Value AS Datetime)
ELSE NULL END) AS MaxDate,
FROM GetMaxValues
GROUP BY DataType
) T1
GROUP BY DataType
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 1, 2011 at 1:41 pm
Thanks once again. I was figuring out that and your post helped me.
I have tweaked it a bit as max is working fine for date's stored in nvarchar column
here is the final one.
SELECT DATATYPE,ISNULL(MAXNUMBER,'')+ISNULL(MAXVALUE,'')
FROM
(
SELECT
DataType,
CAST(
MAX(
CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2))
ELSE NULL END)
AS NVARCHAR(MAX)
)
AS MaxNumber,
MAX(CASE WHEN DataType NOT in ('CURRENCY','NUMBER') THEN VALUE ELSE NULL END) as MAXVALUE
FROM GetMaxValues
GROUP BY DataType
) A
Thanks,
Ravi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply