SQL Query Question

I have a query that pulls data from several tables.  In the query which I am using to fuel a SSRS report, I need the query to only return the latest record from one of the tables.  In other words, I create a "deal" and then I create tasks based on that deal.  One Deal, might have several tasks, but within the report I always want to see the deal, but only the last entered task.  I have enclosed the query and was not sure how to handle that in the code.

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
FROM   opportunity
       INNER JOIN accountbase
         ON opportunity.accountid = accountbase.accountid
       LEFT OUTER JOIN filteredtask
         ON filteredtask.ayr_regardingoppdealtaskid = opportunity.opportunityid  
/* WHERE  ( opportunity.ayr_dealstatus <> 8 ) 
       AND ( filteredtask.createdon IS NULL 
              OR filteredtask.createdon = '03/24/2011' 
       AND ( 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 IN ( @DealStatus ) ) 
       AND ( 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 IN ( @DealType ) ) )
ORDER  BY opp_dtcreated DESC */

Open in new window

bduengesAsked:
Who is Participating?
 
SharathData EngineerCommented:
I think I figured out the column names, try this.
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) AS t1
 WHERE rn = 1 
            /* WHERE  ( opportunity.ayr_dealstatus <> 8 )  
       AND ( filteredtask.createdon IS NULL  
              OR filteredtask.createdon = '03/24/2011'  
       AND ( 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 IN ( @DealStatus ) )  
       AND ( 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 IN ( @DealType ) ) ) 
ORDER  BY opp_dtcreated DESC */

Open in new window

0
 
SharathData EngineerCommented:
What are the columns in your query for deal and last entered task date?
0
 
bduengesAuthor Commented:
Great, quick solution.  Thanks
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.

All Courses

From novice to tech pro — start learning today.