Issues with Grouping Data according to the Date.

[Nassy]

Hi

Imagine I have the following set of data relating to properties and the vendors of those properties that are entered into the Diary to show a property's history.


PropertyID Diary Date VendorID
5 2003 - 04 - 23 20
5 2003 - 02 - 05 20
5 2003 - 01 - 02 21
6 2003 - 10 - 14 26
6 2003 - 09 - 21 26
6 2002 - 06 - 09 27



What I would like to do (But can't think how):


I would like to remove the vendor history from this set of data so that the resultant set of data is as follows:


PropertyID Diary Date VendorID
5 2003 - 04 - 23 20
5 2003 - 02 - 05 20
6 2003 - 10 - 14 26
6 2003 - 09 - 21 26


I have tried to achieve this by getting the maximum Diary Date and grouping by PropertyID in the first instance and then trying to join this information back onto the original table. However this will just return me one row for each property. What I want (as shown above) is to return all the rows relating to the most recent vendor.

How can I do this? I have been pulling my hairs out over this one!

Thanks

Nassy

 

[nigelrivett]

delete tbl
from tbl t1
where exists (select * from tbl t2 where t1.PropertyID = t2.PropertyID and t1.Diary Date > t2.Diary Date and t1.VendorID <> t2.VendorID)








======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[sunila7]

Try this :

SELECT * FROM TBLName T1
INNER JOIN (SELECT PropertyID,Min(VendorID) FROM Tblname GROUP BY PropertyID) TBL ON TBL.PropertyId = T1.PropertyID


Sunil

 

[dreameR25]

Nigel your script returns two rows only

5 2003-01-02 00:00:00.000 21
6 2002-06-09 00:00:00.000 27

How about :

SELECT *
FROM TBLAS A
WHERE VENDORID IN (SELECT VENDORID
FROM TBL
GROUP BY PROPERTYID, VENDORID
HAVING COUNT(*) > 1)

 

[dreameR25]

Sunila, your query produces :

5 2003-04-23 00:00:00.000 20 5 20
5 2003-02-05 00:00:00.000 20 5 20
5 2003-01-02 00:00:00.000 21 5 20
6 2003-10-14 00:00:00.000 26 6 26
6 2003-09-21 00:00:00.000 26 6 26
6 2002-06-09 00:00:00.000 27 6 26

 

[nigelrivett]

It was a delete - did you want a resultset or to delete records from the table?

For the resultset

select *
from tbl t1
where not exists (select * from tbl t2 where t1.PropertyID = t2.PropertyID and t1.Diary Date > t2.Diary Date and t1.VendorID <> t2.VendorID)

Note that this will not return a record for the same vendor if there is a later one for a different vendor - even if the earlier vendor is the same as the last one.

select *
from tbl t1
where VendorID = (select top 1 VendorID from tbl t2 where t1.PropertyID = t2.PropertyID order by t2.Diary Date desc)




======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Nassy]

Thanks so much everyone for getting back so quickly. I will now take stock and absorb some of these ideas and then get back if I still have any problems

Thanks Again

Nassy

 

[Golom]

One more possibility for you to absorb

Create a query that lists the latest vendor like this

SELECT T1.PropertyID, First(T1.VendorID) AS Vendor, MAX(T1.DairyDate) AS LatestDate
FROM PropVend AS T1
GROUP BY T1.PropertyID
ORDER BY 1, 3

and call it &quot;PVLatest&quot; (for example) and then

SELECT P.PropertyID, P.DairyDate, P.VendorID
FROM PropVend P INNER JOIN PVLatest L 
ON (P.VendorID = L.Vendor) AND (P.PropertyID = L.PropertyID)

I did this in Access and you may be able to combine this into one query in SQL-Server (which I can't test on my current machine.)