Solved

SQL Query Help:  Grouping Issues and Performance Improvement

Posted on 2012-03-15
9
219 Views
Last Modified: 2012-03-16
Please see the query below.  I have 2 questions that I need help with.

1.  The results are not "grouping" by owneridname.  I know that it is because of the sub queries, but I am not sure how to get the sub queries to "roll up" to the main query's group by function.

   
 OwnerIdName	<45	46-90	91-120	121>
name1	242	137	33	229
name2	242	137	33	229
name 3	242	137	33	229
name 4	242	137	33	229
name 5	242	137	33	229
name 6	242	137	33	229
name 7	242	137	33	229 

Open in new window


2.  I don't think this query is written to perform very well.  Any optimization that you could do to make it faster?

Thanks,

Ron

Query:
SELECT  OwnerIdName,
        [<45] = ( SELECT    COUNT(*)
                  FROM      dbo.Opportunity
                  WHERE     DATEDIFF(d, GETDATE(), EstimatedCloseDate) <= '45'
                            AND StateCode = '0'
                            AND OpportunityId NOT IN (
                            SELECT  RegardingObjectId
                            FROM    dbo.Appointment
                            WHERE   DATEDIFF(d, GETDATE(), ScheduledStart) >= '-7' )
                ),
        [46-90] = ( SELECT  COUNT(*)
                    FROM    dbo.Opportunity
                    WHERE   DATEDIFF(d, GETDATE(), EstimatedCloseDate) >= '46'
                            AND DATEDIFF(d, GETDATE(), EstimatedCloseDate) <= '90'
                            AND StateCode = '0'
                            AND OpportunityId NOT IN (
                            SELECT  RegardingObjectId
                            FROM    dbo.Appointment
                            WHERE   DATEDIFF(d, GETDATE(), ScheduledStart) >= '-14' )
                  ),
        [91-120] = ( SELECT COUNT(*)
                     FROM   dbo.Opportunity
                     WHERE  DATEDIFF(d, GETDATE(), EstimatedCloseDate) >= '91'
                            AND DATEDIFF(d, GETDATE(), EstimatedCloseDate) <= '120'
                            AND StateCode = '0'
                            AND OpportunityId NOT IN (
                            SELECT  RegardingObjectId
                            FROM    dbo.Appointment
                            WHERE   DATEDIFF(d, GETDATE(), ScheduledStart) >= '-30' )
                   ),
        [121>] = ( SELECT   COUNT(*)
                   FROM     dbo.Opportunity
                   WHERE    DATEDIFF(d, GETDATE(), EstimatedCloseDate) >= '121'
                            AND StateCode = '0'
                            AND OpportunityId NOT IN (
                            SELECT  RegardingObjectId
                            FROM    dbo.Appointment
                            WHERE   DATEDIFF(d, GETDATE(), ScheduledStart) >= '-60' )
                 )
FROM    dbo.Opportunity
WHERE   StateCode = '0'
GROUP BY dbo.Opportunity.OwnerIdName

Open in new window

0
Comment
Question by:r270ba
9 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 37725304
you can do this with a sum (case).

Essentially you're running the same query 5 times:

SELECT  OwnerIdName,
sum ( case when  DATEDIFF(d, GETDATE(), EstimatedCloseDate) <= '45'
           AND OpportunityId NOT IN (
                            SELECT  RegardingObjectId
                            FROM    dbo.Appointment
                            WHERE   DATEDIFF(d, GETDATE(), ScheduledStart) >= '-7')
             then 1 else 0 end
          )
[<45]

--repeat for others
--,  sum (case when ...
FROM    dbo.Opportunity
WHERE   StateCode = '0'
GROUP BY dbo.Opportunity.OwnerIdName
0
 
LVL 25

Expert Comment

by:jogos
ID: 37725344
There is no link between select in subquery and the main query -> always same

You shouldn't repeat in subselect 'select count(*) from dbo.Opportunity' while that count can be done using a CASE WHEN.

AND OpportunityId NOT IN is always better written as AND NOT EXISTS + test link in subquery

Something like this will implement those 3 remarks (repeat for other columns)
It may be possible to move the not exists subquery to a join but not sure about the content.

SELECT  o.OwnerIdName,
        [<45] = sum( case when  DATEDIFF(d, GETDATE(), o.EstimatedCloseDate) <= '45'
                            AND o.StateCode = '0'
                            AND not exists (
                            SELECT  RegardingObjectId
                            FROM    dbo.Appointment as a
                            WHERE  a.RegardingObjectId = o.OpportunityId 
                            AND  DATEDIFF(d, GETDATE(), a.ScheduledStart) >= '-7' )
                          THEN 1 
                          ELSE 0 
                     END
                )
 -- others same way
FROM    dbo.Opportunity as o
WHERE   o.StateCode = '0'
GROUP BY o.OwnerIdName

Open in new window

0
 
LVL 25

Expert Comment

by:jogos
ID: 37725381
Ok parall post by: ged325 (and he was first).

Worth to take from my solution is the table alias (o) and certainly the not exists.
But don't forget a ') ' before the THEN because I seemed to forgot to close my subquery
0
 

Author Comment

by:r270ba
ID: 37725442
For both I receive the following error:


Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

jogos...I do not see where you left out the ')'.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37725541
A two-step using CTE http://msdn.microsoft.com/en-us/library/ms175972.aspx
;with cte_owner (ownerIdName, [<45] )
AS 
SELECT  o.OwnerIdName,
         case when  DATEDIFF(d, GETDATE(), o.EstimatedCloseDate) <= '45'
                            AND o.StateCode = '0'
                            AND not exists (
                            SELECT  RegardingObjectId
                            FROM    dbo.Appointment as a
                            WHERE  a.RegardingObjectId = o.OpportunityId 
                            AND  DATEDIFF(d, GETDATE(), a.ScheduledStart) >= '-7' )
                          THEN 1 
                          ELSE 0 
                     END  [<45]
                   
              
 -- others same way
FROM    dbo.Opportunity as o
WHERE   o.StateCode = '0'
)
Select  OwnerIdName, sum( [<45]) as  [<45]
from cte_owner
GROUP BY OwnerIdName

Open in new window


PS the ')' was just out of my window.
0
 

Author Comment

by:r270ba
ID: 37725806
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.
0
 

Author Comment

by:r270ba
ID: 37725840
Figured it out...there was a '(' missing right after the AS on line 2

Results seem to be correct.  Let me do a little more looking at the data and I should be ready to award points!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37726474
You could likely even more time by pre-processing dbo.Appointment data and storing it in a temp table and using the temp table in the main query in place of the original dbo.Appointment.

If you want to add that as well, just let me know.
0
 

Author Closing Comment

by:r270ba
ID: 37729166
Thanks for all the help!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now