bduenges
asked on
Exclusion QUERY
I have a query that is working fine however I need to see all records except when type of opportunity = Aircraft Placement. I have attached the code. any help would 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
/* 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 */
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
WHERE opportunity.ayr_dealstatus in (3,5,6)
AND opportunity.new_typeofopportunity <> 2) AS t1
WHERE rn = 1
You can just filter it in your where clause
WHERE rn = 1
AND opportunity.new_typeofoppo rtunity <> 2
AND ( filteredtask.createdon IS NULL
OR filteredtask.createdon = '03/24/2011'
WHERE rn = 1
AND opportunity.new_typeofoppo
AND ( filteredtask.createdon IS NULL
OR filteredtask.createdon = '03/24/2011'
ASKER