SELECT     TOP (100) PERCENT ItemCode, Description, LotNumber, ExpirationDate, PurchasePrice, PCur, XRate, QtyIn, QtyOut, Reserved, Available, MIN(DateStart) 
                      AS LotDate
FROM         dbo.VK_PCOMPRA_2
GROUP BY LotNumber, ItemCode, Description, ExpirationDate, PurchasePrice, PCur, XRate, QtyIn, QtyOut, Reserved, Available
ORDER BY ItemCode

quote:

---

Originally posted by sz1

Post some sample data.

We are the creators of our own reality!

---

with getmin as
(
select  item1, description, expdate, p_price, xrate, startdate Lotdate,
row_number() over(partition by item1 order by startdate asc) rn
from #mintest
)
select item1, description, expdate, p_price, xrate, Lotdate from getmin
where rn = 1

quote:

---

Originally posted by sz1

Its because of your grouping, if you use the MIN on its own it will be fine but you are adding groups from other fields, use row_number to get around this.

This logic will work for you:

with getmin as
(
select  item1, description, expdate, p_price, xrate, startdate Lotdate,
row_number() over(partition by item1 order by startdate asc) rn
from #mintest
)
select item1, description, expdate, p_price, xrate, Lotdate from getmin
where rn = 1

We are the creators of our own reality!

---