Comparing duplicates

SSAS and SSRS
1

I'm trying to find a way, either in SQL or SSRS, to compare rows that have similarities but have one field that is different.

For example.

I have a report that is showing late shipments. If the order had 2 items, and both shipped late, but on different days, I don't want both items to show up, I only want the last one to ship to show up.

Unfortunately there isn't a trigger or field that lets me know which was the last one shipped, but I found the shipping table master that has all the shipments and dates.

So the query I think I can use would be something like

WHERE
TABLE.jobNumber + TABLE.shipDate > TABLE.jobNumber + TABLE.shipDate

so if the job number is the same, then only return the row that has the higher shipDate

Hopefully that makes sense.

Thanks!

2

You could use the row_number function to assign sequencenumber (in certain order) and then show only the first one.
Something like:

select ...
  from (select ...
              ,row_number()
                   over(partition by jobnumber
                        order by shipdate desc
                       )
               as rn
         from table
        where ...
       ) as a
 where a.rn=1
3

Try this sample query to compare and list duplicate entries in a table, hopefully it may be helpful to you.

select t1.* from Table t1
join(
select username from Table
group by username
having count(username) >1) t2 on t1.username = t2.username
4

This looks close, but I think this is returning anything where there's more than 1 username, correct?

That's not really what will work. I need it to return the 1 row with the latest date, there could very well be 4-5 results, but I just want to bring in the one that shipped last.

Thank for your help!