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 columns..is there a way ti split the values and subtract ...can anyone help me out..thanks in advance
Posted
Updated 14-Mar-16 21:19pm
v2
Comments
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

SQL
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)
   
Comments
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;
   
Comments
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