cancel
Showing results for 
Search instead for 
Did you mean: 

Sales Opportunity Stage Tab - Want last updated row details in query

Former Member
0 Kudos

Dear Experts,

I have a query as under :

SELECT T1.Name, T1.OpprId, T1.OpenDate, T0.Step_Id, T0.Line, T0.OpprId, T0.U_status, T2.Name FROM OPR1 T0 INNER JOIN OOPR T1 ON T0.OpprId = T1.OpprId INNER JOIN [dbo].[@STAGE] T2 ON T0.U_status = T2.Code WHERE T1.Name = 'ORDER'

Now I want that this query should give me the last row detail in the Stage TAb of Sales opportunity. Now this query is giving me every row stage details which are put in the Stage Tab. For example in the Stage Tab of Sales Opportunity there are 4 rows of Stages, its giving me all the 4 row stages in the query. I want that only the last stage should be displayed in the query may be it's on 2 second stage or 3 rd ot 4 th. I want only last stage to be shown.

Please advise what I need to do for the above query.

Regards,

Kamlesh

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Dear Kamlesh,

Try this(you have to change the folloing given query as per your requirement)-

SELECT T0.[OpprId], T0.[Name], max(T1.[Line])

FROM OOPR T0 INNER JOIN OPR1 T1 ON T0.OpprId = T1.OpprId

group by T0.[OpprId], T0.[Name]

There are other options by checking max of the start-date and stage-key fields of the sage tableu2026. Hope this will solve your purpose.

Former Member
0 Kudos

Hi All,

I have been working in a query to generate the last stage details in the query but still not able to resolve it.

SELECT T0.Status,T0.OpprId, max(T1.Line) 'Stage',T0.OpenDate, T0.CardCode, T0.CardName, T0.Territory, T0.CloPrcnt,T1.OpprId, T1.Line, T1.OpenDate, max(T1.OpenDate) 'Open Date',T1.CloseDate, T1.Step_Id,T2.descript, T3.Name, T0.MaxSumLoc, T4.U_swl, T0.MaxSumSys, T0.U_eqno, T0.MaxSumLoc, T4.U_partno, T4.U_desc, T4.U_unit, T4.U_amt, T4.U_qty, T4.U_amt, T4.U_span FROM OOPR T0  INNER JOIN OPR1 T1 ON T0.OpprId = T1.OpprId INNER JOIN OTER T2 ON T0.Territory = T2.territryID INNER JOIN [dbo].[@STAGE]  T3 ON T1.U_status = T3.Code, [dbo].[@SALES_ENQ_ITEM]  T4 WHERE T0.OpprId = T4.U_opprNo AND T0.Status = 'O' group by T0.Status,T0.OpprId, T0.OpenDate, T0.CardCode, T0.CardName, T0.Territory, T0.CloPrcnt,T1.OpprId, T1.Line, T1.OpenDate, T1.CloseDate, T1.Step_Id,T2.descript, T3.Name, T0.MaxSumLoc, T4.U_swl, T0.MaxSumSys, T0.U_eqno, T0.MaxSumLoc, T4.U_partno, T4.U_desc, T4.U_unit, T4.U_amt, T4.U_qty, T4.U_amt, T4.U_span

The above query is generating and giving me the Last Stage through max(T1.Line) 'Stage' field. But I want to have if any opportunity Number has 1,2,3 Stage then only Max should should which is 3 but in this query all 3 are showing.

As per Noratam ''There are other options by checking max of the start-date and stage-key fields of the sage table''.

I can use the Start Date which seems to be max(T1.OpenDate) 'Open Date' but how to give a formula or write a query by which only Max Date is selected as per the stage in the query.

I think I am not able to use the Max funtion properly. Please help what is wrong with it.

Can you please help in this regard.

Regards,

kamlesh

Edited by: Kamlesh Gupta on Mar 22, 2009 11:33 PM

Former Member
0 Kudos

Dear All,

Please help me I am eagerly waiting for a solution to my problem of how to get Only the last stage detail in my query. For example in the Sales Opportunity Tab there are some opportunity with Various Stages and I want in my query that I should get the last stage details which is there in the row.

Please help.

Regards,

Kamlesh

Edited by: Rui Pereira on May 1, 2009 11:07 AM

Former Member
0 Kudos

Only if we know what is inside your UDT, we may provide the right query. What is the purpose of your UDT?

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

I have a UDF in the Sales Opportunity Stages Table linked to a UDT named Stage. In it diffrent events are there as Enquiry Booked in, Quote in Progress Department 1, Quote in Progress Department 2, Technical Details Required etc etc.

When a new Line Row is added to the stage any event in the UDF is also selcted. I want that what ever the row is there, query should bring all opportunity details with the last event and last line row details.

Now the query is showing all the line row details in several line rows are added into the Stage Tab of Sales Opportunity.

I hope I am clear to the requirement.

Regards,

Kamlesh

Former Member
0 Kudos

Hi,

try this one (could be, you need to change ORDER BY statement, i dont not the line num field)




SELECT TOP 1 T1.Name, T1.OpprId, T1.OpenDate, T0.Step_Id, T0.Line, 
T0.OpprId, T0.U_status, T2.Name
FROM OPR1 T0
INNER JOIN OOPR T1 ON T0.OpprId = T1.OpprId
INNER JOIN dbo.@STAGE T2 ON T0.U_status = T2.Code 
WHERE T1.Name = 'ORDER' 
ORDER BY T0.Line DESC

Former Member
0 Kudos

Hi Steffen ,

I tried your query but it giving me 0 results. Please help what might be the problem

SELECT TOP 1 T1.Name, T1.OpprId, T1.OpenDate, T0.Step_Id, T0.Line, 
T0.OpprId, T0.U_status, T2.Name
FROM OPR1 T0
INNER JOIN OOPR T1 ON T0.OpprId = T1.OpprId
INNER JOIN dbo.[@STAGE] T2 ON T0.U_status = T2.Code 
WHERE T1.Name = 'ORDER' 
ORDER BY T0.Line DESC

Regards,

Kamlesh

Former Member
0 Kudos

Hi Experts,

Can anyone please guide me of how to get this report.

Waiting for a positive response.

Regards,

Kamlesh

Former Member
0 Kudos

Hi Experts,

I am using the following query to extract details of Sales Opportunity

SELECT T0.OpprId, T0.Name, T0.CardCode, T0.CardName, T1.OpprId, T1.OpenDate, T1.CloseDate, T1.Step_Id, T3.Num, T3.Descript, T1.Line, T1.U_status, T2.Name, T2.U_Status FROM OOPR T0  INNER JOIN OPR1 T1 ON T0.OpprId = T1.OpprId INNER JOIN [dbo].[@STAGE]  T2 ON T1.U_status = T2.Code INNER JOIN OOST T3 ON T0.StepLast = T3.Num

My requirement is that I want all opportunity should come in the query with the last line updated details in the opr1 row.

Means in the stage tab if there are 3 rows then in the query that ooportunity should show the last line detail and not all the 3 rows. ( T1.Line )

Please help as I am stuck into this problem.

Regards,

Kamlesh