asked on

SQL Query

I have a query I am using for a report that returns info from two separate tables. Currently the query will return results based on functionname =management summary and new_typeofopportunity=Aircraft Placement and it only returns one row of data per dealname or accountbase.name. I need to change this so that when the data is returned, the data returns only one management summary per dealname based on MSN. In other words, I have an activity report that users enter data to and assign to the deal name. I am creating a report that when run, will group by MSN and only show the last management summary entered for a particular account. So I might have 4 accounts entering management summaries. I only want to see one summary per account. Any help would be appreciated.

bduenges

ASKER

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,
               Opportunity.ayr_msnfreeform													AS MSN,
               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 filteredtask.ayr_functionname = 'Management Summary' AND opportunity.new_typeofopportunity in (2)) 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

bduenges

ASKER

Actually the requirements on this has changed. Right now It gives me back data when Task_Function=ManagementSummary and opp_dealtype<> aircraft placement. I need the data to come back as I only wanted to see deals that are not aircraft placement and task function can be only NULL or Management Summary along with Deal Status as stated in query.

Nico Bontenbal

Please check this example to see how you can get the last task with specific criteria from the task table. This sample uses temp tables so it doesn't change anything in your database.

--create temp sample tables
set nocount on
create table #opportunity (Opportunityid int, Name varchar(10))
create table #task (TaskID int, Opportunityid int, DateCreated datetime, TaskFunction varchar(100), Description varchar(100))

--fill tables with date
insert into #opportunity values (1,'op1')
insert into #opportunity values (2,'op2')
insert into #opportunity values (3,'op3')
insert into #opportunity values (4,'op4')
insert into #task values (1,1,'20110101','Management Summary','op1 task1')
insert into #task values (2,1,'20110201','Other','op1 task2')
insert into #task values (3,1,'20110301','Management Summary','op1 task3')
insert into #task values (4,2,'20110101','Management Summary','op2 task1')
insert into #task values (5,2,'20110201','Other','op2 task2')
insert into #task values (6,2,'20110301','Other','op2 task3')
insert into #task values (7,3,'20110101','Other','op3 task1')
insert into #task values (8,3,'20110201','Other','op3 task2')

--actual query
select 
    opp.Opportunityid,
    opp.Name,
    task.DateCreated,
    Task.Description 
from
    #opportunity opp
    left join
    (select * from
        (select 
            TaskID,
            Opportunityid,
            DateCreated,
            Description,
            ROW_NUMBER() OVER (PARTITION BY Opportunityid ORDER BY DateCreated desc) as row
        from
            #task
        where
            TaskFunction = 'Management Summary'
        ) as TaskWithRow
    where 
        TaskWithRow.row = 1
    ) Task
    on opp.Opportunityid = Task.Opportunityid 

--drop temp tables
drop table #opportunity
drop table #task

Open in new window

The trick is in the part

select 
    TaskID,
    Opportunityid,
    DateCreated,
    Description,
    ROW_NUMBER() OVER (PARTITION BY Opportunityid ORDER BY DateCreated desc) as row
from
    #task
where
    TaskFunction = 'Management Summary'

Open in new window

This adds a rownumber to the rows for each opportunity based on create data. On the next level this rownumber is filtered to get only rownumber 1, which is the one with the latest createdate.

I suppose you could adapt this technique to your situation. And other criteria you want to apply to the tasks you'll add at the

TaskFunction = 'Management Summary'

Open in new window

part. Any criteria you want to apply to the opportunities you can add at the end of the query.

ASKER CERTIFIED SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bduenges

ASKER

patelalpesh,

Receive this error when i parse and execute the query:

Msg 156, Level 15, State 1, Line 64
Incorrect syntax near the keyword 'AS'.