472,328 Members | 1,107 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,328 software developers and data experts.

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 V°yenli
EDB-konsulent as
NORWAY
Jul 20 '05 #1
2 2736
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: Wescotte | last post by:
Here is a small sample program I wrote in PHP (running off Apache 1.3.31 w/ PHP 5.0.1) to help illustrates problem I'm having. The data base is...
3
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to...
1
by: Jeff Blee | last post by:
I hope someone can help me get this graph outputing in proper order. After help from Tom, I got a graph to display output from the previous 12...
8
by: Adam Louis | last post by:
I would like help resolving this problem. I'm a novice who's been hired to query a hospital database and extract useful information, available to...
6
by: Martin Lacoste | last post by:
Ok, before I headbutt the computer... don't know why when I add criteria in a query, I get an 'invalid procedure call'. I also don't know why...
4
by: Apple | last post by:
1. I want to create an autonumber, my requirement is : 2005/0001 (Year/autonumber), which year & autonumber no. both can auto run. 2. I had...
11
by: Andy_Khosravi | last post by:
My problem: I'm having trouble with a query taking much too long to run; a query without any criteria evaluating only 650 records takes over 300...
4
by: Konrad Hammerer | last post by:
Hi! I have the following problem: I have a query (a) using another query (b) to get the amount of records of this other query (b), means: ...
4
by: Stan | last post by:
I am using MS Office Access 2003 (11.5614). My basic question is can I run a query of a query datasheet. I want to use more that one criteria and...
2
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in...
0
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
1
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.