Hi,
I have a problem to write this query. I need to show a report which have employeeName, OrderTotal based on certain date.
However, eventhough that employee doesn't have an order yet, I will still show their name on the report with zero as orderTotal.
I have this query:
Select
EmployeeName,
Count(OrderID) as TotalOrder
from Employee WHERE InvoiceDT BETWEEN '1/1/2006' AND '1/31/2006'
But, this query would not return employeeName which are not between those invoiceDT.
So, how to get all employeeName, but filter the Order based on the invoiceDT.
Thanks in advance.
Anyone please...|||
Are you seeking for something like this?:
SELECT EmployeeName,Sum(TotalOrder) FROM
(SELECT EmployeeName,TotalOrder=CASE WHEN InvoiceDT BETWEEN '1996-08-06' AND '1998-01-05'
THEN Count(OrderID)
ELSE 0
END
FROM Employees
GROUP BY EmployeeName,InvoiceDT) as tmp
GROUP BY EmployeeName
No comments:
Post a Comment