Duplicates and how to get rid of them

Question

I am getting duplicate rows of data.

The wh.whsheaderID is different and it corresponds to a particular unit and to a particular reference (hence my "duplicate values"). Then I have a datetime for NOT departure and then a different datetime for THU departure.

However, because there are 2 different wh.whsheaderIDs corresponding to the same unit and the same reference I get 2 rows of data where the NOT departure date & scan date will be populated and on the next row, the THU departure date & scan time will be populated. How do I collate this information into one row?

SELECT wh.WhsHeaderID,
        mu.Number AS Unit ,
        m.MovementRef AS Reference ,
        (SELECT CASE WHEN COUNT(*) >=2 THEN 'Yes' ELSE 'No' END FROM dbo.whsHeader WHERE RunMovID = m.movementId) AS [Co-Load],
        (Select wh.ArrDepDate where wh.localdepotcode = 'NOT')  AS [NOT Departure Date], 
        (Select wh.LastScannedTime where wh.LocalDepotCode = 'NOT') AS [NOT Last Scan] ,
        (Select wh.ArrDepDate where wh.LocalDepotCode = 'THU') as [THU Departure Date],
        (Select wh.LastScannedTime where wh.LocalDepotCode = 'THU') as [THU Last Scan],

Did you search for a [solution](https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows?rq=1)? Similar questions have been asked many, many times before. — HoneyBadger, Jun 12 '17 at 12:40
If you have 2 `wh.whsheaderID`, which one do you want to keep? For example the larger one? (hint: use `MAX`). The SQL you've posted isn't valid. Please post it all — Nick.Mc, Jun 12 '17 at 12:42
@Nick.McDermaid that is the issue, I technically want to keep both but I am not sure it is possible. The whole sql is very big. Do you still advise to post the whole query? — Jo Jo, Jun 12 '17 at 12:43
@Diana, the best thing to do is create a short example query that demonstrates the problem but *actually works* (is valid SQL). If you want to keep both headers then you need to decide what to do with them. What is the output you actually want as a result? From that it is possible to work back to what your query should be. — , Jun 12 '17 at 12:49
Could you show us the whole sql-statement? Right now it is not complete, and thus not valid, and very difficult to help with... — Sourcery, Jun 12 '17 at 12:54

score 1 · Accepted Answer · answered Jun 12 '17 at 12:55

Using conditional aggregation:

select 
    WhsHeaderId = min(wh.WhsHeaderID)
  , Unit = mu.Number
  , Reference = m.MovementRef
  , [Co-Load] = case when count(*) >=2 then 'Yes' else 'No' end 
  , [NOT Departure Date] = min(case when wh.localdepotcode = 'NOT' then wh.ArrDepDate end)
  , [NOT Last Scan]      = max(case when wh.localdepotcode = 'NOT' then wh.LastScannedTime end)
  , [THU Departure Date] = min(case when wh.localdepotcode = 'THU' then wh.ArrDepDate end)
  , [THU Last Scan]      = max(case when wh.localdepotcode = 'THU' then wh.LastScannedTime end)
  /* , other columns */ 
from ...
  inner join dbo.whsHeader wh
    on wh.RunMovID = m.movementId
group by 
    mu.Number
  , m.MovementRef
  /* , other non aggregated columns */

Duplicates and how to get rid of them

1 Answers1