return age of an item, in Work days.

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)

return age of an item, in Work days.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-07-13 : 10:15:25

Randy writes "I have the following problem, I hope you can help me with it.

I want the average turn around time for an item, i have the following:

select
( TotalTurnAroundTime / NumberofTickets ) as AverageTurnAroundTime ,
numberoftickets,
totalturnaroundtime
from
(
select
cast(count(id) as float) as NumberofTickets,
Sum(
(
case
when recoverydate is null then abs(datediff(d, date, '05/31/04'))+1
else abs(datediff(d, date, recoverydate))+1
end
)
) as TotalTurnAroundTime
from tbdailyprooftickets
where
(
(ticketdate between '05/01/04' and '05/31/04')
or
(recoverydate between '05/01/04' and '05/31/04')
or
recoverydate is null
)
and
categoryid in ( 4,6, 14)
) as turnaroundtime

My problem.

The TotalTurnAroundTime is returned for every single day. No problem right? Problem - I want workdates only.

I've thought of using a modulus if it's over 7 then it would be age = age - int(age/5) + 5 mod age + 1. (free hand equation)

That doesn't work, since if it's a friday, and the item is recovered on monday - I want 2. Not 4.

Thank you for your help!"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-13 : 10:43:09

And what about public holidays?

You will need to have a table that references which days/hours are non-working hours.

If your code joins to this table you can eliminate non-working hours from your calculation.

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources