SQL Query

Posted on 2011-05-05
Last Modified: 2012-05-11
I have a query where I pull data from two tables.  See attached query.  The current query returns fields based on deal status. I am writing a report that will pull data from the query that has a taskfunction (Column Name) of management summary or null.  I also need the query to give me the last management summary entered.  If the record comes back with a meeting report or other function name other than management summary, I need to show the most recent management summary.  In other words I always need the most recent management summary to show on the report.  Any help with writing this query will be appreciated.
/* 4.27.11 - Bill Duenges - This query is used for Deal report.  Only brings data over that has a deal status of High Probability, Medium Probability and Near Term Action Required. */

  FROM (SELECT opportunity.opportunityid                                                  AS opp_oppid,
               opportunity.ayr_opportunitytimeframe                                       AS opp_enddate,
               opportunity.ayr_dealstartdate                                              AS opp_startdate,
               opportunity.ayr_dealnumaircraft                                            AS opp_numofaircraft,
               opportunity.ayr_deliverydate                                               AS opp_deliverydate,
               opportunity.ayr_dealactype                                                 AS opp_variant,
               opportunity.ayr_dealengine                                                 AS opp_engine,
               opportunity.ayr_dealyom                                                    AS opp_yom,
               opportunity.ayr_dealrent                                                   AS opp_rent,
               opportunity.ayr_dealleaseterm                                              AS opp_leaseterm,
               opportunity.ayr_dealdebt                                                   AS opp_debt,
               opportunity.ayr_dealassumedprice                                           AS opp_assumedprice,
               opportunity.ayr_dealtotaldealsize                                          AS opp_totaldealsize,
               opportunity.ayr_dealstage                                                  AS opp_dealstage,
               opportunity.createdon                                                      AS opp_dtcreated,
               Opportunity.Name                                                           AS Name,
                                                                AS DealName,
                 WHEN opportunity.ayr_dealstatus = 1 THEN 'Potential Opportunity' 
                 WHEN opportunity.ayr_dealstatus = 3 THEN 'Near Term Action Required' 
                 WHEN opportunity.ayr_dealstatus = 4 THEN 'Awaiting Cust Feedback' 
                 WHEN opportunity.ayr_dealstatus = 5 THEN 'Medium Probability' 
                 WHEN opportunity.ayr_dealstatus = 6 THEN 'High Probability' 
                 WHEN opportunity.ayr_dealstatus = 7 THEN 'Executed LOI' 
                 WHEN opportunity.ayr_dealstatus = 8 THEN 'Dead' 
                 WHEN opportunity.ayr_dealstatus = 9 THEN 'Lost' 
                 WHEN opportunity.ayr_dealstatus = 10 THEN 'AYR Remarketing Aircraft' 
                 WHEN opportunity.ayr_dealstatus = 11 THEN 'Owned' 
                 ELSE 'ZZZFAILZZZ' 
               END AS opp_dealstatus, 
                 WHEN opportunity.ayr_dealvalueaddcat = 1 THEN 'Freighter Conversion' 
                 WHEN opportunity.ayr_dealvalueaddcat = 2 THEN 'No Lease' 
                 WHEN opportunity.ayr_dealvalueaddcat = 3 THEN 'SLB Old' 
                 WHEN opportunity.ayr_dealvalueaddcat = 4 THEN 'SLB New' 
                 WHEN opportunity.ayr_dealvalueaddcat = 5 THEN 'Lease Placement' 
                 WHEN opportunity.ayr_dealvalueaddcat = 6 THEN 'None' 
               END AS opp_valueaddcat, 
                 WHEN opportunity.new_typeofopportunity = 2 THEN 'Aircraft Placement' 
                 WHEN opportunity.new_typeofopportunity = 4 THEN 'Purchase w/o Lease' 
                 WHEN opportunity.new_typeofopportunity = 5 THEN 'Aircraft Sale' 
                 WHEN opportunity.new_typeofopportunity = 6 THEN 'Other' 
                 WHEN opportunity.new_typeofopportunity = 9 THEN 'Purchase Leaseback' 
                 WHEN opportunity.new_typeofopportunity = 10 THEN 'Purchase w/Lease' 
               END AS opp_dealtype, 
               filteredtask.ayr_regardingoppdealtaskid                                    AS task_oppid,
               filteredtask.ayr_functionname                                              AS task_function,
               filteredtask.regardingobjectidname                                         AS task_targetacct,
               filteredtask.createdon                                                     AS task_createddt,
               filteredtask.createdbyname                                                 AS task_createdby,
               filteredtask.DESCRIPTION                                                   AS task_notes,
                 OVER(PARTITION BY ORDER BY filteredtask.createdon DESC) rn 
          FROM opportunity 
               INNER JOIN accountbase 
                 ON opportunity.accountid = accountbase.accountid 
               LEFT OUTER JOIN filteredtask 
                 ON filteredtask.ayr_regardingoppdealtaskid = opportunity.opportunityid
                WHERE opportunity.ayr_dealstatus in (3,5,6)) AS t1 
 WHERE rn = 1

