Hi,
I'm trying to do the following, but am getting errors because (obviously) SUM doesn't work with data types of nvarchar.
SELECT
SUM(CASE WHEN FieldName = 'SPECIFIC' THEN Tolerance ELSE '' END) AS 'Specific Tolerance'
FROM FIELD_TOLERANCE
GROUP BY Area
Tolerance holds values such as '100 +/- 25'. Obviously the first thought would be to seperate the two parts '100' and '25' into seperate fields and then have the program reconstruct it. Unfortunatly sometimes the value is odd, such as '100 +10 -25' (meaning a range of 75 - 110 with a target of 100).
Is there any way to put effectively sum up the Tolerance. Also, I know for a fact the FieldName 'SPECIFIC' will only be in the database once for each area.
Thanks,
RyanI searched Books Online for aggregate functions as well as just functions. I found nothing listed under either which would help.
Should I create a function for this task? How would I create a function to do this?
Thanks,
Ryan|||We no longer need to do what I was asking about. But is there a way? I'm curious.|||If there is only one 'SPECIFIC' value per area, are you really adding anything together? Not being in your field, I am having a problem getting my mind around adding tolerances together. It may be that sum is not the right function for this application. What is the result set you want in the end?|||Originally posted by MCrowley
If there is only one 'SPECIFIC' value per area, are you really adding anything together? Not being in your field, I am having a problem getting my mind around adding tolerances together. It may be that sum is not the right function for this application. What is the result set you want in the end?
well I have a table like so (dashes inserted for web formating purposes):
Area--Field--Tolerance
1----FieldA--100 +/- 12
1----FieldB--100 +/- 13
2----FieldA--97 +3 -7
2----FieldC--95 +/- 5
Area type = int
Field type = varchar
Tolerance type = varchar
I want the results I want are as follows (dashes inserted for web formating purposes):
Area--FieldATolerance--FieldBTolerance--FieldCTolerance
1----100 +/- 12---100 +/-13
2----97 +3 -7----------95 +/- 5
This way I can pass the values for field I want the tolerances for and get them all back in one record. This allows the Tolerance table to hold tolerances for different fields, yet make retrieving the tolerances easy.
-Ryan
ps. Thanks for the reply
Thursday, March 8, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment