SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Output duplicate row occurances in a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

melon.melon
Yak Posting Veteran

Singapore
76 Posts

Posted - 08/18/2013 :  08:00:52  Show Profile  Reply with Quote
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

Edited by - melon.melon on 08/18/2013 21:59:19

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/19/2013 :  05:42:06  Show Profile  Reply with Quote
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
Go to Top of Page

melon.melon
Yak Posting Veteran

Singapore
76 Posts

Posted - 08/19/2013 :  13:46:55  Show Profile  Reply with Quote
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

Edited by - melon.melon on 08/19/2013 13:56:21
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000