Help with my query

Hi,

I would like to get the data which return employee code associate with the order that is already assigned to them. But on those orders, I also would like to create different column counting how many orders are finished, and how many orders are still in process.

Can anyone help me with the query?

=============================================================

SELECT
e.EmployeeCode,
COUNT(oa.OrderID) as OrderCount
FROM Employee e
INNER JOIN OrderAssignment oa ON oa.EmployeeID = e.EmployeeID
WHERE e.DivisionCode = 'COM'
GROUP BY e.EmployeeCode

==============================================================

Above query will return the number of orders assigned to employee, however, I also need to get how many orders finished and how many orders still in process.

Thanks in advance.

SELECT
e.EmployeeCode,
COUNT(oa.OrderID) as OrderCount,

SUM(CASE WHERE oa.OrderStatus='Finished' THEN 1 ELSE 0 END) AS Finished,

SUM(CASE WHERE oa.OrderStatus='InProcess' THEN 1 ELSE 0 END) AS InProcess
FROM Employee e
INNER JOIN OrderAssignment oa ON oa.EmployeeID = e.EmployeeID
WHERE e.DivisionCode = 'COM'
GROUP BY e.EmployeeCode

Help with my first report!

Hi Team,

I am trying to create my first report here. My report will show the employee name with corresponding order assigned to them. I already created one datasets which returned the employee name, then I created another one which take the employee name and returned count of the order.

My questions is:

1) is that possible to passing the parameter from the first datasets which contain of customer name?

2) is there any better way of doing this (maybe using one datasets instead of two).

Any respond I will really appreciate.

Anyone please!|||

Make one dataset with query like this:

select empName, count(orderID) as orderCount
from employees
inner join orders on employees.name = orders.employeeName

But better if you use employee ID rather than his name for joining tables.

|||Thank you.