TSQL: How do I combine two values (money/int) using UNION ALL without changing the data?

Question

I have the following Query:

create table #Result (Reward varchar(40), Value MONEY);

insert #Result exec GetCurrentCycleQualifierStatusByAccountId @AccountId=76011;

with cteFirstResults as
(select Reward, round(Value,2) as Value from #Result where Reward like '%Balance%'), 
cteSecondResults as
(select Reward, convert(INTEGER, Value) as Value from #Result where Reward NOT like    '%Balance%')

select * from cteFirstResults 
UNION ALL
select * from cteSecondResults;

drop table #Result;

When running a select * individually against each "cte" table, I get the results I want.
But when run all together, I get something like:

Reward          Value
------          -----
Daily Balance   4709.00
Value A         1.00
Value B         9.00

I want the Value A/Value B data to show without any decimal values as they do when running a select against the table directly. How do I combine the two queries into one to show this data correctly?

Round(value,0) does nothing.

I can not change the sproc from which I'm gathering the data, but I can make the temp table any way I like.

Thanks,

Jason

The solution:

create table #Result (Reward varchar(40), Value MONEY);
insert #Result exec GetCurrentCycleQualifierStatusByAccountId @AccountId=76011;
With cteFirstResults as
(
Select Reward, Value
From #Result 
Where Reward like '%Balance%'
)
,  cteSecondResults as
(
Select Reward, cast(Value as int) as Value
From #Result 
Where Reward Not like '%Balance%'
)
Select Reward, Cast( Value As varchar(max)) As Value
From cteFirstResults 
Union All
Select Reward, Cast( value As varchar(max)) as Value
From cteSecondResults;
drop table #Result;

Answer 1

In a union SQL Server will assume that the datatype of the second select is the same as the first and where it can convert them, do so. You will have to beat it at its own game and do you own conversion

In the final select (the one with the Union) massage the data in both cases to be a string. Format the output as desired before converting it to a string.

Answer 2

The problem is the integers are being implicitly cast to decimal because they are being represented in a decimal column.

If you just want the values displayed, cast them both to strings.

    CREATE TABLE #test
(
    test decimal(9,2)
)

CREATE TABLE #test2
(
test int
)

INSERT INTO #test (test)
SELECT 1.25 UNION ALL
SELECT 172813.99

INSERT INTO #test2 (test)
SELECT 134 UNION ALL
SELECT 41

SELECT CAST(test as  varchar(max)) FROM #Test
UNION ALL
SELECT CAST(test as  varchar(max)) FROM #Test2

Results:

1.25
172813.99
134
41

Answer 3

EACH Sql Server column can only have 1 data type.

• round(money,4) returns a money(4)*
• convert(int) returns an int

Based on data type precedence

• #13. money
• #16. int

The resultant column is money(4). Therefore ALL values in the column will be formatted using money(4).

Your options are

1. convert(float) across both - downside: a value of 1.1 is shown as 1.1, not 1.10
2. convert(varchar) - you have stated you don't want this, and it changes the data type to the receiving program

FWIW

Round(value,0) does nothing.

It does do something. It burns CPU rounding an int value to another int value (of the same value). Incidentally, the resultant type is (still) "int". This has nothing to do with formatting.

REF:

declare @m money
set @m = 12.3233
select SQL_VARIANT_PROPERTY(round(@m,2), 'basetype')   -- money
select SQL_VARIANT_PROPERTY(round(@m,2), 'precision')  -- 19
select SQL_VARIANT_PROPERTY(round(@m,2), 'scale')      -- 4