?
Solved

SQL Query Help:  Grouping Issues and Performance Improvement

Posted on 2012-03-15
9
Medium Priority
?
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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
 
LVL 25

Accepted Solution

by:
jogos earned 2000 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:Scott Pletcher
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

765 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