I've created a query:
SELECT DISTINCT co.CL_Desc,a.AN_Key,a.CL_Code,a.AT_Code,a.AN_Refer ence,a.AN_ToDo,a.An_Closed
,at2.CU_Value2,an1.CU_Value1,at3.CU_Value5 /* INNER JOIN FIELDS */
FROM clients co
LEFT OUTER JOIN Action a ON co.CL_Code = a.CL_Code /* Get actions */
LEFT OUTER JOIN ActionCustomTxt2 at2 ON a.AN_Key=at2.AN_Key /* Project checking */
LEFT OUTER JOIN ActionCustomNum1 an1 ON a.AN_Key=an1.AN_Key /* Date created */
LEFT OUTER JOIN AECTESUSER.ActionCustomTxt3 at3 ON a.AN_Key=at3.AN_Key /* Diagnostic levels */
WHERE AT_Code IN ('ENQUIRY','DIAGNOSTIC','ONWARD REFERRAL','CONSULTANCY/CONTRACT')
AND at2.CU_Value2='40201'
AND (an1.CU_Value1 BETWEEN 37851 AND 37857)
ORDER BY CL_Desc,a.AN_Reference
And it returns somthing like
CompanyA,Enquiry,04/09/2003
CompanyA,Contract,05/09/2003
ComanyB,Enquiry,04/09/2003
And so on
I know this is not true duplcates but for me its a problem. I only want the last record of each company to display being the most recent record. I thought I union query may have helped to solve this, but no luck has anyone got any ideas?What is your last record? The record with the highest ID per company? Select those ID's per company, and INNER JOIN the result with your original table!|||DB's general solution in code:
(I assumed that AN_Closed was the date value and that you wanted the latest one, rather than an ID value.) Because you are using OUTER joins, I think you need two levels of nesting to be sure you get all the data you want, but I couldn't test the code.
SELECT DISTINCT co.CL_Desc, a.AN_Key, a.CL_Code, a.AT_Code, a.AN_Reference, a.AN_ToDo, a.An_Closed, at2.CU_Value2, an1.CU_Value1, at3.CU_Value5 /* INNER JOIN FIELDS */
FROM clients co
LEFT OUTER JOIN /* Get last actions */
LEFT OUTER JOIN
(select An_Key, CL_Code, AT_Code, AN_Reference, AN_ToDo, AN_Closed
from Action
inner join (select CL_Code, max(AN_Closed) Last_AN_Closed from Action group by CL_Code) Last_Values
on Action.CL_Code = Last_Values.CL_Code and Action.AN_Closed = Last_Values.Last_An_Closed) a
ON co.CL_Code = a.CL_Code
LEFT OUTER JOIN ActionCustomTxt2 at2 ON a.AN_Key=at2.AN_Key /* Project checking */
LEFT OUTER JOIN ActionCustomNum1 an1 ON a.AN_Key=an1.AN_Key /* Date created */
LEFT OUTER JOIN AECTESUSER.ActionCustomTxt3 at3 ON a.AN_Key=at3.AN_Key /* Diagnostic levels */
WHERE AT_Code IN ('ENQUIRY','DIAGNOSTIC','ONWARD REFERRAL','CONSULTANCY/CONTRACT')
AND at2.CU_Value2='40201'
AND (an1.CU_Value1 BETWEEN 37851 AND 37857)
ORDER BY CL_Desc,a.AN_Reference
blindman
Friday, March 9, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment