Output duplicate row occurances in a table

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

Output duplicate row occurances in a table

Author

Topic

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2013-08-18 : 08:00:52

I want to retrieve staff who attend less than 80% for a meeting type assuming we have 10 meetings per list.

Meeting Table:

staffID list date
------------------------
1 A 2013-01-15
2 B 2013-01-17
1 B 2013-01-17
1 A 2013-01-18
2 B 2013-01-19
1 A 2013-01-20
2 C 2013-01-21

* 1 - Dan
* 2 - Jane

When the staffID occur 3 times (70%), query will Output:

staffName list Participation%
------------------------
Dan A 70


SELECT a.staffName, b.list, 
(100 - ((COUNT(c.staffID) * 100) / 10)) AS 'Participation%' 
from Staff AS a, listType AS b, Meeting AS c
where a.staffID = c.staffID AND 
b.list = c.list 
GROUP BY a.staffName, b.list
HAVING COUNT(c.staffID) > 2

Thanks for any help,
melon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-19 : 05:42:06

sorry how do you determine that 3 occurance is 70 %? is the maximum mettings fixed for the employees?

Also didnt understand whats the issue you faced with query above. please illustrate with some sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2013-08-19 : 13:46:55

Hi,

Sorry for late reply i juz came back frm a class.

- The column for meeting table "Date" means the date that the staff miss the meeting

- There are 2 type of meetings: Team Meeting, Board Meeting

- Each type of Meeting has 10 sessions each so Team Meeting has 10 & Board Meeting has 10.

If a staff miss the same meeting on 3 different dates (less than 80% for a meeting type),
the output will give the meeting that this staff has missed.

(e.g.)
From the Meeting table, staffID 1 (Dan) did not attend the Team Meeting held on dates 2013-01-15,2013-01-18,2013-01-20

3/10 * 100% = 30%
100 - 30% = 70% [output this staff as has less than 80% of overall attendance]

Meeting Table:

staffID list date
------------------------
1 A 2013-01-15
2 B 2013-01-17
1 B 2013-01-17
1 A 2013-01-18
2 B 2013-01-19
1 A 2013-01-20
2 C 2013-01-21

Staff Table:
staffID staffName
---------------------
1 Dan
2 Jane

listType Table:
list Name
---------------------
A Team Meeting
B Board Meeting

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources