quote:

---

Originally posted by Srinika

Its the trend of some "Programmers" <including me - sometime back> to think in terms of a cursor whenever some T-SQL needs to be written. Its easier to think as a standard program (written using VB / Java /...) and consider one record at a time, getting values to variables and assigning those etc.

eg. if u want to increase the salary of employee table by 10 % u can use a single SQL query or u can (but don't) use a cursor where each record is fed to variables, the salary value is multiplied by 1.1 and assigned to another table, then updating the record with the new value and do for each record in a loop.
The cursor option kills time.

But if u properly use SQL / T-SQL, u can avoid the cursors in most situations.
Cursors should be avoided whenever possible, as those are very very inefficient.

Again temp tables are used if there is a strong need for that.

Ur requirements are not clear to give a solution, but u may use something like

UPDATE Inventory SET UnitCost = (<query to find sum of VehicleSalvageCost> * (<query to find WholesalePrice > / (<query to find sum of WholesalePrice >
WHERE (CURRENT OF get_unit_cost_cursor) AND
......

---

quote:

---

Originally posted by cmdr_skywalker

I forgot to put the alias. I wasn't sure if it will work without the alias in the table. At anyrate, the final query should look like this:

UPDATE
SET UnitCost =
CASE
WHEN W.SUM_WholesalePrice = 0 THEN 0
ELSE
(V.SUM_VehicleSalvageCost * WholesalePrice) / W.SUM_WholesalePrice
END
FROM
Inventory I,
(SELECT SUM(WholesalePrice) AS SUM_WholesalePrice
FROM Inventory
WHERE StockTicketNumber = i.StockTicketNumber
)AS W,
(SELECT SUM(VehicleSalvageCost) AS SUM_VehicleSalvageCost
FROM VehicleStockDetails
WHERE StockTicketNumber = i.StockTicketNumber
) AS V
WHERE
I.UnitCost = 0


May the Almighty God bless us all!

---

select	I.UnitID
	,I.StockTicketNumber
	,I.WholesalePrice
	,UnitCost = (I.WholesalePrice * sum1.SUM_WholesalePrice) / sum2.SUM_VehicleSalvageCost
-- instead of select, you can update by using the 2 lines below
-- update	I set
-- 	UnitCost = (I.WholesalePrice * sum1.SUM_WholesalePrice) / sum2.SUM_VehicleSalvageCost
from	dbo.Inventory as I
	join
	(
		select	StockTicketNumber
			,sum(WholesalePrice) as SUM_WholesalePrice
		from	dbo.Inventory
		group by
			StockTicketNumber
	) as sum1
		on I.StockTicketNumber = sum1.StockTicketNumber
	join
	(
		select	StockTicketNumber
			,sum(VehicleSalvageCost) as SUM_VehicleSalvageCost
		from	dbo.VehicleStockDetails
		group by
			StockTicketNumber
		having	sum(VehicleSalvageCost) <> 0 -- one way of avoiding / 0
	) as sum2
		on I.StockTicketNumber = sum2.StockTicketNumber

update	dbo.Inventory set
	UnitCost =
	(	I.WholesalePrice
		* (	select	sum(VehicleSalvageCost)
			from	dbo.VehicleStockDetails as V
			where	dbo.Inventory.StockTicketNumber = V.StockTicketNumber)
	)
		/ (	select	coalesce(nullif(sum(WholesalePrice),0),1) -- avoid / 0, by setting 0 to null and null to 1 
			from	dbo.Inventory as I
			where	dbo.Inventory.StockTicketNumber = I.StockTicketNumber)

quote:

---

Originally posted by rockmoose

skywalker, you are mixing derived tables and correlated subqueries in the sql.

-- derived tables

select	I.UnitID
	,I.StockTicketNumber
	,I.WholesalePrice
	,UnitCost = (I.WholesalePrice * sum2.SUM_VehicleSalvageCost / sum1.SUM_WholesalePrice)
-- instead of select, you can update by using the 2 lines below
-- update	I set
-- 	UnitCost = (I.WholesalePrice * sum2.SUM_VehicleSalvageCost / sum1.SUM_WholesalePrice)
from	dbo.Inventory as I
	join
	(
		select	StockTicketNumber
			,sum(WholesalePrice) as SUM_WholesalePrice
		from	dbo.Inventory
		group by
			StockTicketNumber

having	sum(WholesalePrice) <> 0 -- one way of avoiding / 0
	) as sum1
		on I.StockTicketNumber = sum1.StockTicketNumber
	join
	(
		select	StockTicketNumber
			,sum(VehicleSalvageCost) as SUM_VehicleSalvageCost
		from	dbo.VehicleStockDetails
		group by
			StockTicketNumber
		
	) as sum2
		on I.StockTicketNumber = sum2.StockTicketNumber

-- correlated subqueries

update	dbo.Inventory set
	UnitCost =
	(	I.WholesalePrice
		* (	select	sum(VehicleSalvageCost)
			from	dbo.VehicleStockDetails as V
			where	dbo.Inventory.StockTicketNumber = V.StockTicketNumber)
	)
		/ (	select	coalesce(nullif(sum(WholesalePrice),0),1) -- avoid / 0, by setting 0 to null and null to 1 
			from	dbo.Inventory as I
			where	dbo.Inventory.StockTicketNumber = I.StockTicketNumber)

rockmoose

---

create procedure dbo.updatecostpricebyinventoryid 
    (
    @InventoryID    int,
    @CostPrice      money
    )
as
    set nocount on
    
    update  dbo.INVENTORY
    set     CostPrice = @CostPrice
    where   (InventoryID = @InventoryID)
    if (@@rowcount = 0) begin
        raiserror(55000, 16, 1)
        return (-6)
    end
        
    set nocount off
    return (0)

select	I.InventoryID
	,I.StockTicketNumber
	,I.WholesalePrice
	,UnitCost = 
CASE 
WHEN I.UnitCost > 0 THEN I.UnitCost 
ELSE 
(I.WholesalePrice * (sum2.SUM_VehicleSalvageCost + 1200) / sum1.SUM_WholesalePrice)
END 
from	 dbo.Inventory as I
	join
	(
		select	StockTicketNumber
			,sum(WholesalePrice) as SUM_WholesalePrice
		from	 dbo.Inventory
		group by
			StockTicketNumber

having	sum(WholesalePrice) <> 0 

	) as sum1
		on I.StockTicketNumber = sum1.StockTicketNumber
	join
	(
		select	StockTicketNumber
			,sum(VehicleSalvageCost) as SUM_VehicleSalvageCost
		from	dbo.Vehicle_Cost_Payment
		group by
			StockTicketNumber
		
	) as sum2
		on I.StockTicketNumber = sum2.StockTicketNumber

quote:

---

Originally posted by cmdr_skywalker

rockmoose, you're right. i forgot that the table resolution in a FROM SECTION is made within the subquery only and thus, does not recognized correlate with the other table alias.

May the Almighty God bless us all!

---