Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SubSelect Statement not returning accurate results Expand / Collapse
Author
Message
Posted Thursday, September 9, 2010 8:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 28, 2013 9:58 AM
Points: 54, Visits: 161
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:

Branch MonthlyTotal DailyTotal
Branch1 113 37
Branch2 7 37
Branch3 23 37
Branch4 527 37

Post #983189
Posted Thursday, September 9, 2010 9:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
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
Exploring Recursive CTEs by Example Dwain Camps
Post #983211
Posted Thursday, September 9, 2010 9:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 28, 2013 9:58 AM
Points: 54, Visits: 161
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.

Post #983219
Posted Thursday, September 9, 2010 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
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


SELECT 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 DailyTotal
FROM WorkOrderReportView w
WHERE CreatedOn >= @Startmonth AND CreatedOn < @Endmonth
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
Exploring Recursive CTEs by Example Dwain Camps
Post #983227
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse