sql statement possible?

Question

Hello,

I am trying to write an SQL query for a help desk system that I am developing. I have these two tables called Technician and WorkOrder. The WorkOrder table has a Technician ID field that marks which Technician was assigned to it.

I would like to create an SQL statement that will return each Technician, how many work orders they've been assigned (Technician.ID = WorkOrder.TechID), and how many of them are still open (WorkOrder.Status = 1)

I can manage to get two of the values with a GROUP BY statement but I am wondering if all three values can be retrieved with a compound statement or if I am just better off retrieving two separate data sets.

BTW this is for MS SQL 2005

Answer 1

Use CASE WHEN... For example,

SELECT Technician.ID AS TechnicianID,
Count(WorkOrder.ID) AS AssignedWorkOrderCount,
Sum(CASE WHEN WorkOrder.Status=1 Then 1 Else 0 END) AS OpenWorkOrderCount

FROM Technician INNER JOIN WorkOrder ON Technician.ID=WorkOrder.TechID

GROUP BY Technician.ID

Answer 2

Try to Group by WorkOrder Status also.

If not possible,
Try with Different Queries which is the best way.

Answer 3

How about something like:

select t.name, 'assigned', count(*) from technician t, workorder w on t.id = w.techId
group by t.name, w.techid
union
select t.name, 'open', count(*) from technician t, workorder w on t.id = w.techId
group by t.name, w.techid,w.status
having w.status = 'new'

-----------------


An alternative might be to use a "window function", but I've not messed with them enough to be sure.

Answer 4

While it's possible Delphi is different (never worked with it), usually a WHERE clause is not applicable for an insert operation. What you probably want is to do a query to see if the user exists and conditionally do tyhe insert depending on whether any rows were found.

Answer 5

Do you have to use ONLY SQL?
I'm sure it can be done using only SQL queries, but man that would make my brain die.
Try mixing some C# into the fray and see what happens.