asked
least rate of item that bought in 6 months as on date ..
i have table called po it holds columns pono,supplierid,itemname,docdate,itemdisc,rate,discount,totalamount on different docdate with different supplierid and different rate same item is there in po table when i give itemname in run time it has to give least rate of that item with docid and supplierid in last 6 months from as on date.
I think this will work - but haven't had a chance to test it. The second SELECT will return more than one record if an item has been supplied more than once in the last 6 months at that minimum rate. You could add a TOP 1 if you just wanted to get one record (and ORDER BY something like docDate DESC if you want the most recent order at that rate). DECLARE @minRate MONEY DECLARE @itemName NVARCHAR(50) --get min rate for item in last 6 months SELECT @MinRate = MIN(Rate) FROM dbo.PO b WHERE DATEDIFF(MONTH, docData, GETDATE()) < 6 --limit to POs in last 6 month AND itemName = @itemName --get the records that match that minrate (could return more than one record) SELECT Rate, DocId, SupplierId FROM dbo.PO a WHERE DATEDIFF(MONTH, docData, GETDATE()) < 6 --limit to POs in last 6 month AND itemName = @itemName AND rate = @minRate
14 People are following this question.
