?
Solved

How do I write following queries?

Posted on 2011-02-17
7
Medium Priority
?
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 57

Expert Comment

by:HainKurt
ID: 34920144
try:

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

Assisted Solution

by:HainKurt
HainKurt earned 2000 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 57

Accepted Solution

by:
HainKurt earned 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

800 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