SQL Query Help and Percentages

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

General SQL Server Forums

New to SQL Server Programming

SQL Query Help and Percentages

Author

Topic

kofseattle_ken
Starting Member

2 Posts

Posted - 2009-06-04 : 17:28:07

Hello,

I have a table that includes NAME, CurrentDueDate and CompletionDate (simplified). I need to write a qry that groups the data by "NAME" and then tells me the count and percentage of instances for each NAME where the Completion Date occured AFTER the CurrentDueDate. I also need to know the percentage of those that occured on or before that CurrentDueDate. I hope this makes sense. Any help would be greatly appreciated!

Thanks K

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-04 : 19:39:39

See if this helps:

DECLARE @T TABLE (NAME VARCHAR(10), CurrentDueDate DATETIME, CompletionDate DATETIME)
INSERT INTO @T
SELECT 'Name1', '2/1/2009', '1/1/2009' UNION ALL
SELECT 'Name2', '2/1/2009', '3/1/2009' UNION ALL
SELECT 'Name3', '4/1/2009', '2/1/2009' UNION ALL
SELECT 'Name1', '1/1/2009', '12/31/2008' UNION ALL
SELECT 'Name3', '3/1/2009', '4/1/2009' UNION ALL
SELECT 'Name2', '5/1/2009', '6/1/2009' UNION ALL
SELECT 'Name2', '2/1/2009', '1/1/2009' UNION ALL
SELECT 'Name4', '3/1/2009', '4/1/2009'

SELECT [NAME],
CountComplete = COUNT(CompletionDate),
LateCount = COUNT(CASE WHEN CompletionDate > CurrentDueDate THEN 1 END),
LatePercent = 100.0*(1.0*COUNT(CASE WHEN CompletionDate > CurrentDueDate THEN 1 END)/(COUNT(CompletionDate))),
OnTimePercent = 100.0*(1.0*COUNT(CASE WHEN CompletionDate <= CurrentDueDate THEN 1 END)/(COUNT(CompletionDate)))
FROM @T
GROUP BY NAME

Some days you're the dog, and some days you're the fire hydrant.

kofseattle_ken
Starting Member

2 Posts

Posted - 2009-06-05 : 10:31:06

Thank you that got me on the right track. Greatly appreciated!

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-05 : 12:11:25

You're welcome :)

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources