Calculate Percentage <= 24 hours

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 2005 Forums

Transact-SQL (2005)

Calculate Percentage <= 24 hours

Author

Topic

a0305547
Starting Member

3 Posts

Posted - 2009-06-25 : 08:42:22

Hi,
I have two dates, Job_started_date and Job_finished_date
I want to calculate the percentage of Jobs finished within 24 hours (i.e. Job_finished minus Job_started <= 24 hours)

I have:
SELECT Job_No, Job_started_date, DATEDIFF(hh, Job_started_date, Job_finished_date)
FROM Job

Can anyone suggest how to Count the number of Jobs completed within 24 hours and then express that as a percentage of the total number of Jobs carried out within a range of Job_started_date

Thanks,
Graham

gh444

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-25 : 08:54:40

[code]
SELECT count(*) * 100.0 / (select count(*) from Job)
FROM Job
where DATEDIFF(hour, Job_started_date, Job_finished_date) <= 24
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-25 : 08:56:26

quote:
total number of Jobs carried out within a range of Job_started_date

Can you define this more clearly ? What range ? Example ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 08:58:10

E 12°55'05.63"
N 56°04'39.26"

a0305547
Starting Member

3 Posts

Posted - 2009-06-25 : 09:08:34

quote:
Originally posted by khtan

quote:
total number of Jobs carried out within a range of Job_started_date

Can you define this more clearly ? What range ? Example ?

KH
[spoiler]Time is always against us[/spoiler]

For example Job started dates for the week from 01/06/2009 00:00:01 to 07/06/2009 23:59:59

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 09:11:30

[code]SELECT 1.0E * ShortJobs / AllJobs,
100.0E * ShortJobs / AllJobs
FROM (
SELECT COUNT(*) AS ShortJobs
FROM Job
WHERE DATEDIFF(SECOND, Job_started_date, Job_finished_date) < 86400
AND Job_started_date >= '20090601'
AND Job_started_date < '20090608'
CROSS JOIN (
SELECT COUNT(*) AS AllJobs
FROM Job
WHERE Job_started_date >= '20090601'
Job_started_date < '20090608'
) AS x[/code]

E 12°55'05.63"
N 56°04'39.26"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-06-25 : 10:08:54

[code]
select
ShortJobsPercent =
sum(case
when Job_finished_date <= dateadd(dd,1,Job_started_date)
then 100.0E
else 0.00E end) / count(*) ,
TotalShortJobs =
sum(case
when Job_finished_date <= dateadd(dd,1,Job_started_date)
then 1
else 0 end) ,
TotalJobs = count(*)
from
Job
where
Job_started_date >= '20090601' and
Job_started_date < '20090608'
[/code]

CODO ERGO SUM

a0305547
Starting Member

3 Posts

Posted - 2009-06-30 : 08:59:54

quote:

Originally posted by Michael Valentine Jones


select
	ShortJobsPercent =
		sum(case
		when Job_finished_date <= dateadd(dd,1,Job_started_date)
		then 100.0E
		else 0.00E end) / count(*) ,
	TotalShortJobs =
		sum(case
		when Job_finished_date <= dateadd(dd,1,Job_started_date)
		then 1
		else 0 end) ,
	TotalJobs = count(*)
from
	Job
where
	Job_started_date >= '20090601'	and
	Job_started_date < '20090608'

CODO ERGO SUM

Thanks for help everyone. I've adopted a solution along the lines proposed by Michael.

gh444

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources