Click here to Skip to main content
15,037,420 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
S.No -> 1
Agent_code -> 1351
Branch -> BG
palmtec_id -> BG10
min_rec -> BG10000058
max_rec -> BG10000068
Last_billed -> 16-02-2016

I want to subtract 68-58 and store in third column

for ex: 68-58 =10

What I have tried:

I had tried (max_rec - min_rec) nut it doesnt subtract varchar there a way ti split the values and subtract ...can anyone help me out..thanks in advance
Updated 14-Mar-16 21:19pm
CHill60 14-Mar-16 9:56am
Instead of storing palmtec_id as part of min_rec and max_rec just do that bit in your presentation layer. Min_rec and max_rec then become integers and your subtraction becomes trivial.
By normalising your table you will also save on storage

SELECT CAST(REPLACE(max_rec, BRANCH, '') as INT) - CAST(REPLACE(min_rec, BRANCH, '') as INT)
select CAST(Right(@max_rec ,2) as int) - CAST(Right(min_rec ,2) as int)
Herman<T>.Instance 14-Mar-16 6:25am
What if the 2 digit must represent a number > 100?
AnantPithadiya 14-Mar-16 6:29am
select CAST(right(max_rec ,8) as int) - CAST(Right(min_rec ,8) as int)
Herman<T>.Instance 14-Mar-16 8:07am
NOPE. See my solution. BG10 in branchcode. I take that out.Remainder is INT.
alia007 15-Mar-16 3:53am
That works perfectly!!! Thanks a lot
Try this
SELECT (RIGHT(max_rec,2) - RIGHT(min_rec,2)) FROM table_name;
Herman<T>.Instance 14-Mar-16 6:26am
What if the 2 digit must represent a number > 100?
alia007 15-Mar-16 3:07am
SELECT (cast(RIGHT(max(Receipt_number),4) as integer)- cast(RIGHT(min(Receipt_number),4) as integer))
because I had split Receipt_number column as two columns min_rec and max_rec...henceforth this worked out

I got exact result as expected after using ths...
alia007 15-Mar-16 3:20am
thanks for all.. who helped me out !!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900