Solved

How do I write following queries?

Posted on 2011-02-17
7
264 Views
Last Modified: 2012-05-11
Can you help me writing following queries:

1. How many staff members have more than 1 assignment?
2. Select personID, employmentID from TEST_STAFF table having more than 1 assignment.


CREATE TABLE [Test_staff](
      [personID] [int] NOT NULL,
      [identityID] [int] NULL,
      [employmentID] [int] NULL,
      [assignmentID] [int] NULL)

INSERT INTO [Test_staff] VALUES (1,2,3,4)
INSERT INTO [Test_staff] VALUES (1,2,3,1)
INSERT INTO [Test_staff] VALUES (1,2,3,5)
INSERT INTO [Test_staff] VALUES (2,4,6,6)
INSERT INTO [Test_staff] VALUES (3,2,1,1)
INSERT INTO [Test_staff] VALUES (3,2,1,2)
INSERT INTO [Test_staff] VALUES (4,2,1,3)
INSERT INTO [Test_staff] VALUES (5,3,2,1)
0
Comment
Question by:pawar_deepak
7 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 34919841
This looks like a homework problem.  If so, we are not allowed to help you with this.
0
 
LVL 3

Expert Comment

by:rob_AXSNL
ID: 34919976
select t.personID from Test_staff  t where 1 < 
(select COUNT(PersonID) from Test_staff t2 where t.personID = t2.personID)

select t.personID from Test_staff  t group by t.personid having 1 < 
(select COUNT(PersonID) from Test_staff t2 where t.personID = t2.personID)

You can play around ...
0
 

Author Comment

by:pawar_deepak
ID: 34920085
Hello KnightEknight,

Thanks for your comment but it surely isn't a homework question... I want to write query which is similar to queries mentioned above. I just created a new table to simplify the question..

I need to perform some operations on following view... I just asked the part which I need help with.

CREATE VIEW [dbo].[staff]
AS
SELECT p.personID, p.stateID, p.staffNumber, p.staffStateID,
       i.identityID, i.effectiveDate, i.lastName, i.firstName, i.middleName, i.suffix, i.alias, i.gender, i.birthdate, i.ssn, i.raceEthnicity,
       e.employmentID, ea.assignmentID, ea.startDate, ea.endDate, ea.title, e.seniority, ea.type, ea.teacher,
       ea.specialEd, ea.behavior, ea.health, ea.advisor, ea.supervisor, ea.foodservice, ea.departmentID,
       s.schoolID, s.name schoolName, s.number schoolNumber, d.name departmentName,ea.excludeReferral, ea.counselor
FROM dbo.Person p WITH (NOLOCK)
INNER JOIN dbo.[Identity] i WITH (NOLOCK) ON p.currentIdentityID = i.identityID
LEFT OUTER JOIN dbo.Employment e WITH (NOLOCK) ON e.personID = p.personID
INNER JOIN dbo.EmploymentAssignment ea WITH (NOLOCK) ON p.personID = ea.personID
INNER JOIN dbo.School s WITH (NOLOCK) ON s.schoolID = ea.schoolID
LEFT OUTER JOIN dbo.Department d WITH (NOLOCK) ON d.departmentID = ea.departmentID

It might be a simple question for you to answer but I'm getting errors while writing my query...
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34920144
try:

select * from Test_staff t
group by personID
having count(1) > 1
0
 
LVL 51

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 500 total points
ID: 34920157
oops, above does not work. try this:

select personid, count(1) assignments_count
from Test_staff t
group by personID
having count(1) > 1

0
 
LVL 51

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 34920176
second part:

select distinct personID, employmentID
from Test_staff t left join (select personid, count(1) assignments_count
from Test_staff t
group by personID
having count(1) > 1) x on t.personid=x.personid
0
 

Author Comment

by:pawar_deepak
ID: 34920673
Thanks HainKurt.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question