All outstanding within a period.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

All outstanding within a period.

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2009-05-15 : 11:21:11

Hi,

Don't know If I'm being dense here, but I can't seem to get my head round this...

We've got a support system. Lets say for arguments sake the jobs table & data looks like this:

create table 
#Support
(jobid int identity (1,1), start datetime, complete datetime)
insert into #support 
select '01/05/2009', '07/05/2009' union
select '02/05/2009', '08/05/2009' union
select '03/05/2009', '09/05/2009' union
select '04/05/2009', '10/05/2009' union
select '05/05/2009', '11/05/2009' union
select '06/05/2009', '12/05/2009' union
select '07/05/2009', '13/05/2009'

I can work out how many jobs are outstanding (not completed) at any given date like this:

declare @reportdate datetime
set @reportdate = '01/05/2009'

select 
@reportdate reportdate,
count(jobid) outstandingjobs
from #support 
where @reportdate >= #support.start and @reportdate <= isnull(#support.complete, current_timestamp)

Which gives me this:

reportdate outstandingjobs
---------- ---------------
2009-05-01 1

But how do I work out how many jobs are outstanding for each day within a period? so say I wanted to make my period 01/05/2009 to 13/05/2009, I want to get back something like this:

reportdate outstandingjobs
---------- ---------------
2009-05-01 1
2009-05-02 2
2009-05-03 3
2009-05-04 4
2009-05-05 5
2009-05-06 6
2009-05-07 7
2009-05-08 6
2009-05-09 5
2009-05-10 4
2009-05-11 3
2009-05-12 2
2009-05-13 1

Any ideas?

Cheers

Cheers,

Yonabout

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-15 : 11:46:44

Is CompleteDate NULL for jobs that have not been completed? I'm not sure I'm following your business rules, but maybe the data is throwing me off. Obviously, your sample data doesn't match your expected output. Can you prove some matching sample data and expected output?

Just a guess:

DECLARE @PeriodStart DATETIME
DECLARE @PeriodEnd DATETIME

SET @PeriodStart = '20090501'
SET @PeriodEnd = '20090506'


SELECT 
	start,
	COUNT(*) AS OutstandingJobs
FROM 
	#Support
WHERE
	complete IS NULL
	AND start BETWEEN @PeriodStart AND @PeriodEnd
GROUP BY 
	start

-- Or

select 
	start AS reportdate,
	count(jobid) AS outstandingjobs
from 
	#support 
where 
	start BETWEEN @PeriodStart AND @PeriodEnd
	AND COALESCE(complete, CURRENT_TIMESTAMP) BETWEEN @PeriodStart AND @PeriodEnd

yonabout
Posting Yak Master

112 Posts

Posted - 2009-05-15 : 15:21:39

Yep, complete date is null for unfinished jobs, which is why I stuck the isnull bit in so there was a complete date of now to do the comparison with.

I'm at home now so I'll have to wait a couple of days before I can check your suggestion, but thanks in anticipation of it being what I'm after

Cheers,

Yonabout

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-16 : 03:40:28

[code]
CREATE TABLE #TALLY
(Number INT,
CONSTRAINT PK_Tally_ Number PRIMARY KEY CLUSTERED (Number))
DECLARE @PeriodStart datetime,@PeriodEnd datetime,@Start int

SET @Start=0

WHILE @Start<=DATEDIFF(dd,@PeriodStart,@PeriodEnd)-1
BEGIN
INSERT INTO #TALLY (Number)
SELECT @Start
SET @Start=@Start+1
END

SELECT DATEADD(dd,Number,@PeriodStart) AS [Date],0 AS OutstandingJobs
INTO #RESULTS
FROM #TALLY
WHERE DATEADD(dd,Number,@PeriodStart)<=@PeriodEnd

UPDATE r
SET r.OutstandingJobs=(SELECT COUNT(*) FROM #Support WHERE start<=r.Date AND (complete >= r.Date OR complete IS NULL))
FROM #RESULTS r

SELECT * FROM #RESULTS

DROP TABLE #TALLY
DROP TABLE #RESULTS
[/code]

yonabout
Posting Yak Master

112 Posts

Posted - 2009-05-18 : 05:49:19

Hi,

Thanks both for your help.

I tried both methods, and visakhs is more like what I'm after.

Thanks again!

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources