challenging question - SQL Server Forums

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

Other Forums

MS Access

challenging question

Author

Topic

batarsehs
Starting Member

9 Posts

Posted - 2005-07-07 : 12:31:58

I need to create a database that hosts the names of people who would participate in taking surveys.
now, i need to send the survey1 to 20% of the whole participant.
Now, for survey 2, i need to take 20% out of the people who took survey 1, and replace them with people who did not take survey at all. "the number of people who are taking the survey at any given time should always be = 20% of the whole participants"
for survey 3 and survey 4, it should be the same concept as survey 2

after I give out 4 surveys, i need to create a query to check who have already taken surveys 4 times, and replace them with people who did not take the surveys at all.

I need a query that calculates that..
for now, all i have is a participant table that includes participantID, F&LName
I have Survey table: it has SurveyID, SurveyName, SurveyCreationDate.
Participant-SurveyDetail: it has surveyID and ParticipantID, SurveyStampDate "which represents the date that the participant actually filled out the survey".

If you can figure the calculations.. please let me know..

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-08-10 : 20:56:48

20 Percent of the whole participant:

SELECT TOP 20 PERCENT Participant.ParticipantID, Participant.FirstName, Participant.LastName
FROM Participant ORDER BY Rnd(ParticipantID);

20 percent of the people who took survey 1

SELECT TOP 20 PERCENT P.ParticipantID
FROM Participant AS P 
INNER JOIN [Participant-SurveyDetail] AS PS ON P.ParticipantID = PS.ParticipantID 
WHERE (((PS.SurveyID)=1)) ORDER BY Rnd([p].[ParticipantID]);

People who did not take any survey at all:

SELECT P.ParticipantID
FROM Participant AS P LEFT JOIN [Participant-SurveyDetail] AS PS 
 ON P.ParticipantID = PS.ParticipantID
WHERE (((PS.ParticipantID) Is Null))

A query to let you know who took what:

SELECT P.ParticipantID
, [ParticipantID] In (SELECT ParticipantID FROM [Participant-SurveyDetail] as PS WHERE PS.SurveyID = 1) AS HasTakenSurvey1
, [ParticipantID] In (SELECT ParticipantID FROM [Participant-SurveyDetail] as PS WHERE PS.SurveyID = 2) AS HasTakenSurvey2
, [ParticipantID] In (SELECT ParticipantID FROM [Participant-SurveyDetail] as PS WHERE PS.SurveyID = 3) AS HasTakenSurvey3
, [ParticipantID] In (SELECT ParticipantID FROM [Participant-SurveyDetail] as PS WHERE PS.SurveyID = 4) AS HasTakenSurvey4 
FROM Participant AS P;

How many times did those who took surveys take them?:

SELECT [Participant-SurveyDetail].SurveyID, [Participant-SurveyDetail].ParticipantID, Count(*) AS NumberOfTakes
FROM [Participant-SurveyDetail]
GROUP BY [Participant-SurveyDetail].SurveyID, [Participant-SurveyDetail].ParticipantID;

I hope that gives you a good start...

~ Shaun Merrill
Seattle, WA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources