bduenges
asked on
SQL Query
I have a query where I pull data from two tables. See attached query. The current query returns fields based on deal status. I am writing a report that will pull data from the query that has a taskfunction (Column Name) of management summary or null. I also need the query to give me the last management summary entered. If the record comes back with a meeting report or other function name other than management summary, I need to show the most recent management summary. In other words I always need the most recent management summary to show on the report. Any help with writing this query will 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
Sharath S
You already have ROW_NUMBER function with filter on rn = 1. Are you still getting dupes? Can you post the result of your query and the expectd result?
ASKER
Unfortunately I cannot post the results of the query. But here is what comes back: Let's say I have a record (a Deal), in the CRM system I can assign activity to that deal. So in the filtered task table there might be 3-4 pieces of activity that is linked to the one deal. In the report I am developing I want to see the most recent management summary (Function Name). It might not be the last bit of activity posted to the system though. Someone might have posted a meeting report (function name) after the management summary. So though the row number is there, it might give me a meeting report/credit function if that was the last thing entered. Long story short, I only want to see management summary on the report and the most recent one. I am not sure if the easiest place is to do it in the query or to do it in the SSRS report itself.
It sounds like you want to use a common table expression or a derived table that is getting that separately and then LEFT OUTER JOIN it in.
i.e.,
LEFT OUTER JOIN (
SELECT ayr_regardingoppdealtaskid
, ROW_NUMBER()
OVER(PARTITION BY ayr_regardingoppdealtaskid
ORDER BY createdon DESC) ayr_RN
FROM filteredtask
WHERE ayr_functionname = 'Management Summary'
) ms ON ms.ayr_regardingoppdealtas
i.e.,
LEFT OUTER JOIN (
SELECT ayr_regardingoppdealtaskid
, ROW_NUMBER()
OVER(PARTITION BY ayr_regardingoppdealtaskid
ORDER BY createdon DESC) ayr_RN
FROM filteredtask
WHERE ayr_functionname = 'Management Summary'
) ms ON ms.ayr_regardingoppdealtas
ASKER
So where would I add this in the query. It sounds like the proper way to go.
ASKER
MWVISA1,
This seems to work great. However how do I get it to show only one record, the most recent mamangement summary. It gives me the most recent management summary, but I only want the one record and in some instances it gives me multiple records. See screen shot. Also is there any way to say give me the deal record if there is a task function = management summary or of task function is null.
Management-Summary.jpg
This seems to work great. However how do I get it to show only one record, the most recent mamangement summary. It gives me the most recent management summary, but I only want the one record and in some instances it gives me multiple records. See screen shot. Also is there any way to say give me the deal record if there is a task function = management summary or of task function is null.
Management-Summary.jpg
My suggestion was to do this on the outside query:
SELECT t1.*, ms.ManagementSummary
FROM (...) AS t1
LEFT OUTER JOIN (
SELECT ayr_regardingoppdealtaskid
, ROW_NUMBER()
OVER(PARTITION BY ayr_regardingoppdealtaskid
ORDER BY createdon DESC) ayr_RN
FROM filteredtask
WHERE ayr_functionname = 'Management Summary'
) ms ON ms.ayr_regardingoppdealtas
WHERE t1.rn = 1
What that should have done was keep your original records in tact, but always display for you the most recent management summary.
If you want that in the main query, just add the other columns of data you want to see from filteredtask and put the derived table in the main JOIN instead of on the outside.
i.e., in place of this:
LEFT OUTER JOIN filteredtask
ON filteredtask.ayr_regarding
Hope that helps!
SELECT t1.*, ms.ManagementSummary
FROM (...) AS t1
LEFT OUTER JOIN (
SELECT ayr_regardingoppdealtaskid
, ROW_NUMBER()
OVER(PARTITION BY ayr_regardingoppdealtaskid
ORDER BY createdon DESC) ayr_RN
FROM filteredtask
WHERE ayr_functionname = 'Management Summary'
) ms ON ms.ayr_regardingoppdealtas
WHERE t1.rn = 1
What that should have done was keep your original records in tact, but always display for you the most recent management summary.
If you want that in the main query, just add the other columns of data you want to see from filteredtask and put the derived table in the main JOIN instead of on the outside.
i.e., in place of this:
LEFT OUTER JOIN filteredtask
ON filteredtask.ayr_regarding
Hope that helps!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Issue was resolved on my own with no help from comments