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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
What are the columns in your query for deal and last entered task date?
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bduengesAuthor Commented:
Great, quick solution.  Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.