Need help to create SQL query - 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

General SQL Server Forums

New to SQL Server Programming

Need help to create SQL query

Author

Topic

claud53
Starting Member

4 Posts

Posted - 2011-02-14 : 05:59:06

Hello,

I need some help to create a SQL query.
I have three tables:

Employer(ID_E, Name_Surname, Function, ID_D)
Department(ID_D, Description)
Vacation(ID_E, Start_Date, Period)

I want the query to find:
The ID of the employers and Name_surname for the employers that have done longer vacations that the average of the days of the employers of the same Function in the same Department.

here are some sample data:

Table Employer:

ID_E Name_Surname Function ID_D

1 A.Done IT 2
2 B.Funny IT 2
3 M.Douglas IT 2
4 A.Pata Accountant 1
5 G.Nesa Accountant 1

Table Department:

ID_D Description

1 Economic Department
2 Technical Department

Table Vacation:

ID_E Start_Date Period

1 1/1/2011 15
2 2/2/2011 5
3 15/1/2011 10
4 15/02/2011 9
5 17/02/2011 7

From the data in simple words I want the query to display

ID_E Name_Surname

1 A.Done
4 A.Pata

Could you please help me!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-14 : 08:24:45

Hi Claud53,

Most people here won't do your homework for you, but if you try to do it yourself, poeple here are more than happy to help. Post the query you have so far (even if it doesn't work) and we'll help you from there.

Jim

Everyday I learn something that somebody else already knew

claud53
Starting Member

4 Posts

Posted - 2011-02-14 : 08:45:02

The query I have until now is:

Select E.ID_E, E.Name_Surname
from Employer E, Department D, Vacation V
Where E.ID_E=V.ID_E
and E.ID_D=D.ID_D
Having V.Period >
(select E1.Function, D1.ID_D, AVG(V1.Period)as aver
From Employer E1, Vacation V1, Department D1
Where E1.ID_E=V1.ID_E and D1.ID_D=E1.ID_D
Group by E1.Function, D1.ID_D)

I know that there is a problem with the having part but I don't know how to proceed.

Thanks for your help

claud53
Starting Member

4 Posts

Posted - 2011-02-14 : 08:47:29

let say that I don't know even if it is possible to build such a query because I am new to SQL

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-14 : 09:28:08

When asking a question you should provide test data in an easily consumable format.
Dates should be ISO format.


CREATE TABLE #Employer
(
	ID_E int NOT NULL
	,Name_Surname varchar(20) NOT NULL
	,[Function] varchar(20) NOT NULL
	,ID_D int NOT NULL
)
INSERT INTO #Employer
SELECT 1, 'A.Done', 'IT', 2
UNION ALL SELECT 2, 'B.Funny', 'IT', 2
UNION ALL SELECT 3, 'M.Douglas', 'IT', 2
UNION ALL SELECT 4, 'A.Pata', 'Accountant', 1
UNION ALL SELECT 5, 'G.Nesa', 'Accountant', 1

CREATE TABLE #Vacation
(
	ID_E int NOT NULL
	,[Start_Date] datetime NOT NULL
	,Period int NOT NULL
)
INSERT INTO #Vacation
SELECT 1, '20110101', 15
UNION ALL SELECT 2, '20110202', 5
UNION ALL SELECT 3, '20110115', 10
UNION ALL SELECT 4, '20110215', 9
UNION ALL SELECT 5, '20110217', 7

Assuming you are using SQL2005 or above, the following query, which uses the windowed functions, works with the above test data:


;WITH VacationAggregates
AS
(
	SELECT E.ID_E, E.Name_Surname
		,SUM(V.Period) OVER (PARTITION BY E.ID_E) AS Duration
		,AVG(V.Period) OVER (PARTITION BY E.ID_D) AS DeptAvg
	FROM #Employer E
		JOIN #Vacation V
			ON E.ID_E = V.ID_E
)
SELECT ID_E, Name_Surname
FROM VacationAggregates
WHERE Duration > DeptAvg
ORDER BY ID_E

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-14 : 09:28:24

Here is a way, it may not be the best, (but hey, it's free!)
Jim

DECLARE @Employer TABLE (ID_E tinyint, Name_Surname varchar(50),fcn varchar(20),ID_D tinyint)
INSERT INTO @Employer

SELECT 1,'A.Done','IT'         ,2 UNION ALL---
SELECT 2,'B.Funny','IT'        ,2 UNION ALL
SELECT 3,'M.Douglas','IT'      ,2 UNION ALL
SELECT 4,'A.Pata','Accountant' ,1 UNION ALL----
SELECT 5,'G.Nesa','Accountant' ,1

DECLARE @Department TABLE (ID_D tinyint,Descr varchar(50))

INSERT INTO @Department

SELECT 1,'Economic Department'    UNION ALL
SELECT 2,'Technical Department '


DECLARE @Vacation TABLE(ID_E tinyint, startDate datetime,Period tinyint)

INSERT INTO @Vacation
SELECT 1,'20110101',   15  UNION ALL
SELECT 2,'20110202',    5  UNION ALL
SELECT 3,'20110115',   10  UNION ALL
SELECT 4,'20110215',    9  UNION ALL
SELECT 5,'20110217',    7

 
SELECT e.ID_E,e.name_surname
FROM @employer e
INNER JOIN

 (
  select e.id_e 
      ,[rank] = rank() over(partition by e.id_d order by  v.period desc)
       
  from @employer e
  inner join @vacation v on e.id_e = v.id_e
  ) t

ON e.id_e = t.id_e
WHERE t.rank = 1

Everyday I learn something that somebody else already knew

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-14 : 10:46:16

claud53
Starting Member

4 Posts

Posted - 2011-02-16 : 04:07:20

Thank you very much for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources