SQL Help

Kinetic ERP
, ,
1

I didn’t write this, I inherited it. Works great normally and for most things. This is an excerpt from a report that creates a tag for us. In this part, I believe this will only show warehouses that have already have historical information. If so, how can I change this to take what is listed, historically, and still add in the other bins we are using?

Select * into #BinSeqs
From (

Select 
	  t.Company
	, t.partNum
	, t.warehousecode
	, t.binnum
	, b.OnhandQty
	, Min(TranDate) as RecentRcvDate

from erp.PartTran t
join erp.PartBin b
	on t.company=b.company
	and t.partnum=b.partnum
	and t.WareHouseCode=b.WarehouseCode
	and t.BinNum=b.BinNum

Where 1=1
and t.company = 'comp'
and t.plant = @Plant
and t.partnum = @PartNum
and t.trantype in ('stk-stk', 'pur-stk')
--and t.binnum = @BinNum
and t.tranqty >0
and NOT( b.warehousecode in ('POTH', 'PGAR'))

Group By t.Company, t.partNum, t.warehousecode, t.binnum, b.OnhandQty

) as x;

--Select * from #BinSeqs b

Alter table #BinSeqs ADD
	SortDate int,
	SortWhse int;

Update #BinSeqs Set SortDate = CONVERT(int, CAST(RecentRcvDate as DateTime))
Update #BinSeqs Set SortWhse = 1 where warehousecode = 'BULK'
Update #BinSeqs Set SortWhse = 2 where warehousecode = 'BLKO'
Update #BinSeqs Set SortWhse = 3 where warehousecode = 'BLKH'
Update #BinSeqs Set SortWhse = 4 where warehousecode = 'BLKA'
Update #BinSeqs Set SortWhse = 5 where warehousecode = 'RCV'
Update #BinSeqs Set SortWhse = 6 where warehousecode = '501'
1 Like
2

Looks to be SQL from a stored procedure in SQL Server. IMHO there does not appear to be anything in the results that you could not grab from PartBin instead, and if you want all the bins then PartTran really is irrelevant to you isn’t it? You could, just replace all the references to PartTran with the PartBin equivalent or by commenting out statements or by placing static values. This would be easy.

But if you really do want some combination of them both, then you could just add another Insert Into #BinSeqs where the statement is going to grab all the bins from PartBin that are not already over there in PartTran in the first statement, by defining the proper SQL statement. There’s multiple ways. It’s an art form since for example, you won’t have TranDate in the PartBin table. I wouldn’t have a large enough block of time right now to give you much more than that but hopefully it’s a start for you.

2 Likes