1

Here is my SQL, I am trying to get the DISTINCT item ids that have not been invoiced in the past two years that also have a qty on hand greater than 0. I am having an issue getting the last customer id that was invoiced for each distinct item id.

SELECT DISTINCT 
    im.item_id,
    MAX(il.date_created),
    (SELECT TOP 1 customer_id 
     FROM invoice_hdr ih2 
     WHERE ih.invoice_no = ih2.invoice_no) AS customer_id,
    inv_loc.qty_on_hand,
    ih.sales_location_id,
    MAX(il.commission_cost)
FROM 
    invoice_hdr ih
INNER JOIN 
    invoice_line il ON ih.invoice_no = il.invoice_no
INNER JOIN 
    inv_mast im ON il.inv_mast_uid = im.inv_mast_uid
INNER JOIN 
    inv_loc ON im.inv_mast_uid = inv_loc.inv_mast_uid
WHERE 
    inv_loc.qty_on_hand > '0'
GROUP BY 
    im.item_id, inv_loc.qty_on_hand, ih.sales_location_id
HAVING 
    (MAX(il.date_created) < DATEADD(year, -2, GETDATE()))

I get this error:

Column 'invoice_hdr.invoice_no' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I understand the error, but no matter what I try I cannot get it to work.

Sean
  • 1,368
  • 2
  • 9
  • 21
  • 1
    A `DISTINCT` with a `GROUP BY` is always a sign of a flaw in your query. A `GROUP BY` already causes your data to be returned in *distinct* sets, so if you are getting duplicates, it likely means your `GROUP BY` is wrong. Otherwise the `DISTINCT` is redundant and unneeded overhead. – Thom A Feb 28 '22 at 15:51
  • 1
    Also note that a `TOP` without an `ORDER BY` is another sure sign of a flaw. This means that the data engine is free to return what ever arbitrary value it wants, and that value could be different every time you run said query. If you are using `TOP` you need to ensure the query has an `ORDER BY` so that you get consistent and reliable results. – Thom A Feb 28 '22 at 15:52
  • Why are you `JOIN`ing back to `invoice_hdr` in a subquery here anyway? *Presumably* the `invoice_no` is going to be unique in a table called `invoice_hdr` so why use a subquery and not just have `customer_id`? – Thom A Feb 28 '22 at 16:12
  • I am trying to get the last customer id that was invoiced for a given item id that satisfies the conditions. If I just use customer id, it is returning duplicate item ids. – Sean Feb 28 '22 at 16:15
  • Then use `MAX(customer_id)`? That'll at least give you a consistent value, instead of your "give me an arbitrary customer id". – Thom A Feb 28 '22 at 16:16
  • @Larnu, bad choice of MAX( customer_id ). What if customer #10 was the last to order and there are 5,000 customer IDs. – DRapp Feb 28 '22 at 16:17
  • This is why there was a "?" @DRapp , and again, it would still be better than "give me an arbitrary customer id (which could be different every time I run the query, and even different within the same one as the subquery will be executed multiple times)". Consistently wrong results and far better, in my opinion, than inconsistent results that are still all wrong. – Thom A Feb 28 '22 at 16:18
  • Seems what the OP really wants is a [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group). – Thom A Feb 28 '22 at 16:19
  • Please EDIT your post, and put in SIMPLE ENGLISH, what you are trying to do. For example: I want to know all items that were purchased OVER 2 years go (via your date -2yrs GREATER than date_created) the last person (customer id) who purchased it and when that purchase was, AND the QTY on hand > 0. From that purchase, what was ITs commission cost. Now, saying that, many things dont fair well in this query. You should probably do a basic list of the table structures. Again, EDIT your post and update there, not in a comment. – DRapp Feb 28 '22 at 16:31
  • I edited the post with my desired results. – Sean Feb 28 '22 at 16:37
  • Given "item ids that have not been invoiced", is your goal to find items your company produces (or resells) that have not been sold "recently"? It seems your starting point should be the inv_master table, not the invoice information. Get the total on hand and then correlate by filtering out any items that HAVE been invoiced since your "recently" boundary. Beware a reliance on "created date" - sometimes we have to "fix" something after the fact. – SMor Feb 28 '22 at 17:09
  • `GROUP BY im.item_id, inv_loc.qty_on_hand, ih.sales_location_id` Think about what "things" your resultset represents. You want multiple rows - one per location - for these items? Or you want one row per item? – SMor Feb 28 '22 at 17:12
  • add (SELECT TOP 1 customer_id FROM invoice_hdr ih2 WHERE ih.invoice_no = ih2.invoice_no) to your group by or put a max around the subquery. – Golden Lion Feb 28 '22 at 17:28

1 Answers1

0

I was able to get the correct data with the following query:

SELECT DISTINCT il.item_id,
    ih.customer_id,
    il.customer_part_number,
    il.date_created,
    SUM(inv_loc.qty_on_hand) AS [Qty On Hand],
    ih.sales_location_id,
    MAX(il.commission_cost) AS [Max Commission Cost]
FROM invoice_hdr ih
    JOIN invoice_line il ON ih.invoice_no = il.invoice_no
    JOIN inv_loc ON il.inv_mast_uid = inv_loc.inv_mast_uid
WHERE (SELECT TOP 1 il2.date_created FROM invoice_line il2 WHERE il2.item_id = il.item_id ORDER BY il2.date_created DESC) < DATEADD(YEAR, -2, GETDATE())
    AND (SELECT TOP 1 il2.invoice_no FROM invoice_line il2 WHERE il2.item_id = il.item_id ORDER BY il2.date_created DESC) = ih.invoice_no
GROUP BY il.item_id,
    ih.customer_id,
    il.date_created,
    ih.sales_location_id,
    customer_part_number
HAVING SUM(inv_loc.qty_on_hand) > 0
ORDER BY il.item_id
Sean
  • 1,368
  • 2
  • 9
  • 21