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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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  
 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.04 seconds. Powered By: Snitz Forums 2000