Buried in a sea of Casts

[Moebius01]

Ok, this is mostly just a self educational project, but I'm trying to see if this could be streamlined. The field this is reporting on contains 3 statuses, 0,1, and 2 for not started, incomplete, and complete respectively. The idea is to output a count/total % style number for each. So the output looks something like this.

Not Started   Incomplete   Complete
(34/100 34%)  (20/100 20%) (46/100 46%)

After dealing with tons of error converting to int type situations and throwing lots of Casts into the mix, I arrived at this:

SELECT 
	'(' + CAST(SUM(CASE STR.SelectionStatus WHEN 0 THEN 1 ELSE 0 END) AS VARCHAR(3)) + '/' + CAST(COUNT(*) AS VARCHAR(3)) 
	+ ') ' + CAST(CAST(CAST(SUM(CASE STR.SelectionStatus WHEN 0 THEN 1 ELSE 0 END) AS FLOAT) / CAST (COUNT(*) AS FLOAT)*100 AS DECIMAL(5,1)) AS VARCHAR(4)) + '%'  AS [Not Started],
	'(' + CAST(SUM(CASE STR.SelectionStatus WHEN 1 THEN 1 ELSE 0 END) AS VARCHAR(3)) + '/' + CAST(COUNT(*) AS VARCHAR(3)) 
	+ ') ' + CAST(CAST(CAST(SUM(CASE STR.SelectionStatus WHEN 1 THEN 1 ELSE 0 END) AS FLOAT) / CAST (COUNT(*) AS FLOAT)*100 AS DECIMAL(5,1)) AS VARCHAR(4)) + '%'  AS [Incomplete],
	'(' + CAST(SUM(CASE STR.SelectionStatus WHEN 2 THEN 1 ELSE 0 END) AS VARCHAR(3)) + '/' + CAST(COUNT(*) AS VARCHAR(3)) 
	+ ') ' + CAST(CAST(CAST(SUM(CASE STR.SelectionStatus WHEN 2 THEN 1 ELSE 0 END) AS FLOAT) / CAST (COUNT(*) AS FLOAT)*100 AS DECIMAL(5,1)) AS VARCHAR(4)) + '%'  AS [Complete]
FROM SelectionStatus STR

There are some extra joins and such, but this is the base of what I'm trying to learn. Can this be cleaned up much?

 

[markros]

This is too hard to read and understand.

1. Do you need to return the formatted information to the client or you can do formatting on the client instead?

2. If the former, then, at least, put the logic of getting sums and counts into CTE/Derived table and then use them for your casts

 

[bborissov]

I wold do this that way:

SELECT SUM(CASE STR.SelectionStatus 
                WHEN 0 THEN 1
                ELSE 0 END) AS NotStarted,
       SUM(CASE STR.SelectionStatus 
                WHEN 1 THEN 1
                ELSE 0 END) AS Incomplete,
       SUM(CASE STR.SelectionStatus 
                WHEN 2 THEN 1
                ELSE 0 END) AS Complete,
       COUNT(*) AS Total
FROM .....

And then do ALL the calculations and presentation of the data with the FrontEnd.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Yes, my thoughts exactly.