CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jo Jo
Jo Jo

Reputation: 73

Duplicates and how to get rid of them

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],

Upvotes: 1

Views: 89

Answers (1)

SqlZim
SqlZim

Reputation: 38023

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 */

Upvotes: 1

Related Questions