December 21, 2008 at 8:58 am
I have a table 'PurchaseHistory'. Products are listed numerous times.
I would like to return the ProductId once + the PurchasePrice for the LastOrderedDate (i.e. if the product was ordered 10 times jan 08..........oct o8 I want to return the Cost Price for the Product from October).
My Query:
SELECT DISTINCT COUNT(ProductId) AS PartCount,PurchasePrice, MIN(PurchaseOrderDate) AS LastPurchaseDate
FROM dbo.PurchaseHistory
GROUP BY ProductId,PurchasePrice
The problem is some ProductId's are returned more than once with a count of 1.
Does my query look correct?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 21, 2008 at 9:19 am
Use an ORDER BY and a TOP 1, then add a HAVING clause to get only those ordered 1 time (if that's what you need.
December 21, 2008 at 9:20 am
You can't group by productPrice, as a single product could have more than 1 price and you're going to get a count of how many products were sold at each price, which is not what you want. You're also using MIN() when you should be using MAX() for date.
This is off the top of my head. It is untested and may not work due to no sample data being provided.
SELECT
PH.ProductID,
COUNT(*) AS PartCount,
MostRecentPrice,
MAX(PurchaseOrderDate) MaxDate
FROM dbo.PurchaseHistory PH
LEFT JOIN (SELECT ProductID, MAX(PurchasePrice) MostRecentPrice -- Max Accounts for 2 at exactly the same time.
FROM PurchaseHistory
GROUP BY ProductID
HAVING PurchaseOrderDate = MAX(PurchaseOrderDate)) POD ON PH.ProductID = POD.ProductID
GROUP BY ProductID, MostRecentPrice
December 21, 2008 at 11:25 am
Thanks for replying guys.
Seth I tried modifying your code, just working through the errors! 🙂
Steve I have not had much exposure to using HAVING + TOP so will have read up to see if I can put it all into the correct syntax.
Merry Xmas to those who celebrate.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 22, 2008 at 12:56 am
Hello Phil,
it would help us a lot in identifying the problems and arriving at a solution if you post a table definition + sample data together with your question.
Also, I'm not sure what the "PartCount" should mean - is that how many times the product was ordered? Or is it just something you tried to use to find your solution and you don't need that column in the result?
I skipped PartCount in the following code, since I'm not sure what it it should be. If you explain it, I'll modify the code.
/*create table for testing*/
CREATE TABLE PurchaseHistory(ProductID INT, PurchasePrice MONEY, PurchaseOrderDate DATETIME)
/*enter some sample data*/
INSERT INTO PurchaseHistory(ProductID, PurchasePrice, PurchaseOrderDate)
SELECT 1, 10.00, '20080101'
UNION SELECT 1, 12.00, '20080415'
UNION SELECT 1, 12.00, '20080420'
UNION SELECT 1, 12.00, '20080510'
UNION SELECT 1, 12.00, '20080622'
UNION SELECT 1, 13.00, '20080622'
/*this is the required query*/
SELECT PH.ProductID, MAX(PurchasePrice), MIN(PurchaseOrderDate) AS LastPurchaseDate
FROM dbo.PurchaseHistory PH
JOIN (SELECT ProductID, MAX(PurchaseOrderDate) AS LastPurchaseDate
FROM dbo.PurchaseHistory
GROUP BY ProductID) AS Q ON Q.ProductID = PH.ProductID AND Q.LastPurchaseDate = PH.PurchaseOrderDate
GROUP BY PH.ProductID
/*cleanup*/
DROP TABLE PurchaseHistory
P.S.: There is even better solution if your PurchaseHistory table contains identity column, and if it can be used for finding the last purchase (which is not necessarily true, you might for example enter the data about purchases retrospectively, which can result in older transactions being entered later than some more recent).
And one more question - can there be several purchases with exactly the same date and different price? If yes, which one of them should be returned?
December 22, 2008 at 2:06 am
Hi and thanks for posting. Yes Part Count is to capture how many times the Part has been ordered. I will implement your code and post back (table definition, sample data as requested if required).
The table was created from an excel import. I need to identify the last cost price of a Part as this is the cost price I will be migrating.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 22, 2008 at 2:21 am
OK, that means you need to add the COUNT inside the derived table Q:
SELECT PH.ProductID, MAX(PH.PurchasePrice), MIN(PH.PurchaseOrderDate) AS LastPurchaseDate, Q.PartCount
FROM dbo.PurchaseHistory PH
JOIN ( SELECT ProductID, COUNT(*) AS PartCount, MAX(PurchaseOrderDate) AS LastPurchaseDate
FROM dbo.PurchaseHistory
GROUP BY ProductID) AS Q ON Q.ProductID = PH.ProductID AND Q.LastPurchaseDate = PH.PurchaseOrderDate
GROUP BY PH.ProductID, Q.PartCount
Mark that the MIN in MIN(PH.PurchaseOrderDate) AS LastPurchaseDate is there just to filter out duplicity in case there are 2 or more purchases with precisely the same date. It can be MIN or MAX, no difference, JOIN makes sure that only rows with the same value in PurchaseOrderDate are returned.
December 22, 2008 at 7:54 am
1. If you'd just wanted the amount without the accompanying date you could have had a simple select:
select ProductID,
count(*) partcount,
convert(money,substring(max(convert(char(9), PurchaseOrderDate,112) +
convert(char(8),PurchaseOrderDate,8) +
convert(char(20), PurchasePrice))
,18,20)) LatestPrice
from PurchaseHistory group by productid
2. Because you're interested in two elements of the aggregate it's easier to do that once and select the elements separately:
select ProductID,
partcount,
convert(money,substring(maxstring,18,20)) LatestPrice,
convert(char(11), convert(datetime, substring(maxstring,1,8)), 106) LastPurchaseDate
from (select ProductID,
count(*) partcount,
maxstring=max(
convert(char(9), PurchaseOrderDate,112) +
convert(char(8),PurchaseOrderDate,8) +
convert(char(20), PurchasePrice)
)
from PurchaseHistory
group by productid) staged
December 22, 2008 at 11:10 am
Great work and many thanks. I rolled with Vladan example code (last post), worked a treat.
Once again many thanks to all those who posted, I really appreciate your time and efforts.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply