Help with counting/percentage calculation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with counting/percentage calculation

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2007-02-15 : 07:15:33

I have a query which returns a number of rows, one field shows either 'Complete' or 'Scheduled'. I need to be able to calculate a total # of each, as well as a "percent Complete". If possible, I'd like to have the results show up as line items in the recordset.

Here's the query as I have it thus far:


SELECT DISTINCT
TaskDates.Customer,
TaskDates.Status,
TaskDates.TaskID
FROM TaskDates
WHERE
DateValue > '02/01/2007'
AND
DateValue < '02/28/2007'
AND
TaskDates.Type = 'Preventive Maintenance'
AND
(TaskDates.Status = 'Scheduled'
OR
TaskDates.Status = 'Complete')
ORDER BY
TaskDates.Status,
TaskDates.Customer

Any ideas on how to get the counts as well as the percentage (as in "percent complete") to show up at the bottom of my result set?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 07:35:19

Try this

SELECT		Customer,
		SUM(CASE WHEN [Status] = 'Sceduled' THEN 1 ELSE 0 END) AS Scheduled,
		SUM(CASE WHEN [Status] = 'Complete' THEN 1 ELSE 0 END) AS Complete,
		1.0 * SUM(CASE WHEN [Status] = 'Complete' THEN 1 ELSE 0 END) / COUNT(*) AS [Percent complete]
FROM		TaskDates
WHERE		DateValue >= '02/01/2007'
		AND DateValue < '03/01/2007'
		AND [Type] = 'Preventive Maintenance'
		AND [Status] IN ('Scheduled', 'Complete')   -- Is this necessary?
GROUP BY	Customer
ORDER BY	Customer

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 07:45:37

[code]SELECT Customer,
SUM(CASE WHEN [Status] = 'Sceduled' THEN 1 ELSE 0 END) AS Scheduled,
SUM(CASE WHEN [Status] = 'Complete' THEN 1 ELSE 0 END) AS Complete,
SUM(CASE WHEN [Status] NOT IN ('Sceduled', 'Complete') THEN 1 ELSE 0 END) AS Other,
1.0 * SUM(CASE WHEN [Status] = 'Complete' THEN 1 ELSE 0 END) / SUM(CASE WHEN [Status] IN ('Sceduled', 'Complete') THEN 1 ELSE 0 END) AS [Percent complete of complete and scheduled],
1.0 * SUM(CASE WHEN [Status] = 'Complete' THEN 1 ELSE 0 END) / COUNT(*) AS [Percent complete of total]
FROM TaskDates
WHERE DateValue >= '02/01/2007'
AND DateValue < '03/01/2007'
AND [Type] = 'Preventive Maintenance'
GROUP BY Customer
ORDER BY Customer[/code]

Peter Larsson
Helsingborg, Sweden

sbt1
Yak Posting Veteran

89 Posts

Posted - 2007-02-15 : 09:26:05

Wow, thanks a lot. Only one problem, I get a divide-by-zero error. If I remove the lines where the division is done, the rest executes fine.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 09:55:23

With the first suggestion, you can't get a DIVIDE BY ZERO error, becuase as long as there is at least one record for the customers, COUNT is 1.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources