If anybody can shed some light on why my results are doubling up in the two indicated columns it will be greatly appreciated.
declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'
if @whCode = '02'
set @binLoc = 'S%'
if @whCode = '03'
set @binLoc = 'M%'
if @whCode = '04'
set @binLoc = 'B%'
if @whCode = '%'
set @binLoc = '%'
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
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
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'
order by ItemCode, Seq, BLabel