How to concatenate two values while adding zeros to the right in the second value?-CodePudding

I need to concatenate the values of the two columns like VALUE_A.VALUE_B in SQL Server 2014, but the second value (VALUE_B) must have 4 decimals completing with 0 to the right, for example:

First row = 2031323.2200
Second row = 3371331.7500

VALUE_A	VALUE_B	ID
2031323	22	1
3371331	75	1
4399213	52	1
4910001	46	1
2163036	38	1
1514817	91	1
2786338	36	1
3724760	35	1
3769556	13	1
3812410	28	1
4415600	54	1
1018894	95	1
3870688	43	1
3702609	87	1
3410337	79	1
1452983	52	1
3929430	69	1
1732252	44	1
4145563	85	1
4489401	48	1
1029801	20	1
4799841	47	1
3958121	84	1
2014221	02	1
6023489	08	1
348325	29	1
2803	86	1
56077	00	1

I will need to use this concatenated value in another query to make operations, for example:

SELECT 
    VALUE_C   (CONVERT(float, CONCAT(RTRIM(CONVERT(char, VALUE_A)), '.' , RTRIM(CONVERT(char, VALUE_B))) 
FROM TABLE X

If the VALUE_C is 10.5100, the operation should be "10.5100 2031323.2200".

Another problem I'm facing with the operation is checking the SUM of the concatenated value per ID, the correct SUM for ID = 1 should be 68220986.6800, but it's returning 68164910.580000.

Example:

SELECT
    SUM(CONVERT(decimal(15, 4), CONVERT(varchar(10), VALUE_A)   '.'   CONVERT(varchar(4), VALUE_B)   '00')) 
FROM X 
WHERE ID = 1

Here's the SUM of the same data but on Excel:

CodePudding user response：

Not totally clear on your requirements but something like this should be close enough.

select convert(decimal(15,4), convert(varchar(10), VALUE_A)   '.'   convert(varchar(4), VALUE_B)   '00')

CodePudding user response：

One possible solution which appears to give the right answer. It assumes that the base data types for VALUE_A and VALUE_B are in the int family, as in the dbfiddles linked in the comments:

SELECT
    SUM(CONVERT(decimal(20,4), VALUE_A   (VALUE_B * 0.01)))
FROM X 
WHERE ID = 1

dbfiddle

(also assumes that the figures in the OP are incorrect, and rows 20-24 should have the last digit of VALUE_A discarded)

CodePudding user response：

I think that what you need is something like this. But be careful, it only works if value_b is always 2 digits:

DECLARE @start TABLE (
    VALUE_A INT,
    VALUE_B INT,
    ID INT
)

INSERT INTO @start VALUES
  (2031323,     22,     1)
, (3371331,     75,     1)
, (4399213,     52,     1)
, (4910001,     46,     1)
, (2163036,     38,     1)
, (1514817,     91,     1)
, (2786338,     36,     1)
, (3724760,     35,     1)
, (3769556,     13,     1)
, (3812410,     28,     1)
, (4415600,     54,     1)
, (1018894,     95,     1)
, (3870688,     43,     1)
, (3702609,     87,     1)
, (3410337,     79,     1)
, (1452983,     52,     1)
, (3929430,     69,     1)
, (1732252,     44,     1)
, (4145563,     85,     1)
, (4489401,     48,     1)
, (1029801,     20,     1)
, (4799841,     47,     1)
, (3958121,     84,     1)
, (2014221,     02,     1)
, (6023489,     08,     1)
, (348325,  29,     1)
, (2803,    86,     1)
, (56077,   00,     1)

select * from @start

DECLARE @tosum TABLE (
                vals decimal(18, 4)
            )
DECLARE @value_C decimal(18,4) = 10.5100

insert into @tosum (vals) select ((value_A   (value_B * 0.0100))  value_C) from @start where id = 1 

Select * from @tosum
select totalsum = sum(vals) from @tosum