Need QUERY HELP

Vignesh Kumar

12y1.6kSQL

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anupam Singh12y ago
Accepted 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 Kumar12y ago
Almost there, but this is the problem now
Conversion failed when converting the nvarchar value '$3,456' to data type int.
Anupam Singh12y ago
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 Kumar12y ago
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 Singh12y ago
Hi Vignesh,

You can use

select TotalCost ,MonthlyCost ,SharedCost, (TotalCost+1-SharedCost) as PreTotalCost , (MonthlyCost-SharedCost) as PostTotalCost
form YourTableName

Hope will help.