Almost matching data (2000)

Topic

New Reply

Edited by HansV to present data in tables – see Help 19

I have a table (itemsize) which contains fields productid, yearno, weekno, itemsperbox.
Entries will be made in this table giving the year (eg.2004) and week number (eg. 27). The itemsperbox is an integer.

I also have a table (sold) which contains productid, yearno, weekno, quantitysold. There is one entry for each yearno/weekno for a product storing the quantity sold this week and as integer.

I need to product a query which will look in the sold table and multiply the quantity by the appropriate itemsperbox from the itemsize table.

eg. if itemsize contains the following:

ProdID	yearno	weekno	itemsperbox
a	2003	25	100
a	2003	28	150

and sold contains the following:

ProdID	yearno	weekno	sold
a	2003	24	50
a	2003	25	20
a	2003	26	30
a	2003	27	15
a	2003	28	20
a	2003	29	25

then the query should give the results:

ProdID	yearno	weekno	sold	itemsperbox	total
a	2003	24	50	0	0
a	2003	25	20	100	2000
a	2003	26	30	100	3000
a	2003	27	15	100	1500
a	2003	28	20	150	3000
a	2003	29	25	150	3750

I’m struggling to find a way of doing this without introducing an ending yearno/weekno in the itemsize table

If anyone can understand what I’m trying to do…….any suggestions?

John

Reply | Quote

Replies

I think you need two queries for this:

1) A query to retrieve the week from ItemSize that belongs to a week from Sold for a given product and year:

SELECT Sold.ProdID, Sold.yearno, Sold.weekno AS weekSold, Max(ItemSize.weekno) AS weekSize
FROM ItemSize RIGHT JOIN Sold ON (ItemSize.yearno = Sold.yearno) AND (ItemSize.ProdID = Sold.ProdID)
WHERE (((ItemSize.weekno)<=[Sold].[weekno]))
GROUP BY Sold.ProdID, Sold.yearno, Sold.weekno;

Save this query as – say – qryWeeks.

2) A query that uses qryWeeks to join Sold and ItemSize:

SELECT Sold.ProdID, Sold.yearno, Sold.weekno, Sold.sold, ItemSize.itemsperbox, [sold]*[itemsperbox] AS Total
FROM (Sold INNER JOIN qryWeeks ON (Sold.ProdID = qryWeeks.ProdID) AND (Sold.yearno = qryWeeks.yearno) AND (Sold.weekno = qryWeeks.weekSold)) INNER JOIN ItemSize ON (qryWeeks.weekSize = ItemSize.weekno) AND (qryWeeks.ProdID = ItemSize.ProdID) AND (qryWeeks.yearno = ItemSize.yearno)
ORDER BY Sold.ProdID, Sold.yearno, Sold.weekno;

This query will not return anything for week 24, however, since there are no data available. (Using an outer join does not work.)

Reply | Quote

Many thanks
Combined with this and the subsequent post – and a few other bits I had to add for other tables – it is working well

Reply | Quote

Many thanks
Combined with this and the subsequent post – and a few other bits I had to add for other tables – it is working well

Reply | Quote

I think you need two queries for this:

1) A query to retrieve the week from ItemSize that belongs to a week from Sold for a given product and year:

SELECT Sold.ProdID, Sold.yearno, Sold.weekno AS weekSold, Max(ItemSize.weekno) AS weekSize
FROM ItemSize RIGHT JOIN Sold ON (ItemSize.yearno = Sold.yearno) AND (ItemSize.ProdID = Sold.ProdID)
WHERE (((ItemSize.weekno)<=[Sold].[weekno]))
GROUP BY Sold.ProdID, Sold.yearno, Sold.weekno;

Save this query as – say – qryWeeks.

2) A query that uses qryWeeks to join Sold and ItemSize:

SELECT Sold.ProdID, Sold.yearno, Sold.weekno, Sold.sold, ItemSize.itemsperbox, [sold]*[itemsperbox] AS Total
FROM (Sold INNER JOIN qryWeeks ON (Sold.ProdID = qryWeeks.ProdID) AND (Sold.yearno = qryWeeks.yearno) AND (Sold.weekno = qryWeeks.weekSold)) INNER JOIN ItemSize ON (qryWeeks.weekSize = ItemSize.weekno) AND (qryWeeks.ProdID = ItemSize.ProdID) AND (qryWeeks.yearno = ItemSize.yearno)
ORDER BY Sold.ProdID, Sold.yearno, Sold.weekno;

This query will not return anything for week 24, however, since there are no data available. (Using an outer join does not work.)

Reply | Quote

Here’s the SQL for a single query solution:

SELECT ProdID, yearno, weekno, T1.sold, (SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC) AS ItemsPerBox, (SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC) AS total
FROM sold AS T1
ORDER BY ProdID, yearno, weekno;

I used itemsize and sold as the table names, per your post, but I would recommend changing them to tblItemSize and tblSold. (the sold table has a sold field…which can lead to some confusion.

Note, the query results will have 'nulls' instead of zeros, if there is no itemsperbox value 'yet'. If you must have zeros, then use this (it will just take longer…)

SELECT ProdID, yearno, weekno, T1.sold, IIF(IsNull((SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)),0,(SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)) AS ItemsPerBox, IIF(IsNull((SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)),0,(SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)) AS total
FROM sold AS T1
ORDER BY ProdID, yearno, weekno;

You can get zero's with the first query (which would be faster, by putting records in the itemsize table like a,1,1,0, but that may disrupt other systems in your database)

Have fun….

Reply | Quote

Here’s the SQL for a single query solution:

SELECT ProdID, yearno, weekno, T1.sold, (SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC) AS ItemsPerBox, (SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC) AS total
FROM sold AS T1
ORDER BY ProdID, yearno, weekno;

I used itemsize and sold as the table names, per your post, but I would recommend changing them to tblItemSize and tblSold. (the sold table has a sold field…which can lead to some confusion.

Note, the query results will have 'nulls' instead of zeros, if there is no itemsperbox value 'yet'. If you must have zeros, then use this (it will just take longer…)

SELECT ProdID, yearno, weekno, T1.sold, IIF(IsNull((SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)),0,(SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)) AS ItemsPerBox, IIF(IsNull((SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)),0,(SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)) AS total
FROM sold AS T1
ORDER BY ProdID, yearno, weekno;

You can get zero's with the first query (which would be faster, by putting records in the itemsize table like a,1,1,0, but that may disrupt other systems in your database)

Have fun….

Reply | Quote