SubSelect Statement not returning accurate results

Question

Post reply

SubSelect Statement not returning accurate results

iklektic

Ten Centuries

Points: 1236
More actions
September 9, 2010 at 8:55 am

#157192

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
FROM WorkOrderReportView
WHERE (dbo.DateOnly(CreatedOn) = dbo.DateOnly(GETDATE()))
) AS DailyTotal
FROM WorkOrderReportView
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:
BranchMonthlyTotalDailyTotal
Branch1113 37
Branch27 37
Branch323 37
Branch4527 37

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

You forgot to correlate your correlated subquery:

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 DailyTotal

FROM WorkOrderReportView w

WHERE (MONTH(CreatedOn) = MONTH(GETDATE())) AND (YEAR(CreatedOn) = YEAR(GETDATE()))

GROUP BY OwnerStaff_Branch

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

iklektic Ten Centuries Points: 1236 More actions · Answer 2

Thanks! 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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

No worries.

Consider making your WHERE clauses SARGable:

DECLARE @Startmonth DATE, @Endmonth DATE, @Startday DATE, @Endday DATE

SET @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, @Endday