Item as per Latest Date + Summed quantities of distinct items

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear All,

I hope you can help.

I have a table of historic order data, with the fields "ProductId",
"OrderQty", "OrderDate" and "Price".

I would like to generate a query that produces a list of distinct products
(this I have managed to do independently) which also shows the latest price
paid for each, and a total of parts ordered per product (possibly during a
specified date range).

My SQL knowledge is fairly rudmentary and although I can build simple
queries, more involved ones are, for the moment, beyond my grasp!

Thanks in advance,

Matt
 
As a follow up, I have used the GROUP BY function as follows:

SELECT productid, SUM(orderqty) AS "Total Quanity Ordered"
FROM table
WHERE productid like "LH*"
GROUP BY productid;

to give me total order quanities for a specified type of product. I'm
confused, however, as the GROUP BY function seems to return more rows than
running the DISTINCT on the same set of data - surely they sould return the
same number of rows?

Yours confused,

Matt
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to create 3 queries (it could be done in one, but that would
be overly complex for a query novice). One query to get the latest
price, another to get the parts count, and the final one to combine the
2 queries for the final results.

Query 1 - get the latest price paid per product:

PARAMETERS StartDate Date, EndDate Date;
SELECT ProductID, Price
FROM ProductsHistory As PH
WHERE OrderDate = (SELECT Max(OrderDate) FROM ProductsHistory
WHERE ProductID = PH.ProductID)
AND OrderDate BETWEEN StartDate And EndDate

The subquery gets the record w/ the latest date for the product.

The PARAMETERS are pop-up prompts that ask for the date range the query
should consider.

Query 2 - total parts ordered per product

PARAMETERS StartDate Date, EndDate Date;
SELECT ProductID, SUM(OrderQty) As PartCount
FROM ProductsHistory
WHERE OrderDate BETWEEN StartDate And EndDate
GROUP BY ProductID

Query 3 - combined queries

SELECT Q2.ProductID, Q2.PartCount As PartsSole, Q1.Price As LatestPrice
FROM Query1 As Q1 INNER JOIN Query2 As Q2
ON Q1.ProductID = Q2.ProductID

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX4VUIechKqOuFEgEQJ/vQCffW5pYKoc3CbdbqhW2XUN2KnB6PcAoLdb
VABvLQu1vnPku6IzO0bPWtrz
=VtKY
-----END PGP SIGNATURE-----
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I just realized that in Query 1 this:

WHERE OrderDate = (SELECT Max(OrderDate) FROM ProductsHistory
WHERE ProductID = PH.ProductID)
AND OrderDate BETWEEN StartDate And EndDate

should be this:

WHERE OrderDate = (SELECT Max(OrderDate) FROM ProductsHistory
WHERE ProductID = PH.ProductID
AND OrderDate BETWEEN StartDate And EndDate)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX4Z7oechKqOuFEgEQLiJwCeKHzhZKxpXDr7ij/iLL7TE4UwbnMAoOsG
A7v/Qo36TtN6AQwzeeGI2Rhl
=ZEwM
-----END PGP SIGNATURE-----
 
Thanks! I seem to be getting somewhere.

I now need to run a similar query, but this time Date and Part No. fields
are in different tables (which have one matching field between them).

Displaying all the data is as simple as an Inner Join on the equivalent
field, but how would I insert this join into the query as designed below? I
understand the reason for creating the alias "PH", is it possible to give a
joined set of data a single alias or is there another solution?

I have been trying solutions along the lines of

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT ProductsHistory.ProductID, ProductsHistory.Price
FROM ProductsHistory as PH INNER JOIN InvDates ON
ProductsHistory.Invno=InvDates.Invno
WHERE OrderDate = (SELECT Max(OrderDate) FROM ProductsHistory
WHERE ProductID = PH.ProductID
AND OrderDate BETWEEN StartDate And EndDate)

but this has been futile as "OrderDate" appears only in the "InvDates"
table, and ProductID appears only in the "ProductsHistory" table! Is there a
way to alias the whole joined table set as one?

Thanks,

matt
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm not sure I understand what you're describing. What's the criteria
for the new query? IOW, what do the clients want as the results? What
is the design of the new table (columns, PK, FKs)?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX6QDYechKqOuFEgEQKK3gCfXRiBqXBxRqbPXB9lCBk9B9qFSc8AoIoj
UrYZGM7cLojD2UR+xcrU6aQF
=SZVb
-----END PGP SIGNATURE-----
 
Back
Top