Hi
Here is my base data -
Company Ownership Start Date Item Ref
2 19/02/2005 101087510109
7 01/08/2011 101087510109
9 11/05/2012 101087510109
2 28/06/2012 101087510109
9 30/07/2012 101087510109
Here is my query -
SELECT tblBaseData.[Company], tblBaseData.[Ownership Start Date], tblBaseData.[Item Ref], Nz(tblBaseData_1.[Ownership Start Date]-1,date()) AS End_Date
FROM tblBaseData LEFT JOIN tblBaseData AS tblBaseData_1 ON (tblBaseData.Item Ref=tblBaseData_1.Item Ref) AND (tblBaseData.Ownership Start Date<tblBaseData_1.Start_Date) AND (tblBaseData.Company<>tblBaseData_1.Company);
Here is my current result set -
Company Ownership Start Date Item Ref End_Date
2 19-Feb-05 101087510109 29/07/2012
2 19-Feb-05 101087510109 10/05/2012
2 19-Feb-05 101087510109 31/07/2011
7 01-Aug-11 101087510109 29/07/2012
7 01-Aug-11 101087510109 27/06/2012
7 01-Aug-11 101087510109 10/05/2012
9 11-May-12 101087510109 27/06/2012
2 28-Jun-12 101087510109 29/07/2012
9 30-Jul-12 101087510109 12/10/2012
The rows that a bold and striked through are not required. Does anyone know how to reduce the dataset to the desired 5 rows? Is it a date formatting issue?
Any help/tips would be much appreciated.
Kind Regards
Ted
Here is my base data -
Company Ownership Start Date Item Ref
2 19/02/2005 101087510109
7 01/08/2011 101087510109
9 11/05/2012 101087510109
2 28/06/2012 101087510109
9 30/07/2012 101087510109
Here is my query -
SELECT tblBaseData.[Company], tblBaseData.[Ownership Start Date], tblBaseData.[Item Ref], Nz(tblBaseData_1.[Ownership Start Date]-1,date()) AS End_Date
FROM tblBaseData LEFT JOIN tblBaseData AS tblBaseData_1 ON (tblBaseData.Item Ref=tblBaseData_1.Item Ref) AND (tblBaseData.Ownership Start Date<tblBaseData_1.Start_Date) AND (tblBaseData.Company<>tblBaseData_1.Company);
Here is my current result set -
Company Ownership Start Date Item Ref End_Date
2 19-Feb-05 101087510109 10/05/2012
2 19-Feb-05 101087510109 31/07/2011
7 01-Aug-11 101087510109 27/06/2012
7 01-Aug-11 101087510109 10/05/2012
9 11-May-12 101087510109 27/06/2012
2 28-Jun-12 101087510109 29/07/2012
9 30-Jul-12 101087510109 12/10/2012
The rows that a bold and striked through are not required. Does anyone know how to reduce the dataset to the desired 5 rows? Is it a date formatting issue?
Any help/tips would be much appreciated.
Kind Regards
Ted