Query problem

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 Vyenli
EDB-konsulent as
NORWAY"Gunnar Vyenli" <gv@.edbkonsulent.no> wrote in message news:3f93e231$1@.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 Vyenli
> 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|||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
--