I need Perform select Query following this - Bandi

Posted by Jayakumars under Sql Server on 7/31/2015 | Points: 10 | Views : 419 | Status : [Member] [MVP] | Replies : 3
Hi
bandi


Create table Employee2
(
Eno int,
Name varchar(30),
Sex varchar(10),
Dob date,
Doj date,
Designation varchar(20),
Basic_Pay decimal(18,2),
Dept_No int
)

Create table Department2
(
DeptNo int,
DeptName varchar(30)
)

Create table Project
(
ProNo int,
proName varchar(20),
Dept_No int
)

Create table Worksfor
(
Eno int,
ProNi int,
Date date,
Hours int
)


My Questions

1.who have worked in more than three projects on a day.

2.View need track of the department number, the number of employees in the department, and the total basic pay expenditure for
each department.

Mark as Answer if its helpful to you


Responses

Posted by: Bandi on: 8/3/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down

1.who have worked in more than three projects on a day.
SELECT e.Eno, w.Date, COUNT( w.ProNi)
FROM Worksfor w
JOIN Employee2 E ON w.Eno = E.Eno
GROUP BY e.Eno, w.Date
HAVING COUNT( w.ProNi)>3


2.View need track of the department number, the number of employees in the department, and the total basic pay expenditure for
each department.

SELECT e.Dept_No, COUNT(e.Eno) NumberOfEmps, SUM(Basic_Pay) TotalExpenditure
FROM Employee2 E
JOIN Department2 d ON E.Dept_No= d.DeptNo
GROUP BY e.Dept_No


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 8/3/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
bandi

Not working

My test data this

--Insert into Employee2 values(1,'AAA1','Male','02/02/1981','05/05/2002','Software Programmer1',12850.25,101)
--Insert into Employee2 values(2,'AAA2','FeMale','07/02/1981','06/05/2002','Software Programmer2',12850.25,102)
--Insert into Employee2 values(3,'AAA3','Male','09/02/1981','07/05/2002','Software Programmer3',12850.25,103)
--Insert into Employee2 values(4,'AAA4','FeMale','12/02/1981','08/05/2002','Software Programmer4',12850.25,101)
--Insert into Department2 values(101,'Software'),(102,'Hardware'),(104,'HR'),(103,'Production')
--Insert into Project values(1,'Sales Management system',101),(2,'School Management system',102),(3,'Accounts Management system',103)
--Insert into Worksfor VALUES(1,2,'02/03/1981',60),(2,3,'04/02/2013',40),(3,4,'07/02/2013',20)
--Insert into Worksfor VALUES(4,2,'08/03/1981',20),(5,3,'04/02/2013',60),(6,4,'07/02/2013',35)


My Question this

1.who have worked in more than three projects on a day.
based calculation hours column
ProNi column just enter number value thats it



Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/3/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
I didn't get your point....

can you explain how u want the output for above sample data?

I thought, Worksfor table will be having EmployeeNum, ProjectNum, Date on which she/he has worked on task, Hours ( how many hours she/he spent )


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response