SQL queries.txt

select * from Parts p
select * from Subassemblies s
--update parts set cost = 10 where id = 3
--update parts set cost = 50 where id = 2
--insert parts (description, type, isOwnMake, Length, Cost) values ('Master assembly 1', 0, 1, 0, 0)
--insert Subassemblies(AssemblyId, SubassemblyId, CostContribution) values (8, 3, 10)
---------------
Declare @TotalCost int
exec @TotalCost = sp_walkSubassemblies @PartId = 8
print @TotalCost
---------------
CREATE FUNCTION getCost (@PartId int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @Cost int;
	select @Cost=sum(s.CostContribution * p.Cost / 100)
	from Subassemblies s
	inner join Parts p on p.Id = s.SubassemblyId
	where s.AssemblyId = @PartId
	group by AssemblyId     
	RETURN(@Cost);
END;
---------------
with components (ParentId, ChildId, Description, cost)
as
(
	select s.AssemblyId, s.SubassemblyId, s.Notes, p.cost as cost
	from Subassemblies as s
	inner join parts as p on p.Id = s.SubassemblyId
	where AssemblyId = 8
	union all
	select s.AssemblyId, s.SubassemblyId, s.Notes, s.CostContribution * p.Cost / 100 as cost
	from Subassemblies as s
	inner join parts as p on p.Id = s.SubassemblyId
	inner join components as c on s.AssemblyId = c.ChildId	
)

select * from components

---------------
--drop procedure [dbo].[sp_walkSubassemblies]
CREATE PROCEDURE [dbo].[sp_walkSubassemblies]
	@partId int,
	@TotalCost decimal(25,13) OUTPUT
AS

Declare @subassembliesId int
Declare @subPartId int
Declare @costContribution decimal(25,13)
Declare @cost decimal(25,13)
Declare @CurrCost decimal(25,13)
--Declare @TotalCost decimal(25,13)
Declare @taken int

Declare curP cursor local For
	select s.Id, SubassemblyId, CostContribution, p.OwnCost  
	from Subassemblies s
	inner join Parts p on p.Id = s.SubassemblyId
	where AssemblyId = @partId

OPEN curP 
set @TotalCost = 0
set @taken = 0
Fetch Next From curP Into @subassembliesId, @subPartId, @costContribution, @cost

While @@Fetch_Status = 0 Begin
	exec [dbo].[sp_walkSubassemblies] @subPartId, @CurrCost output
	if @CurrCost = 0
	begin
		set @CurrCost = @cost * @costContribution
		set @taken = 1
	end
	set @TotalCost = @TotalCost + (@CurrCost * @costContribution)
	
	update Subassemblies set InheritedCost = @CurrCost
	where Id = @subassembliesId
	print @partId
	print @cost
	print @costContribution
	print @CurrCost
	print @TotalCost 
	print '---'

Fetch Next From curP Into @subassembliesId, @subPartId, @costContribution, @cost
End -- End of Fetch

Close curP
Deallocate curP
update Parts set Cost = @TotalCost + OwnCost
where Id = @partId

	print '==='
	if @taken = 0
	begin
		--set @TotalCost = @cost + (@TotalCost * @costContribution)
		select @TotalCost = Cost from Parts where Id = @partId
	end
return @TotalCost
---------------
Declare @TotalCost decimal(18,2)
exec dbo.sp_walkSubassemblies 1, @TotalCost output
print @TotalCost
---------------
select * from Parts p
left join Subassemblies s on p.Id = s.AssemblyId
order by p.id
---------------
Declare @Cap int
exec dbo.CalculateCapability 170, @Cap output
print @Cap
---------------
select * from Parts
where Id in (170,4, 54,169)

select * from [dbo].[Subassemblies]
where SubassemblyId in ( 4, 54,170,169) or assemblyId in (4, 54,170,169)

--update parts set Count = 10 where id = 54
--alter table Subassemblies add Capability int
--alter table Parts add Capability int

	select p.Id as partId, s.Id, SubassemblyId, CostContribution, p.Count  
	from Subassemblies s
	inner join Parts p on p.Id = s.SubassemblyId
	--where AssemblyId = @partId
	order by p.Id

	select * from Parts where Number = '35'
--insert into Subassemblies values (170, 4, 0, 1, 'AZ test', 0)
--insert into Subassemblies values (170, 169, 0, 2, 'sub1', 0)
--update Parts set count = 40 where id = 169
---------------