July 10, 2014 at 12:41 am
Hi,
I have a stored procedure that accepts 3 parameters - Date, Location and Item and returns the details of the last receipt of the item before the given date for that location. I now need to find the last received cost for a series of transactions (around 5200 lines) that I pull out using a query. These are transfers from our warehouse to a branch and I need to have the cost applicable per line.
My query to select the three parameters is something like this
SELECT x.xfer_no, x.from_loc, x.to_loc, x.date_shipped, x.date_entered, x.date_recvd, xl.line_no, xl.part_no, xl.description, xl.shipped, xl.qty_rcvd, xl.UoM, U.Description AS UoM_Desc, xl.conv_factor, xl.cost, IL.avg_cost
FROMxfers x
INNER JOINxfer_list xl ON x.xfer_no = xl.xfer_no
INNER JOINUOM_list U ON xl.UoM = U.UoM
INNER JOINinv_list IL ON IL.Location = 'xxx' AND IL.Part_no = xl.part_no
WHERE x.to_loc = 'xxx'
I want to use the values x.date_recvd, x.to_loc and xl.part_no to be the date, location and item parameter. I then have to add the output of the stored procedure as the last value in the above query so that I am able to cost the transfer.
I am totally lost... any tips.
pssudarshan
July 14, 2014 at 10:34 pm
I have found a solution of sorts for this issue. It is quite messy with a couple of temp tables etc but it appears to work. I am sure some good coding experts can actually make it much smoother and faster. Here is my code
SELECT idno = 0, x.xfer_no, x.from_loc, x.to_loc, x.date_shipped, x.date_entered, x.date_recvd, xl.line_no, xl.part_no, xl.description,
xl.shipped, xl.qty_rcvd, xl.UoM, U.Description AS UoM_Desc, xl.conv_factor, xl.cost, IL.avg_cost
INTO #Xfer_Details
FROMxfers x
INNER JOINxfer_list xl ON x.xfer_no = xl.xfer_no
INNER JOINUOM_list U ON xl.UoM = U.UoM
INNER JOINinv_list IL ON IL.Location = 'TAV1' AND IL.Part_no = xl.part_no
WHERE x.to_loc = 'TAV1'
GO
-- Update IDNo field in temp table #Xfer_details
DECLARE @ID INT
SET @ID = 0
UPDATE #Xfer_details
SET @ID = idno =@id+1
GO
-- Create Temp table #Xfer_cost
CREATE TABLE #Xfer_Cost (
xfer_date datetime,
xfer_no INT,
xfer_line INT,
From_Loc VARCHAR(10),
Item VARCHAR(16),
Item_Description VARCHAR(55),
Item_UoM VARCHAR(2),
Receipt_Date VARCHAR(10),
Last_Receipt_no INT,
Branch VARCHAR(5),
Qty_Received DECIMAL(28,8),
Receipt_UoM CHAR(2),
UoM_Description VARCHAR(30),
Conv_Factor DECIMAL(28,8),
GroupCode VARCHAR(30),
Resource_Type VARCHAR(30),
Void VARCHAR(3),
PO_no INT,
PO_TaxCode VARCHAR(20),
Unit_Cost DECIMAL(28,8)
)
-- Populate #Xfer_cost - setting parameter values
DECLARE @IntFlag INT
DECLARE @MaxNo INT
SELECT @MaxNo = MAX(IDno) FROM #Xfer_Details
SET @IntFlag = 1
WHILE (@IntFlag <= @MaxNo)
BEGIN
DECLARE @Date DateTime
DECLARE @Item VARCHAR(16)
DECLARE @Location VARCHAR(16)
DECLARE @xfer_no INT
DECLARE @xfer_line INT
DECLARE @From_loc VARCHAR(10)
DECLARE @Xfer_date Datetime
SELECT @date =x.date_shipped , @Item = x.part_no, @Location = x.from_loc, @xfer_no = x.xfer_no, @xfer_line = x.line_no, @from_loc = x.from_loc, @xfer_date = x.date_shipped
FROM #Xfer_Details x
WHERE x.Idno = @IntFlag
-- Populating #Xfer_cost - Calling stored procedure
INSERT INTO #xfer_cost
EXEC SnS_LastPriceByItem @Date, @item, @location, @xfer_no, @xfer_line, @from_loc, @xfer_date
SET @IntFlag = @IntFlag + 1
END
GO
-- Final Select from #Xfer_Cost
SELECTx.IDno, x.xfer_no, x.line_no, x.from_loc, x.to_loc, x.date_shipped, x.date_recvd, x.part_no, x.description, x.shipped, x.qty_rcvd, x.UoM, x.UoM_Desc, x.conv_factor,
x.cost, x.avg_cost, c.branch, c.receipt_date, c.last_receipt_no, c.qty_received, c.receipt_uom, c.uom_description, c.conv_factor, c.groupcode, c.resource_type,
c.po_no, c.po_taxcode, c.unit_cost, VEPPieceCost =
CASEWHEN PO_TaxCode = 'INPVEP' THEN unit_cost/c.conv_factor
WHEN PO_TaxCode = 'INPVIP' THEN (unit_cost/c.conv_factor)/1.15
WHEN PO_TaxCode = 'EXEMPT' THEN unit_cost/c.conv_factor
END
FROM #xfer_Details X
LEFT OUTER JOIN #xfer_cost C ON X.xfer_no = C.xfer_no AND X.line_no = C.xfer_line
-- Clean up temp tables
DROP TABLE #Xfer_Details
DROP TABLE #Xfer_Cost
All constructive comments are invited.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply