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 '$3456' 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.