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

SQL Query

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. */


SELECT * 
  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,
               AccountBase.name                                                           AS DealName,
               CASE 
                 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, 
               CASE 
                 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, 
               CASE 
                 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,
               opportunity.createdbyname, 
               ROW_NUMBER() 
                 OVER(PARTITION BY AccountBase.name 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

0
bduenges
Asked:
bduenges
  • 5
  • 2
1 Solution
 
SharathData EngineerCommented:
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?
0
 
bduengesAuthor Commented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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.

i.e.,
LEFT OUTER JOIN (
   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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
bduengesAuthor Commented:
So where would I add this in the query. It sounds like the proper way to go.  
0
 
bduengesAuthor Commented:
MWVISA1,

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.
Management-Summary.jpg
0
 
Kevin CrossChief Technology OfficerCommented:
My suggestion was to do this on the outside query:

SELECT t1.*, ms.ManagementSummary
FROM (...) AS t1
LEFT OUTER JOIN (
   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!
0
 
bduengesAuthor Commented:
Issue was resolved on my own.  No comments were used in final outcome.
0
 
bduengesAuthor Commented:
Issue was resolved on my own with no help from comments
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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