?
Solved

How do I write following queries?

Posted on 2011-02-17
7
Medium Priority
?
289 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 61

Expert Comment

by:HainKurt
ID: 34920144
try:

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

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 61

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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