I have a situation where I need to total by similar but not exact strings. Example data:
Cust ID Val 1 Val 2 Val 3
448 20 30 40
448-01 30 40 50
448-02 60 70 80
What I want is one line totalling for 448 to be 110,140 and 170
The dash may not be in the third postion so I want to locate its position and then total the group.
My code below does not work because there are customer ID's that have no dash and the query bombs. How can I adjust the structure to produce the desired results.
where custnmbr like '448%'
group by left(custnmbr,patindex('%-%',custnmbr)-1)