Home » RDBMS Server » Server Administration » No of days depts were vacant
No of days depts were vacant [message #373723] Wed, 02 May 2001 15:56 Go to next message
Aneeta
Messages: 1
Registered: May 2001
Junior Member
Hi,
I have a problem
the user wants to know how many days the depts were vacant.

For ex

depts table looks like some thing like this

emplid deptcd transfer date completion dt
22222 A 1/jan1/2001
1111 A 2/may/2000 2/nov/2000
3333 a 2/feb/1999 2/feb/2000

It was occupied on 2/feb/1999 through 2/feb/2000.
And was vacant till 2/apr/2000(3 months) and than ocuupied from
2/apr/2000 to 2/nov/2000 and then vacant till 1/jan1/2001(vacant for 2 months).And so on ....

I have to find the no of vacancy for each dept .Any idea

Thansk
ANita

Report message to a moderator

Re: No of days depts were vacant [message #373733 is a reply to message #373723] Thu, 03 May 2001 14:05 Go to previous messageGo to next message
Sundar Venkatasubramaniam
Messages: 26
Registered: May 2001
Junior Member
you want to try
select max( completion_dt )- min(transfer_date) - sum( completion_dt - transfer_date+1)
from depts where completion_dt is not null group by deptcd

assuming no completion_dt = transfer_dt for a dept.

Report message to a moderator

Re: No of days depts were vacant [message #373734 is a reply to message #373723] Thu, 03 May 2001 14:07 Go to previous messageGo to next message
Sundar Venkatasubramaniam
Messages: 26
Registered: May 2001
Junior Member
you want to try
select max( completion_dt )- min(transfer_date) - sum( completion_dt - transfer_date+1)
from depts where completion_dt is not null group by deptcd

assuming no completion_dt = transfer_dt for a dept.

Report message to a moderator

Re: No of days depts were vacant [message #373735 is a reply to message #373723] Thu, 03 May 2001 14:10 Go to previous messageGo to next message
Sundar Venkatasubramaniam
Messages: 26
Registered: May 2001
Junior Member
you want to try
select max( completion_dt )- min(transfer_date) - sum( completion_dt - transfer_date+1)
from depts where completion_dt is not null group by deptcd

assuming no completion_dt = transfer_dt for a dept.

Report message to a moderator

Re: No of days depts were vacant [message #373736 is a reply to message #373723] Thu, 03 May 2001 14:33 Go to previous messageGo to next message
Sundar Venkatasubramaniam
Messages: 26
Registered: May 2001
Junior Member
Forgot to add +1 in the query .

you want to try
select max( completion_dt )- min(transfer_date)+1 - sum( completion_dt - transfer_date+1)
from depts where completion_dt is not null group by deptcd

assuming no completion_dt = transfer_dt for a dept.

Report message to a moderator

Re: No of days depts were vacant [message #373739 is a reply to message #373723] Thu, 03 May 2001 15:12 Go to previous message
Sundar Venkatasubramaniam
Messages: 26
Registered: May 2001
Junior Member
Forgot to add +1 in the query .

you want to try
select max( completion_dt )- min(transfer_date)+1 - sum( completion_dt - transfer_date+1)
from depts where completion_dt is not null group by deptcd

assuming no completion_dt = transfer_dt for a dept.

Report message to a moderator

Previous Topic: Problem with updating a column in a table from another table
Next Topic: Help!
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jun 29 00:40:09 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.