Results doubling up in two columns, can't work out why?

• david.dartnell

  Ten Centuries

  Points: 1276

  More actions

  August 12, 2014 at 7:36 pm

  #294370

  Hi Everyone,

  I have written some SQL that relates to the (partial) result set below -

  

  I am having an incredibly difficult time trying to work out why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.

  My query looks like the following -

  go

  declare @whCode varchar(5), @binLoc varchar(2)

  set @whCode = '04'

  if @whCode = ''

  set @whCode = '%'

  if @whCode = '02'

  set @binLoc = 'S%'

  else

  if @whCode = '03'

  set @binLoc = 'M%'

  else

  if @whCode = '04'

  set @binLoc = 'B%'

  else

  if @whCode = '%'

  set @binLoc = '%'

  ;

  with CTEResultSet

  as

  (

  select ROW_NUMBER()

  over

  (

  partition by td.itemCode

  order by td.itemCode

  ) as Seq,

  td.ItemCode as ItemCode

  , td.Dscription as ItemDescription

  , cast(td.Quantity as decimal(10,0)) as OrderQty

  --, cast(twhs.OnHand as decimal(10,0)) as OnHand

  , th.CardCode as BPCode

  , th.CardName as BPName

  , cast(th.DocDate as nvarchar(20)) as DocumentDate

  , cast(th.DocDueDate as nvarchar(20)) as DocDueDate

  , cast(th.DocNum as decimal (10,0)) as SONumber

  , tsp.SlpName as SPerson

  --, twhs.WhsCode as WhsCode

  , isnull(tbloc.BINLABEL, '') as BLabel

  , cast(tbloc.QUANTITY as decimal(10,0)) as BQty

  from AU.dbo.RDR1 td

  inner join AU.dbo.ORDR th on th.DocEntry = td.DocEntry

  left outer join AU.dbo.OSLP tsp on tsp.SlpCode = td.SlpCode

  left outer join A1Warehouse.dbo.BINLOCAT tbloc on tbloc.PRODUCT = td.ItemCode collate SQL_Latin1_General_CP850_CI_AS --AND tbloc.WAREHOUSE = td.WhsCode collate SQL_Latin1_General_CP850_CI_AS

  where

  td.LineStatus = 'O'-- Order is Open

  and td.WhsCode like @whCode-- Order lines are from Whs Code

  and tbloc.QUANTITY > 0-- Location has Qty > 0

  and tbloc.BINLABEL like @binLoc-- BinLabel corresponds with Warehouse

  )

  select

  --Seq,

  case when Seq = 1 then ItemCode else '' end as 'Item Code'

  , case when Seq = 1 then ItemDescription else '' end as 'Item Description'

  , case when Seq = 1 then cast(OrderQty as nvarchar(20)) else '' end as 'Order Qty'

  , case when Seq = 1 then BPCode else '' end as 'BP Code'

  , case when Seq = 1 then BPName else '' end as 'BP Name'

  , case when Seq = 1 then cast(SONumber as nvarchar(20)) else '' end as 'Sales Order No.'

  , case when Seq = 1 then SPerson else '' end as 'Sales Person'

  --, case when Seq = 1 then WhsCode else '' end as 'Whs Code'

  , BLabel as 'Bin Label'

  , isnull(cast(BQty as nvarchar(20)), '') as 'Bin Qty'

  from CTEResultSet

  order by ItemCode, Seq, BLabel

  If anybody can shed some light on why my results are doubling up in the two indicated columns it will be greatly appreciated.

  Kind Regards,

  David

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  August 12, 2014 at 9:02 pm

  #1737413

  First guess without further information would be duplicate records in A1Warehouse.dbo.BINLOCAT

  😎