Need QUERY HELP

Question

Hi,

TABLE 1
TotalCost MonthlyCost SharedCost
$ 120          $10             $ 213
$ 240           $20            $ 256
I need to calculate these column like this
sum(TotalCost+1-SharedCost) as PreTotalCost
sum(MonthlyCost-SharedCost) as PostTotalCost

My expected output

TotalCost MonthlyCost SharedCost PreTotalCost PostTotalCost
$ 120             $10             $ 213           $ -92             $ -203
$ 240             $20             $ 256           $ -15            $ -236

Really appreciate any help on this.

Anupam Singh · Answer

My suggestion is , you should use globalization for this kind of scenario. Anyways you may have another reason, You have to use replace function in sql or if you doing same in C# there is also Replace method. Replace : replace(varchar_data,'$','') so after using replace you must have to convert it to int or money datatype using cast(varchar_type_after_relace as money) in sql. All the best !

Vignesh Kumar · Answer

Almost there, but this is the problem now Conversion failed when converting the nvarchar value '$3,456' to data type int.

Anupam Singh · Answer

Okey. Dont worry Vignesh , try this then : select ('$ '+cast( TotalCost as nvarchar(1000))) as TotalCost , ('$ '+cast( ( TotalCost+1- SharedCost ) as nvarchar(1000))) form YourTableName note : it is not tested, so please test it on your own. do the same will all selected column accordingly.. actually type of columns is int so we have to convert these to nvarchar the we can concatenate using + operator .. Hope will help..

Vignesh Kumar · Answer

Hi Anupam, But I need to get the result including $ and money datatype. That is the place where I'm facing problems. t1 and t2 are seperate tables. This is my try so far, on top of this I need to sum and bring values in money datatye and $ at the front. Also when I run this I get an error. Operand data type nvarchar is invalid for subtract operator. parsename ( '$' + Convert ( varchar , Convert ( money , t . [ TotalCost] +1 - t1 . [Shared Cost] ), 1 ), 2 ) as PreTotalCost parsename ( '$' + Convert ( varchar , Convert ( money , t . [ MonthlyCost] - t1 . [Shared Cost] ), 1 ), 2 ) as PostTotalCost

Anupam Singh · Answer

Hi Vignesh , You can use select TotalCost , MonthlyCost , SharedCost, ( TotalCost+1- SharedCost ) as PreTotalCost , (MonthlyCost-SharedCost) as PostTotalCost form YourTableName Hope will help.