Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Almost matching data (2000)

    Edited by HansV to present data in tables - see <!help=19>Help 19<!/help>

    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:

    <table border=1><td>ProdID</td><td>yearno</td><td>weekno</td><td>itemsperbox</td><td>a</td><td align=right>2003</td><td align=right>25</td><td align=right>100</td><td>a</td><td align=right>2003</td><td align=right>28</td><td align=right>150</td></table>
    and sold contains the following:

    <table border=1><td>ProdID</td><td>yearno</td><td>weekno</td><td>sold</td><td>a</td><td align=right>2003</td><td align=right>24</td><td align=right>50</td><td>a</td><td align=right>2003</td><td align=right>25</td><td align=right>20</td><td>a</td><td align=right>2003</td><td align=right>26</td><td align=right>30</td><td>a</td><td align=right>2003</td><td align=right>27</td><td align=right>15</td><td>a</td><td align=right>2003</td><td align=right>28</td><td align=right>20</td><td>a</td><td align=right>2003</td><td align=right>29</td><td align=right>25</td></table>
    then the query should give the results:

    <table border=1><td>ProdID</td><td>yearno</td><td>weekno</td><td>sold</td><td>itemsperbox</td><td>total</td><td>a</td><td align=right>2003</td><td align=right>24</td><td align=right>50</td><td align=right>0</td><td align=right>0</td><td>a</td><td align=right>2003</td><td align=right>25</td><td align=right>20</td><td align=right>100</td><td align=right>2000</td><td>a</td><td align=right>2003</td><td align=right>26</td><td align=right>30</td><td align=right>100</td><td align=right>3000</td><td>a</td><td align=right>2003</td><td align=right>27</td><td align=right>15</td><td align=right>100</td><td align=right>1500</td><td>a</td><td align=right>2003</td><td align=right>28</td><td align=right>20</td><td align=right>150</td><td align=right>3000</td><td>a</td><td align=right>2003</td><td align=right>29</td><td align=right>25</td><td align=right>150</td><td align=right>3750</td></table>
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Almost matching data (2000)

    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.)

  3. #3
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Almost matching data (2000)

    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.... <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25>

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Almost matching data (2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •