Solved

SQL Query Help:  Grouping Issues and Performance Improvement

Posted on 2012-03-15
9
220 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 40

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

929 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

13 Experts available now in Live!

Get 1:1 Help Now