How to subtract two varchar columns and return the result in third column

Other SQL Server Topics
1

col_1 col_2 result

BG10000068 BG10000065 (65+66+67+68) -> 4

2

is it always first 2 characters of the data is string ?

4

its varchar..is it possible to subtract

5

yea the first two,characters always a string

for ex:

col_1 , col_2 , result

BG10000068 , BG10000065 , (65+66+67+68) -> 4

BG11000017 , BG11000019 , (17+18+19)-> 3

6

CONVERT(INT, STUFF(col_2, 1, 2, '')) - CONVERT(INT, STUFF(col_1, 1, 2, ''))

7

Min(Receipt_number)min_rec,Max(Receipt_number )max_rec

Receipt_number is a single column from which i had took max and min and split as two different columns already.. now the problem is when i use the above code as u suggested it throws an error as invalid column since the new split column names are min_rec and max_rec ... please help me

8

Msg 207, Level 16, State 1, Line 3
Invalid column name 'min_rec'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'max_rec'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'min_rec'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'max_rec'.

This is the error am getting after splitting a single column as two columns and again trying to subtract from the splitted columns...is this a correct way or am wrong somewhere

9 
CONVERT(INT, STUFF(Max(Receipt_number), 1, 2, '')) 
- CONVERT(INT, STUFF(Min(Receipt_number), 1, 2, ''))
10

1351, BG, BG10, REMS, BG10000065 ,BG10000065 ,2016-02-01 ,0
1351, BG ,BG10, REMS ,BG10000066, BG10000066, 2016-02-08, 0

i get 0 as result if I do that

11

select Agent_code,branch_code,tbl_pid,company_id,Min(Receipt_number)min_rec,Max(Receipt_number )max_rec,
Max(cast(collection_date as varchar)) as last_billed,CONVERT(int,STUFF(max_rec,1,2,''))-CONVERT(int,STUFF(min_rec,1,2,''))as received from testof_createddate
where collection_date between '2016-02-01' AND '2016-02-29' and company_id='REMS' group by Agent_code,branch_code,tbl_pid,min_rec,max_rec,company_id
order by branch_code,tbl_pid

this is my query...am I wrong anywhere?

12

it subtracts 3rd digit and returns the result...wherein i just need last two digits to get subtracted

13 
select    
    Agent_code,
    branch_code,
    tbl_pid,
    company_id,
    Min(Receipt_number)min_rec,
    Max(Receipt_number )max_rec,
    Max(cast(collection_date as varchar)) as last_billed,
    CONVERT(int,STUFF(Min(Receipt_number),1,2,''))
    - CONVERT(int,STUFF(Max(Receipt_number),1,2,''))as received 
from    testof_createddate
where   collection_date between '2016-02-01' AND '2016-02-29' 
and     company_id='REMS' 
group by Agent_code, 
     branch_code,
     tbl_pid,
     company_id
order by branch_code,
     tbl_pid
1 Like
14

thanks a lottttttt !!!!!!

i got it right