Dedupe parameter list 1

[dianemarie]

Hello, in my report I have a parameter which pulls by client ID and displays both client ID and name. A second parameter brings up a list of products associated with that client, by product ID, and displays product name.

I'm using one table with a mixed dataset of old and new product names, but the same product ID number. In the parameter drop-down, I don't care which product name is displayed with the product ID.

The problem is I'm getting the same ID number twice, with the new name and the old name. I've played with distinct and group by, but can't get it to display just one number with one product name, I don't care which product name gets pulled - new or old.

Is there a way to do this using the existing table of detail records? We were initially using views, but there were issues with ID conflicts. This is the current query I am using. Any help is appreciated. Thank you.

select distinct(lo.prodid),lo.prodname
from nm_ordhistall LO
where lo.prodid in (@Owner)
order by 2,1

 

[RiverGuy]

In the parameter drop-down, I don't care which product name is displayed with the product ID.

In that case, your solution is simple. Try the following query:

select lo.prodid, MIN(lo.prodname) AS prodname
from nm_ordhistall LO
where lo.prodid in (@Owner)
GROUP BY lo.prodid
order by 2,1

Now, if you have dupes where the product name is the same, you might still get some dupes here. To fix that, you're going to need to find another column in your table to designate the current or desired product name. This column could be something like a LastUpdated column. You'd construct a query like the following if needed:

SELECT a.prodid, a.prodname
FROM nm_ordhistall a
INNER JOIN 
	(select prodid, MAX(LastUpdated) AS MaxTime
	FROM nm_ordhistall
	GROUP BY prodid) b
ON a.prodid = b.prodid
AND a.LastUpdated = b.MaxTime 
WHERE a.prodid IN (@Owner)
ORDER BY 2, 1

 

[dianemarie]

Hi RG, meant to get back and say many thanks. The MIN did the trick for me.