#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Location
    Arkansas
    Posts
    9
    Rep Power
    0

    How to find items NOT Sold in year XXXX.


    Hello,
    I need some help with this query since I am no pro at writing queries.
    I have the following query that produces the information I need.

    Here is my query so far.
    Code:
    select IV00101.ITEMNMBR AS "ITEM NUMBER"
    	, IV30102.ITEMNMBR AS "ITEM NUMBER"
    	, IV00101.ITEMDESC AS "ITEM DESCRIPTION"
    	, IV30102.YEAR1 AS "YEAR"
    	, IV30102.SMRYPMTH AS "MONTH"
    	, CAST(IV30102.SMRYQTYS AS INT) AS "QUANTITY SOLD"			
    	, IV00101.ITMGEDSC AS "CUSTOMER"
    --	, IV30102.LOCNCODE
    --	, IV30102.SMRYTYPE
    FROM IV00101, IV30102
    WHERE IV00101.ITEMNMBR = iv30102.ITEMNMBR 
    	AND IV30102.LOCNCODE = 'MAIN'
    	AND IV30102.SMRYTYPE = 1
    	AND IV30102.ITEMNMBR NOT LIKE 'some value'
    	AND IV30102.ITEMNMBR NOT LIKE 'somevalue'
    ORDER BY
    	IV00101.ITEMNMBR ASC
    	, YEAR1 ASC
    	, SMRYPMTH ASC
    BUT, I need to go a step further and only list the items that have NO sales in the last 3 years.

    I am not sure to even how to start that part.

    Thanks for looking.
  2. #2
  3. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,952
    Rep Power
    4033
    I have no idea what your tables are, the names are terrible. As such, I won't even try to write a proper query for them, but rather just an example.

    Basically you'd do a LEFT JOIN against your sales table and then a WHERE conition to find items where the sales id is null.

    Code:
    SELECT 
       products.productId
       , products.productName  
    FROM products
    LEFT JOIN sales ON sales.productId=products.productId AND sales.soldDate >= DATEADD(YEAR, -3, GETDATE())
    WHERE
       sales.saleId IS NULL
    The LEFT JOIN will match rows based on the product ID and if the sales date is greater than three years ago. If no rows match that condition it will fill in a row will NULL's and use that.

    The WHERE condition then limits the result set to only the NULL field rows, ie those that had no sales matches.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Location
    Arkansas
    Posts
    9
    Rep Power
    0
    Thank you for your response.

    I didn't set these tables up. I'm using an accounting system that's been around since before 2000, and was DOS based before that.

    The first table gives me the Item number and Description the next one gives me the Item Number and the PERIOD(Monthly), YEAR Sales.
    The thing about your answer that I don't know is this: sales.saleId

    There is no salesID field or anything like that in the two tables I have. The two tables I'm using are Inventory tables. They had the information I needed so that's why I choose them.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by igloo iguana
    The thing about your answer that I don't know is this: sales.saleId
    he was simply showing you a sample query which uses the IS NULL test on a LEFT OUTER JOIN to find unmatched rows

    you're still going to need a LEFT OUTER JOIN, so here's my attempt at reworking your query --
    Code:
    SELECT iv00101.itemnmbr AS "item number"
         , iv30102.itemnmbr AS "item number"
         , iv00101.itemdesc AS "item description"
         , iv30102.year1 AS "year"
         , iv30102.smrypmth AS "month"
         , CAST(iv30102.smryqtys AS int) AS "quantity sold"   
         , iv00101.itmgedsc AS "customer"
      FROM iv00101
    LEFT OUTER
      JOIN iv30102
        ON iv30102.itemnmbr = iv00101.itemnmbr
       AND iv30102.locncode = 'main'
       AND iv30102.smrytype = 1
     WHERE iv00101.itemnmbr not IN ('some value','somevalue')
       AND iv30102.itemnmbr IS NULL
    ORDER 
        BY iv00101.itemnmbr ASC
         , iv30102.year1 ASC
         , iv30102.smrypmth ASC
    this should return all items with no sales whatsoever

    to limit it to no sales in the last three years, and some more conditions to the ON clause
    Last edited by r937; January 28th, 2014 at 07:54 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Location
    Arkansas
    Posts
    9
    Rep Power
    0
    Let me start over so we all understand what I am asking and why.
    I have this query and it produces the following data (image 1)

    Code:
    select IV00101.ITEMNMBR AS "ITEM NUMBER"
    	, IV00101.ITEMDESC AS "ITEM DESCRIPTION"
    	, IV30102.YEAR1 AS "YEAR"
    	, IV30102.SMRYPMTH AS "MONTH"
    	, CAST(IV30102.SMRYQTYS AS INT) AS "QUANTITY SOLD"			
    FROM IV00101, IV30102
    WHERE IV00101.ITEMNMBR = iv30102.ITEMNMBR 
    	AND IV30102.LOCNCODE = 'MAIN'
    	AND IV30102.SMRYTYPE = 1
    	AND IV30102.ITEMNMBR NOT LIKE '%-WK%'
    	AND IV30102.ITEMNMBR NOT LIKE '%-SP%'
    ORDER BY
    	IV00101.ITEMNMBR ASC
    	, YEAR1 ASC
    	, SMRYPMTH ASC
    This query gives me ALL SALES for each item in its respective YEAR/MONTH.


    I also have this query.
    And the results are in image number 2.

    Code:
    SELECT IV00101.ITEMNMBR AS "ITEM NUMBER" 
    , COUNT(iv30102.ITEMNMBR) AS "Total sales IN"
    , IV30102.YEAR1 AS "YEAR"
    FROM  iv30102 
    left join IV00101
    ON IV30102.ITEMNMBR=IV00101.ITEMNMBR
    	AND IV30102.LOCNCODE = 'MAIN'
    	AND IV30102.SMRYTYPE = 1
    	AND IV30102.ITEMNMBR NOT LIKE '%-WK%'
    	AND IV30102.ITEMNMBR NOT LIKE '%-SP%'
    group by IV00101.ITEMNMBR, IV30102.YEAR1
    order by iv00101.ITEMNMBR asc, YEAR1 ASC
    This counts the SALES in each YEAR and lists them.


    Here is another query I tried with some of your suggestions.
    Code:
    SELECT IV00101.ITEMNMBR AS "ITEM NUMBER"
    	, IV00101.ITEMDESC AS "ITEM DESCRIPTION"
    	, IV30102.YEAR1 AS "YEAR"
    	, IV30102.SMRYPMTH AS "MONTH"
    	, CAST(IV30102.SMRYQTYS AS INT) AS "QUANTITY SOLD"			
    FROM IV00101
    	LEFT OUTER
    		JOIN IV30102 
    			ON IV30102.ITEMNMBR=IV00101.ITEMNMBR	
            AND IV30102.YEAR1 >= DATEADD(YEAR, -3, GETDATE())
    	AND IV30102.LOCNCODE = 'MAIN'
    	AND IV30102.SMRYTYPE = 1
    	AND IV30102.ITEMNMBR IS NULL
    	AND IV30102.ITEMNMBR NOT LIKE '%-WK%'
    	AND IV30102.ITEMNMBR NOT LIKE '%-SP%'
    ORDER BY
    	IV00101.ITEMNMBR ASC
    	, YEAR1 ASC
    	, SMRYPMTH ASC
    I have no idea what this means. I see Items on there that have sales in YEAR 2011, 2012 and 2013.


    Query 1 and 2 are queries I came up with in my search for the information I needed. They donít rely on each other. Just wanted to show what I have done so far.

    As you can see by query number 2 I have a history of items sold by year.
    What I want is a list of items that have NO History of sales in the last 3 years.

    Ex. Item numbers 00001-0001 and 00001-0002 have sales in year 2011, 2012 and 2013 so they would not be in my list, but item numbers 0000-0005, 000-0011, 000-0016 and 0000-0017 donít have sales in the last 3 years, so they would be on my list.

    How can I find all items that fall under the NO sales if there is NO HISTORY of it sold?

    My thinking was something along this line of thought.
    I should be able to only list the last year an item was sold and then do an IF statement.
    Which I have no idea how to implement into the query I have.
    Code:
    IF YEAR < 2011 THEN
    That was my line of thought when I came here and I apologize if I misled what I was asking.

    So with all this new information do the suggestions you two made work and I've just not haven't figured it out yet? Or is there some other easier way to accomplish this?

    Thanks for your time.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    i think you completely missed the part about the IS NULL test

    both of the people who tried to help you mentioned it

    please do some more testing, and research if necessary

    the IS NULL test is what gives you the NOT found concept

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo