Hi everyone! I am working on a report where I want to get the total orders entered per branch per day and per month. The query I have right now is:
SELECT OwnerStaff_Branch, COUNT(WorkOrderID) AS MonthTotal,
(SELECT COUNT(WorkOrderID) AS Expr1
WHERE (dbo.DateOnly(CreatedOn) = dbo.DateOnly(GETDATE()))
) AS DailyTotal
WHERE (MONTH(CreatedOn) = MONTH(GETDATE())) AND (YEAR(CreatedOn) = YEAR(GETDATE()))
GROUP BY OwnerStaff_Branch
The hangup here is that the results for the sub-select is the same total in all branches... which isn't the case. How do I make this code return the unique total per branch?
As always, you guys rock and your help is greatly appreciated!
Here's what the report looks like:
Branch MonthlyTotal DailyTotal
Branch1 113 37
Branch2 7 37
Branch3 23 37
Branch4 527 37