Select Query - Too Many Records Returned 2

[EnergyTed]

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

 

[Golom]

I assume that [red]Start_Date[/red] should be [red][Ownership Start Date][/red] in your query. The records being returned do seem to conform to your ON conditions. How did you determine that the records indicated should not appear?

The only pattern that I can see is that you want to keep the record with the earliest End_Date but not the others for a given Company and Ownership Start Date. If that is the case then try something like

Select Company, [Ownership Start Date], [Item Ref], MIN(End_Date) As ED

FROM

(
SELECT tblBaseData.[Company], tblBaseData.[Ownership Start Date], tblBaseData.[Item Ref], 
Nz(tblBaseData_1.[Ownership Start Date]-1,date()) AS End_Date, (tblBaseData.[seq] & " & " & tblBaseData_1.[seq]) as C1

FROM tblBaseData LEFT JOIN tblBaseData AS tblBaseData_1 
ON (tblBaseData.[Item Ref]=tblBaseData_1.[Item Ref]) 
AND (tblBaseData.[Ownership Start Date]<tblBaseData_1.[Ownership Start Date]) 
AND (tblBaseData.Company<>tblBaseData_1.Company)) As X

GROUP BY Company, [Ownership Start Date], [Item Ref]

ORDER BY  [Ownership Start Date]

That gives me
[pre]
Company Ownership Start Date Item Ref ED
2 19/02/2005 101087510109 10/05/2012
7 01/08/2011 101087510109 10/05/2012
9 11/05/2012 101087510109 27/06/2012
2 28/06/2012 101087510109 29/07/2012
9 30/07/2012 101087510109 12/10/2012[/pre]

 

[PHV]

What about this ?

SELECT A.Company,A.[Ownership Start Date],A.[Item Ref],Min(Nz(B.[Ownership Start Date]-1,Date())) AS End_Date
FROM tblBaseData A LEFT JOIN tblBaseData B ON A.[Item Ref]=B.[Item Ref] AND A.[Ownership Start Date]<B.[Ownership Start Date] AND A.Company<>B.Company
GROUP BY A.[Item Ref],A.[Ownership Start Date],A.Company

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EnergyTed]

Hi Golom and PHV

Both of your tips have reduced the dataset to the required 5 rows, however the first row being returned is wrong -

The End_date should be 31/07/2011.

Company Ownership Start Date Item Ref ED
2 19/02/2005 101087510109 10/05/2012

This should be -

2, 19/02/2005, 101087510109, 31/07/2011
7, 01/08/2011, 101087510109, 10/05/2012

Any ideas?

Ted

 

[Golom]

Using PHV's which is more concise than mine

SELECT A.Company,A.[Ownership Start Date],A.[Item Ref],
       Min(Cdate(Nz(B.[Ownership Start Date]-1,Date()))) AS End_Date

FROM tblBaseData A LEFT JOIN tblBaseData B 
ON A.[Item Ref]=B.[Item Ref] 
AND A.[Ownership Start Date]<B.[Ownership Start Date] 
AND A.Company<>B.Company

GROUP BY A.[Item Ref],A.[Ownership Start Date],A.Company

Which Gives
[pre]
Company Ownership Start Date Item Ref End_Date
2 19/02/2005 101087510109 7/31/2011
7 01/08/2011 101087510109 5/10/2012
9 11/05/2012 101087510109 6/27/2012
2 28/06/2012 101087510109 7/29/2012
9 30/07/2012 101087510109 10/12/2012[/pre]

 

[EnergyTed]

Hi Golom and PHV

Fantastic, this works great.

Much appreciated.


Ted.