Challeging Script - please help

[cpuphantom]

Hello,

I am attempting to program a pretty challenging Stored Procedure in SQL 7. I thought I'd put it up on here because you all might think of something I have not.

Here's what I'm trying to do. I am trying to do a real time desk count for users in an office for a reporting time period. A desk is a person working there. Normally this would be pretty easy to do, but we have a lot of turn over, so if a person worked for our office for 6 months... and I'm running a report for a year... that person would actually only be 0.5 of a desk. I actually need to be as specific is by month, so if a person worked for 1 month out of the year, they would be 1/12 of a desk.

I track in the tbUser table the hire_date and Term_date of each user. Using those dates, and the DateDiff function I believe I can get a single number returned for the total number of desks for a time period.

Here's an example of the table:

Name       hire_date      term_date     office_id
Reagan J   6/1/1998       null          1
Sam H      1/1/2001       null          1
Joe G      1/1/2002       5/31/2004     1
Mike C     7/1/2004       null          1
Leith L    1/1/2003       2/1/2004      1
Jay        2/1/2004       4/30/2004     1

Now the stored procedure would require an office ID, a report start date, and a report end date.

CREATE PROCEDURE spCalcDesks
     @office_id int,
     @startdate datetime,
     @enddate datetime
AS

So if we assume we want to use a reporting date of 1/1/2004 to 12/31/2004... I'd want to output a single number when that stored procedure is run that is equal to the desks of people working during the time period of 2004.

The number this table should produce is: 3.25

Why 3.25? Because if you use DateDiff to calculate that each person was there the following number of months:

Reagan      12
Sam         12
Joe         5
Mike        6
Leith       1
Jay         3

The sum of those months is 39. Divid that by 12 (the # of months in the reporting period) that gives you 3.25 desks for the year.

This is a toughy... but figured someone out there would like the challenge. Let me know what you think would be a good stored procedure code to make this happen.

 

[vongrunt]

Awright. For anybody interested, here is gen SQL script.

create table myTable
(	Name varchar(32) not null,
	hire_date datetime not null,
	term_date datetime null,
	office_id int not null
)

insert into myTable values ('Reagan J', '1998-06-01', null, 1)
insert into myTable values ('Sam H', '2001-01-01', null, 1)
insert into myTable values ('Joe G', '2002-01-01', '2004-05-31', 1)
insert into myTable values ('Mike C', '2004-07-01', null, 1)
insert into myTable values ('Leith L', '2003-01-01', '2004-02-01', 1)
insert into myTable values ('Jay', '2004-02-01', '2004-04-30', 1)

First problem here is how to find intersection between two intervals. Math formula is simple:

[A, B] intersect [C, D] = [max(A, C), min(B, D)]

Therefore:

select Name, 
case when hire_date >= @startdate then hire_date else @startdate end as minDate,
case when term_date <= @enddate then term_date else @enddate end as maxDate
from myTable
where office_id = @office_id

NULL for term_date is handled correctly (2nd CASE will evaluate to false and return @enddate). Intersection exists when minDate <= maxDate, so only these results should be taken into further calculations:

select A.Name, datediff(mm, A.minDate, A.maxDate+1)
from
(	<query from above>
) A
[b]where A.minDate <= A.maxDate[/b]
-- order by A.Name

If you want averages:

select 1.0 * SUM(datediff(mm, A.minDate, A.maxDate+1))/ datediff(mm, @startdate, @enddate+1)
from
(	<query from above>
) A
where A.minDate <= A.maxDate

Btw. I'm not sure about rounding (in fact, ceiling). What if someone gets hired close to end of the month? Or if @enddate is somewhere in the middle of month? In any case, inner query used for intersection remains the same.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[cpuphantom]

dude, you are seriously a genius. Thank you so much for your help. That looks exactly like what I was looking for. You just helped me out a lot.