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.
| Author |
Topic |
|
gjr.timmer
Starting Member
1 Post |
 Posted - 2004-10-21 : 10:29:32
|
| Dear SQL Experts,Together with a colleague of mine we are facing a big problem with SQL.We have the following assignment.SQL Server: MS SQLProblem Definition:Retrieve the total downtime errors of the network for each month in hours out of the helpdesk database, but only count the downtime when they are between 07:30 - 1700Fields:ClosedDate -> Date were the helpdesk closes the callRecvdDate -> Date when the call was registered ClosedTime -> Time when the call was closed at the helpdeskRecvdTime -> Time when the call was received at the helpdeskCallCategory -> helpdesk Category which type the call was registered on.We have figured out the following.Filter for Month and network:WHERE (CallCategorie = ‘Network’ AND (ClosedDate BETWEEN ‘2003-01-01’ AND ‘2003-01-31’)We tried the following:SELECT DATEDIFF(Hour, RecvdDate, ClosedDate) AS Expr1, DATEDIFF(Hour, RecvdTime, ClosedTime) AS Expr2 WHERE (CallCategory = ‘Network’) AND (ClosedDate BETWEEN ‘2003-01-01’ AND ‘2003-01-31’)The problem with this one is that we have no idea how to put in a filter for counting only between 07:30-17:00 and we don’t know how we can calculate with the output columns. Because sql will return an error that the column does not exists.We have the idea that the sql statement we made is not even in the right direction.We have really no idea know how to solve this problem.Any help is welcome.Thanks in AdvanceG.J.R. Timmer |
|
|
tsturm
Starting Member
7 Posts |
Posted - 2004-10-21 : 11:16:46
|
Just take your query:SELECT DATEDIFF(Hour, RecvdDate, ClosedDate) AS Expr1, DATEDIFF(Hour, RecvdTime, ClosedTime) AS Expr2 WHERE (CallCategory = ‘Network’) AND (ClosedDate BETWEEN ‘2003-01-01’ AND ‘2003-01-31’)and add:AND (CONVERT(char(8),ClosedTime,104)>'07:30:00' OR CONVERT(char(5),ClosedTime,104)<'17:00:00')should do.Tom |
 |
|
|
|
|
|
|
|
SQL Challange