By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,745 Members | 1,897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,745 IT Pros & Developers. It's quick & easy.

Query problem

P: n/a
Hi
Some days ago I posted a problem with a query.
Thaks to Dave and John, I got a little closer to a solution.
Their suggestion was a solution to my problem as I stated it, but the real
problem is a bit more complicated, so I have to expand the original problem
a bit.
The query has to handle unknown number of departments, and a date interval.

I want:
1) For each day, for each department: a list of (from at_work table) all
employees at work.
2) In the same list I want listed (from emp table) all emplyees that belongs
to this department, but is not on work this date

Here are new scripts:
create table emp
(
empno int not null,
depno int not null
)
alter table emp add primary key (empno)

create table at_work
(
empno int not null,
depno int not null,
working_date int not null,
duration int not null
)
alter table at_work add primary key (empno, depno, working_date)
alter table at_work add constraint fk_at_work_emp foreign key (empno)
references emp (empno)

insert into emp (empno, depno) values (1,10)
insert into emp (empno, depno) values (2,10)
insert into emp (empno, depno) values (3,20)
insert into emp (empno, depno) values (4,20)

insert into at_work (empno, depno, working_date, duration) values
(1,10,'20031017',5)
insert into at_work (empno, depno, working_date, duration) values
(3,10,'20031017',4)
insert into at_work (empno, depno, working_date, duration) values
(1,10,'20031018',6)
insert into at_work (empno, depno, working_date, duration) values
(4,10,'20031018',7)
insert into at_work (empno, depno, working_date, duration) values
(1,20,'20031017',3)
insert into at_work (empno, depno, working_date, duration) values
(3,20,'20031017',5)
insert into at_work (empno, depno, working_date, duration) values
(2,20,'20031018',6)
insert into at_work (empno, depno, working_date, duration) values
(3,20,'20031018',7)
insert into at_work (empno, depno, working_date, duration) values
(4,20,'20031018',8)

The result set should now look like this:
empno depno working_date duration
---------------------------------------------
1 10 '20031017' 5
3 10 '20031017' 4
2 10 '20031017' NULL
1 10 '20031018' 6
4 10 '20031018' 7
2 10 '20031018' NULL
1 20 '20031017' 3
3 20 '20031017' 5
4 20 '20031017' NULL
2 20 '20031018' 6
3 20 '20031018' 7
4 20 '20031018' 8

Could someone please help me?

Thanks in advance
Regards,
Gunnar V°yenli
EDB-konsulent as
NORWAY
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Gunnar V°yenli" <gv@edbkonsulent.no> wrote in message news:3f********@news.broadpark.no...
Hi
Some days ago I posted a problem with a query.
Thaks to Dave and John, I got a little closer to a solution.
Their suggestion was a solution to my problem as I stated it, but the real
problem is a bit more complicated, so I have to expand the original problem
a bit.
The query has to handle unknown number of departments, and a date interval.

I want:
1) For each day, for each department: a list of (from at_work table) all
employees at work.
2) In the same list I want listed (from emp table) all emplyees that belongs
to this department, but is not on work this date

Here are new scripts:
create table emp
(
empno int not null,
depno int not null
)
alter table emp add primary key (empno)

create table at_work
(
empno int not null,
depno int not null,
working_date int not null,
duration int not null
)
alter table at_work add primary key (empno, depno, working_date)
alter table at_work add constraint fk_at_work_emp foreign key (empno)
references emp (empno)

insert into emp (empno, depno) values (1,10)
insert into emp (empno, depno) values (2,10)
insert into emp (empno, depno) values (3,20)
insert into emp (empno, depno) values (4,20)

insert into at_work (empno, depno, working_date, duration) values
(1,10,'20031017',5)
insert into at_work (empno, depno, working_date, duration) values
(3,10,'20031017',4)
insert into at_work (empno, depno, working_date, duration) values
(1,10,'20031018',6)
insert into at_work (empno, depno, working_date, duration) values
(4,10,'20031018',7)
insert into at_work (empno, depno, working_date, duration) values
(1,20,'20031017',3)
insert into at_work (empno, depno, working_date, duration) values
(3,20,'20031017',5)
insert into at_work (empno, depno, working_date, duration) values
(2,20,'20031018',6)
insert into at_work (empno, depno, working_date, duration) values
(3,20,'20031018',7)
insert into at_work (empno, depno, working_date, duration) values
(4,20,'20031018',8)

The result set should now look like this:
empno depno working_date duration
---------------------------------------------
1 10 '20031017' 5
3 10 '20031017' 4
2 10 '20031017' NULL
1 10 '20031018' 6
4 10 '20031018' 7
2 10 '20031018' NULL
1 20 '20031017' 3
3 20 '20031017' 5
4 20 '20031017' NULL
2 20 '20031018' 6
3 20 '20031018' 7
4 20 '20031018' 8

Could someone please help me?

Thanks in advance
Regards,
Gunnar V°yenli
EDB-konsulent as
NORWAY


SELECT COALESCE(W.empno, E.empno) AS empno,
COALESCE(W.depno, E.depno) AS depno,
COALESCE(W.working_date, D.working_date) AS working_date,
W.duration
FROM Emp AS E
CROSS JOIN
(SELECT DISTINCT working_date FROM At_Work) AS D
FULL OUTER JOIN
At_Work AS W
ON E.empno = W.empno AND
E.depno = W.depno AND
D.working_date = W.working_date
ORDER BY depno, working_date, empno

empno depno working_date duration
1 10 20031017 5
2 10 20031017 NULL
3 10 20031017 4
1 10 20031018 6
2 10 20031018 NULL
4 10 20031018 7
1 20 20031017 3
3 20 20031017 5
4 20 20031017 NULL
2 20 20031018 6
3 20 20031018 7
4 20 20031018 8

Regards,
jag
Jul 20 '05 #2

P: n/a
Since you want to report on dates which may or may not exist in your table,
best create a Calendar table:

CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY)

Populate with as many years as you need:

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'20101231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Here's the query:

SELECT COALESCE(W.empno, E.empno) AS empno,
COALESCE(W.depno, E.depno) AS depno,
COALESCE(C.caldate, W.working_date) AS working_date,
W.duration
FROM Calendar AS C
CROSS JOIN Emp AS E
FULL JOIN At_Work AS W
ON E.empno=W.empno AND E.depno=W.depno AND C.caldate=W.working_date
WHERE C.caldate BETWEEN '20031017' AND '20031018'
OR C.caldate IS NULL

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.