?
Solved

SQL Query

Posted on 2011-05-05
5
Medium Priority
?
185 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:bduenges
  • 3
5 Comments
 

Author Comment

by:bduenges
ID: 35700989

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

0
 

Author Comment

by:bduenges
ID: 35701320
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.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35703984
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.
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 2000 total points
ID: 35719693

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 opportunity.new_typeofopportunity in (2)
AND ((deals <> 'aircraft placement' AND TaskFunction IS NULL) OR  (TaskFunction  = 'Management Summary' AND AS Above)
 )
) AS t1
               
 WHERE rn = 1

Open in new window

0
 

Author Comment

by:bduenges
ID: 35720104
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'.

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 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