Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007
    Posts
    17

    Unanswered: Updating Multiple rows based on sums from another table

    Hello All

    I am trying to figure out if what i am attempting to do is possible and whether or not my approach is wrong to begin with.

    I am trying to build a custom report for our accounting system which is Traverse from Open systems. This is what i have done in the stored procedure thus far

    Code:
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    
    ALTER  PROCEDURE rptArFLSalesByCustItemized_sp
    @custId pCustID,
    @dateFrom datetime,
    @dateThru datetime,
    @itemIdFrom pItemId,
    @itemIdThru pItemId
    as
    set nocount on
    
    -- define some variables for previous year
    declare @LYqty int, @LyAmt money, @LYfrom datetime, @LYthru datetime
    
    -- set defaults
    SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(itemId) FROM tblInItem))
    SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(itemId) FROM tblInItem))
    SET @LYfrom=DATEADD(YEAR,-1,@dateFrom)
    SET @LYthru=DATEADD(YEAR, -1, @dateThru)
    
    -- create small temp table to hold customer info
    Create Table #tmpArCustInfo
    (
    	custId pCustID,
    	custName VARCHAR (30),
    )
    -- populate customer temp table with info
    Insert into #tmpArCustInfo
    select custId, custName
    from tblArCust
    WHERE custId = @custId
    
    
    -- create a temp table to hold the Data for each Item
    Create Table #tmpArSalesItemized
    (
    	itemId pItemId,
    	productLine VARCHAR (12),
    	pLineDesc VARCHAR (35),
    	descr VARCHAR (35),
    	LYQtySold int,
    	LYTDQtySold int,
    	QtySold int,
    	LYTDsales money,
    	totalSales money,
    	LastInvDate datetime,
    )
    
    -- populate the temp table with all of the inventory items
    insert into #tmpArSalesItemized
    select ii.itemId, ii.productLine, ip.Descr, ii.Descr, 0,0,0,0,0, NULL
    from tblInItem ii, tblInProductLine ip
    where ip.productLine = ii.productLine
    AND ii.itemId BETWEEN @itemIdFrom AND @itemIdThru
    
    -- update table with this years quantities
    update #tmpArSalesItemized
    SET QtySold = (select SUM(QtyOrdSell) from tblArHistDetail hd
    		where TransId IN (select TransId from tblArHistHeader where custId = @custId)
    		AND orderDate IN (select OrderDate from tblArHistHeader where OrderDate BETWEEN @dateFrom AND @dateThru)
    		AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru
    		GROUP BY hd.partId
    )
    	
    -- Return the temp tables results
    select * from #tmpArSalesItemized, #tmpArCustInfo
    
    drop table #tmpArSalesItemized, #tmpArCustInfo
    
    return
    
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    My problems begin where i want to start updating all of the Qty's of the QtySold field. I have managed to get it to write the same sum in every field but i cannot figure out how to update each row based on the sum of the qty found for that item in the tblArHistDetails table, trouble is too that there is no reference to the custId in that table either. The custId resides in tblArHistHeader and is linked to the details table via the TransId column. So really i need to update many rows based on criteria from 2 other tables.

    Can anyone please help? I dont have a clue how to make this work, and most of what i have learned about sql thus far has been from opening other stored procs etc in the accounting system and just reading to see how the developers have done things.

    Thanks
    Jamie

  2. #2
    Join Date
    Jan 2007
    Posts
    17

    Solved my own problems...

    Well i just kept on reading and looking at other sp's within the database. I have come up with my solution and thought i would post it in case someone else comes accross the post and is contemplating a response.

    anyway here is the completed SP that functions very well.

    I imagine there is always a better way to do things but i was just happy to get it working at all.

    Feel free to suggest better or more efficient methods.

    Code:
    Alter  PROCEDURE rptArFLSalesByCustItemized_sp
    @custId pCustID,
    @custName VARCHAR (30) ,
    @dateFrom datetime,
    @dateThru datetime,
    @itemIdFrom pItemId,
    @itemIdThru pItemId
    as
    set nocount on
    
    -- define some variables for previous year
    declare @LYqty int, 
    	@LyAmt money, 
    	@LYfrom datetime, 
    	@LYthru datetime
    
    -- set defaults
    SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(partId) FROM tblArHistDetail WHERE partId <> ''))
    SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(partId) FROM tblArHistDetail))
    SET @LYfrom=DATEADD(YEAR,-1,@dateFrom)
    SET @LYthru=DATEADD(YEAR, -1, @dateThru)
    SET @custName=ISNULL(@custName, (SELECT custName FROM tblArCust WHERE custId = @custId))
    -- create a temp table to hold the Data for each Item
    Create Table #tmpArSalesItemized
    (
    	custId pCustID,
    	custName VARCHAR (30),
    	itemId pItemId,
    	productLine VARCHAR (12),
    	pLineDesc VARCHAR (35),
    	descr VARCHAR (35),
    	LYQtySold int,
    	QtySold int,
    	LYsales money,
    	totalSales money,
    	LastInvDate datetime
    )
    
    insert into #tmpArSalesItemized
    select @custId
    	, @custName
    	, hd.partId
    	, ii.productLine
    	, ip.Descr
    	, hd.[desc]
    	, 0
    	, 0 --SUM(hd.QtyOrdSell)
    	, 0
    	, 0 --SUM (hd.QtyOrdSell * hd.unitPriceSell)
    	, MAX(hh.invcDate)
    	from tblArHistHeader hh
    		, tblArHistDetail hd
    		, tblInItem ii
    		, tblInProductLine ip
    	where hh.TransId = hd.TransId
    	and hh.postrun = hd.postrun
    	AND hd.partId = ii.itemId
    	AND hh.custId = @custId
    	AND (hh.invcDate BETWEEN @dateFrom AND @dateThru or hh.invcDate between @LYfrom and @LYthru)
    	AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru
    	AND ip.productLine = ii.productLine
    	GROUP BY hd.partId, ii.productLine, hd.[desc], ip.Descr
    	ORDER BY ii.productLine, hd.partId
    
    update #tmpArSalesItemized
    	set QtySold = isnull((select sum(ISNULL(hh.transType,1)*isnull(hd.qtyShipSell,0))
    			from tblArHistHeader hh, tblArHistDetail hd
    			, tblInItem ii, tblInProductLine ip
    			where hh.custId = @custId 
    			and hh.TransId = hd.TransId 
    			and hh.invcDate between @dateFrom and @dateThru
    			and hh.postrun		= hd.postrun
    			AND ip.productLine 	= ii.productLine
    			and hd.partId		= ii.itemId
    			AND ip.productLine 	= #tmpArSalesItemized.productLine
    			and hd.partId 		= #tmpArSalesItemized.itemId
    			and hd.[desc] 		= #tmpArSalesItemized.descr
    			and ip.Descr 		= #tmpArSalesItemized.pLineDesc
    			group by hd.partId),0);
    
    update #tmpArSalesItemized
    	set TotalSales = isnull((select sum(ISNULL(hh.transtype,1)*isnull(hd.qtyShipSell,0) * isnull(hd.unitPriceSell,0))
    			from tblArHistHeader hh, tblArHistDetail hd
    			, tblInItem ii, tblInProductLine ip
    			where hh.custId = @custId 
    			and hh.TransId = hd.TransId 
    			and hh.invcDate between @dateFrom and @dateThru
    			and hh.postrun		= hd.postrun
    			AND ip.productLine 	= ii.productLine
    			and hd.partId		= ii.itemId
    			AND ip.productLine 	= #tmpArSalesItemized.productLine
    			and hd.partId 		= #tmpArSalesItemized.itemId
    			and hd.[desc] 		= #tmpArSalesItemized.descr
    			and ip.Descr 		= #tmpArSalesItemized.pLineDesc
    			group by hd.partId),0);
    
    update #tmpArSalesItemized
    	set LYQtySold = isnull((select sum(ISNULL(hh.transtype,1)*isnull(hd.qtyShipSell,0))
    			from tblArHistHeader hh, tblArHistDetail hd
    			, tblInItem ii, tblInProductLine ip
    			where hh.custId = @custId 
    			and hh.TransId = hd.TransId 
    			and hh.invcDate between @LYfrom and @LYthru
    			and hh.postrun		= hd.postrun
    			AND ip.productLine 	= ii.productLine
    			and hd.partId		= ii.itemId
    			AND ip.productLine 	= #tmpArSalesItemized.productLine
    			and hd.partId 		= #tmpArSalesItemized.itemId
    			and hd.[desc] 		= #tmpArSalesItemized.descr
    			and ip.Descr 		= #tmpArSalesItemized.pLineDesc
    			group by hd.partId),0);
    
    update #tmpArSalesItemized
    	set LYsales = isnull((select sum(ISNULL(hh.transtype,1)*isnull(hd.qtyShipSell,0) * isnull(hd.unitPriceSell,0))
    			from tblArHistHeader hh, tblArHistDetail hd
    			, tblInItem ii, tblInProductLine ip
    			where hh.custId = @custId 
    			and hh.TransId = hd.TransId 
    			and hh.invcDate between @LYfrom and @LYthru
    			and hh.postrun		= hd.postrun
    			AND ip.productLine 	= ii.productLine
    			and hd.partId		= ii.itemId
    			AND ip.productLine 	= #tmpArSalesItemized.productLine
    			and hd.partId 		= #tmpArSalesItemized.itemId
    			and hd.[desc] 		= #tmpArSalesItemized.descr
    			and ip.Descr 		= #tmpArSalesItemized.pLineDesc
    			group by hd.partId),0);
    
    -- Return the temp table results
    select * from #tmpArSalesItemized
    
    drop table #tmpArSalesItemized
    
    return
    J

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •