• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

SQL Query Help: Grouping Issues and Performance Improvement

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
r270ba
Asked:
r270ba
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
jogosCommented:
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
 
jogosCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
r270baAuthor Commented:
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
 
jogosCommented:
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
 
r270baAuthor Commented:
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
 
r270baAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
r270baAuthor Commented:
Thanks for all the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now