Sep 29, 2010 04:37 PM|maverik187|LINK
Okay, so I have a little problem developing a SQL Select Statement on a column that is formatted pretty badly. Firstly, the Database has already been created and filled with Data in a Test Environment, I'm not sure if the format is the same or has been updated
in Production but since I'm only able to work with the Test Environment right now I'm stuck with using what I have. The Column in one Table shows a Snapshot of a clients account which is set to VarChar(50) but the actual data in the column is formatted like
a money format (with a $ and decimal point so for example "$5,000.00" would be an entry in one Row).
What I'm trying to do is a create a WebService that will be used in a iPhone Application and one of the Methods needs to just give a quick display of the total value of all Clients with each specific company but I can't perform a SUM function on a VarChar column
and I've tried to CAST and CONVERT the column into money, int, decimal and just about every format I can think of but I keep getting an error stating that I convert the data type varchar to which ever one I'm trying. I think it has something to do with the
$ already listed in the Column, so I wanted to know what I could do to either remove the $ sign (like a Trim option) or if there is a better way of doing this that I can't think of. I know the best option would be to convert the column to the appropriate format
but as I said, this was a previously designed system and I'm not sure if the Production area is the same (most likely not).
Here's what I've tried so far:
SELECT holding_company, COUNT(*) AS 'Total Accounts', SUM(CAST(snapshot AS DECIMAL(14, 2))) AS 'Total Value'
WHERE (agent_id = '114558')
GROUP BY holding_company
ORDER BY holding_company