InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 11:24:38 GMT20http://www.sqlservercentral.com/Forums/Topic983189-149-1.aspxNo worries.Consider making your WHERE clauses SARGable:[code="sql"]DECLARE @Startmonth DATE, @Endmonth DATE, @Startday DATE, @Endday DATESET @Startmonth = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)SET @Endmonth = DATEADD(MONTH,1,@Startmonth)SET @Startday = DATEADD(day,DATEDIFF(day,0,GETDATE()),0)SET @Endday = DATEADD(day,1,@Startday)SELECT @Startmonth, @Endmonth, @Startday, @EnddaySELECT OwnerStaff_Branch, COUNT(WorkOrderID) AS MonthTotal, (SELECT COUNT(WorkOrderID) AS Expr1 FROM WorkOrderReportView WHERE CreatedOn >= @Startday AND CreatedOn < @Endday AND OwnerStaff_Branch = w.OwnerStaff_Branch) ) AS DailyTotalFROM WorkOrderReportView wWHERE CreatedOn >= @Startmonth AND CreatedOn < @EndmonthGROUP BY OwnerStaff_Branch[/code]Thu, 09 Sep 2010 09:24:13 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic983189-149-1.aspxThanks! I couldn't for the life of me figure out how to do that... I knew I was missing some kind of tie in. You're the best, thanks again. Thu, 09 Sep 2010 09:19:55 GMTiklektichttp://www.sqlservercentral.com/Forums/Topic983189-149-1.aspxYou forgot to correlate your correlated subquery:[code="sql"]SELECT OwnerStaff_Branch, COUNT(WorkOrderID) AS MonthTotal, (SELECT COUNT(WorkOrderID) AS Expr1 FROM WorkOrderReportView WHERE (dbo.DateOnly(CreatedOn) = dbo.DateOnly(GETDATE()) AND OwnerStaff_Branch = w.OwnerStaff_Branch) ) AS DailyTotalFROM WorkOrderReportView wWHERE (MONTH(CreatedOn) = MONTH(GETDATE())) AND (YEAR(CreatedOn) = YEAR(GETDATE()))GROUP BY OwnerStaff_Branch[/code]Thu, 09 Sep 2010 09:12:18 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic983189-149-1.aspxHi 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:[code="sql"]SELECT OwnerStaff_Branch, COUNT(WorkOrderID) AS MonthTotal, (SELECT COUNT(WorkOrderID) AS Expr1 FROM WorkOrderReportView WHERE (dbo.DateOnly(CreatedOn) = dbo.DateOnly(GETDATE())) ) AS DailyTotalFROM WorkOrderReportViewWHERE (MONTH(CreatedOn) = MONTH(GETDATE())) AND (YEAR(CreatedOn) = YEAR(GETDATE()))GROUP BY OwnerStaff_Branch[/code]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 DailyTotalBranch1 113 37Branch2 7 37Branch3 23 37Branch4 527 37Thu, 09 Sep 2010 08:55:44 GMTiklektic