Solved

SQL Query Help:  Grouping Issues and Performance Improvement

Posted on 2012-03-15
9
224 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 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: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

Technology Partners: 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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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