Open in new window

Question by:bduenges
    LVL 40

    Expert Comment

    You already have ROW_NUMBER function with filter on rn = 1. Are you still getting dupes? Can you post the result of your query and the expectd result?

    Author Comment

    Unfortunately I cannot post the results of the query.  But here is what comes back:  Let's say I have a record (a Deal), in the CRM system I can assign activity to that deal.  So in the filtered task table there might be 3-4 pieces of activity that is linked to the one deal.  In the report I am developing I want to see the most recent management summary (Function Name).  It might not be the last bit of activity posted to the system though.  Someone might have posted a meeting report (function name) after the management summary.  So though the row number is there, it might give me a meeting report/credit function if that was the last thing entered.  Long story short, I only want to see management summary on the report and the most recent one.  I am not sure if the easiest place is to do it in the query  or to do it in the SSRS report itself.
    LVL 59

    Expert Comment

    by:Kevin Cross
    It sounds like you want to use a common table expression or a derived table that is getting that separately and then LEFT OUTER JOIN it in.

       SELECT ayr_regardingoppdealtaskid, DESCRIPTION as ManagementSummary
            , ROW_NUMBER()
                 OVER(PARTITION BY ayr_regardingoppdealtaskid
                           ORDER BY createdon DESC) ayr_RN
       FROM filteredtask
       WHERE ayr_functionname = 'Management Summary'
    ) ms ON ms.ayr_regardingoppdealtaskid = t1.task_oppid and ms.ayr_RN = 1

    Author Comment

    So where would I add this in the query. It sounds like the proper way to go.  

    Author Comment


    This seems to work great.  However how do I get it to show only one record, the most recent mamangement summary.  It gives me the most recent management summary, but I only want the one record and in some instances it gives me multiple records.  See screen shot.  Also is there any way to say give me the deal record if there is a task function = management summary or of task function is null.
    LVL 59

    Expert Comment

    by:Kevin Cross
    My suggestion was to do this on the outside query:

    SELECT t1.*, ms.ManagementSummary
    FROM (...) AS t1
       SELECT ayr_regardingoppdealtaskid, DESCRIPTION as ManagementSummary
            , ROW_NUMBER()
                 OVER(PARTITION BY ayr_regardingoppdealtaskid
                           ORDER BY createdon DESC) ayr_RN
       FROM filteredtask
       WHERE ayr_functionname = 'Management Summary'
    ) ms ON ms.ayr_regardingoppdealtaskid = t1.task_oppid and ms.ayr_RN = 1
    WHERE t1.rn = 1

    What that should have done was keep your original records in tact, but always display for you the most recent management summary.  

    If you want that in the main query, just add the other columns of data you want to see from filteredtask and put the derived table in the main JOIN instead of on the outside.

    i.e., in place of this:
    LEFT OUTER JOIN filteredtask
       ON filteredtask.ayr_regardingoppdealtaskid = opportunity.opportunityid

    Hope that helps!

    Accepted Solution

    Issue was resolved on my own.  No comments were used in final outcome.

    Author Closing Comment

    Issue was resolved on my own with no help from comments

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    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

    A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. (htt…
    Introduction Earlier I wrote an article about the new lookup functions ( that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 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

    9 Experts available now in Live!

    Get 1:1 Help Now