create database [dbBom] on  primary (   
  name = n'dbBom', filename = n'c:\msdnmag\dbBom.mdf', 
  size = 50mb , maxsize = unlimited, filegrowth = 2mb )
log on ( 
  name = n'dbBom_log', filename = n'c:\msdnmag\dbBom_log.ldf', 
  size = 10mb , maxsize = 2048gb , filegrowth = 10%);

create table dbo.part(
  partID smallint not null,
  descr varchar(50) not null,
  cost money not null);
create table dbo.partList(
  partListID int not null,
  partID smallint not null,
  quantity smallint not null);
create table dbo.billPartList(
  billID int not null,
  partListID int not null);  
create table dbo.bill(
  billID int not null,
  parentBillID int null,  
  descr varchar(50) not null);

alter table dbo.part  
  add constraint pkPart 
  primary key clustered(partID);
alter table dbo.bill
  add constraint pkBill 
  primary key clustered(billID);
alter table dbo.partList  
  add constraint pkPartList 
  primary key clustered(partListID);
alter table dbo.billPartList
  add constraint pkBillPartList 
  primary key clustered(billID,partListID);

alter table dbo.part  
  add constraint uqDescr 
  unique(descr);
alter table dbo.bill
  add constraint uqBill 
  unique(descr);
alter table dbo.partList  
  add constraint uqPartList 
  unique(partID,quantity); 

alter table dbo.partList  
  add constraint fkPartList_Part 
  foreign key(partID) 
  references dbo.part(partID)
  on delete cascade
  on update no action;

alter table dbo.bill  
  add constraint fkBill_Bill 
  foreign key(parentBillID) 
  references dbo.bill(BillID)
  on delete no action
  on update no action; 

alter table dbo.billPartList
  add constraint fkBillPartList_PartList 
  foreign key(partListID) 
  references dbo.partList(partListID)
  on delete cascade
  on update no action;
alter table dbo.billPartList
  add constraint fkBillPartList_Bill 
  foreign key(billID) 
  references dbo.bill(billID)
  on delete cascade
  on update no action;

with c as (
  select billID,parentBillID,descr,0 as [level]
  from dbo.bill b 
  where b.parentBillID is null
    union all
  select b.billID,b.parentBillID, 
    b.descr,[level] + 1
  from dbo.bill b join c on b.parentBillID = 
    c.billID)
select descr,[level],billID,parentBillID 
  as bill 
from c

with c as (
  select billID,parentBillID,descr,0 as [level]
  from dbo.bill b 
  where b.parentBillID is null
    union all
  select b.billID,b.parentBillID,b.descr,[level] + 1
  from dbo.bill b join c on b.parentBillID = c.billID)
select p.partID,p.descr
from c
join dbo.billPartList bpl on c.billID = bpl.billID
left join dbo.partList pl on bpl.partListID = pl.partListID
join dbo.part p on pl.partID = p.partID
group by p.partID,p.descr;

with c as (
  select billID,parentBillID,descr,0 as lvl
  from dbo.bill b 
  where b.parentBillID is null
    union all
  select b.billID,b.parentBillID,b.descr,lvl + 1
  from dbo.bill b join c on b.parentBillID = c.billID)
select SUM(p.cost*pl.quantity) * 2.0
  from c
  join dbo.billPartList bpl on c.billID =     bpl.billID
  left join dbo.partList pl on     bpl.partListID= pl.partListID
  join dbo.part p on pl.partID = p.partID

with c as (
  select b.descr,b.billID,b.parentBillID,0     as lvl
  from dbo.partList pl
  left join dbo.billPartList bpl on    pl.partListID = bpl.partListID
  left join dbo.bill b on bpl.billID =     b.billID
  where pl.partID = 19
    union all
  select b.descr,b.billID,b.parentBillID,lvl+1
  from dbo.bill b
  join c on c.parentBillID = b.billID)
select * from c;

with c as (
  select '/'+cast(billID as varchar(49)) as path,BillID
  from dbo.bill b 
  where b.parentBillID is null
    union all 
  select cast(c.path+'/'+CAST(b.billID as varchar(4)) as varchar(50)),    b.billID
  from dbo.bill b join c on b.parentBillID = c.billID)
select c.path+'/',b.descr
from c join dbo.bill b on c.billID = b.billID
order by 1;

update dbo.bill set parentBillID = 320 where billID = 507
go
select * from dbo.bill where parentBillID = 320;
go

begin transaction
  delete from dbo.billPartList where billID=507;
  delete from dbo.bill where billID=507;
  delete from dbo.part where partID=45;
commit;
go
select * from dbo.billPartList;
select * from dbo.bill;

create table dbo.bill2(
  billPath HierarchyID not null,
  billID smallint not null,
  parentBillPath as billPath.GetAncestor(1) persisted,
  descr varchar(50) not null);

alter table dbo.bill2
  add constraint pkBill2
  primary key(billPath);

alter table dbo.bill2
  add constraint fkBill2Parent 
  foreign key (parentBillPath) references dbo.bill2(billPath);

insert into dbo.bill2(billPath,billID,descr) 
  values (hierarchyID::GetRoot(),0,'All Bills'); 

with c(billID,[path],[descr]) as (
  select b.billID,'/'+CAST(b.billID as varchar(max)),descr
  from dbo.bill b
  where parentBillID is null
    union all
  select b.billID,c.path+'/'+CAST(b.billID as varchar(max)),b.descr
  from dbo.bill b
  join c on b.parentBillID = c.billID)
insert into dbo.bill2(billpath,billID,descr)
select path+'/' as [path],billID,descr 
from c
order by c.path;

alter table dbo.billPartList
drop constraint fkBillPartList_Bill
go
alter table dbo.billPartList
  add constraint fkBillPartList_Bill2 
  foreign key(billPath) 
  references dbo.bill2(billPath)
  on delete cascade
  on update no action;

select billPath.ToString() from dbo.bill2 where   billID=405; 

create function dbo.Vectorize(@i hierarchyID)
returns @t table (position int identity(1,1) 
  not null,nodeValue int not null)
as begin
  declare @list varchar(max) = @i.ToString();
  declare @delimit int;
  set @list = substring(@list,2,len(@list)-1);
  while len(@list) > 1 begin
    set @delimit = charindex('/',@list);
    insert into @t values (cast(substring(@list,1,@delimit-1) as int));
    set @list = substring(@list,@delimit+1,len(@list));
  end;
  return;
end;

declare @anyBill int = 405;
select b1.billPath,b2.billID,b2.descr from dbo.bill2 b1
  cross apply dbo.vectorize(b1.billPath) p  
  join dbo.bill2 b2 on b2.billID = p.nodeValue
  where b1.billID = @anyBill;

declare @anyBill int = 110;
declare @sourceBillPath hierarchyID;
select @sourceBillPath = billPath from dbo.bill2 where billID = @anyBill;
select p.partID as 'partID', 
  p.descr 'partName' 
  from dbo.bill2 b2     
  left join dbo.billPartList bpl on b2.billID = bpl.billID
  left join dbo.partList pl on bpl.partListID = pl.partListID
  left join dbo.part p on pl.partID =p.partID
  where b2.billPath.IsDescendantOf(@sourceBillPath)=1
  and p.partID is not null
  order by p.partid;

declare @anyBill int = 110;
declare @sourceBillPath hierarchyID;
select @sourceBillPath = billPath 
from dbo.bill2 where billID = @anyBill;
  select SUM(p.cost * pl.quantity)*2.0
  from dbo.bill2 b2     
  left join dbo.billPartList bpl on b2.billID = bpl.billID
  left join dbo.partList pl on bpl.partListID = pl.partListID
  left join dbo.part p on pl.partID =p.partID
  where b2.billPath.IsDescendantOf(@sourceBillPath)=1;

declare @partID int = 20;
with c(billPath,descr) as (
  select b.billPath,b.descr
  from dbo.part p
  join dbo.partList pl on p.partID = pl.partID
  join dbo.billPartList bpl on pl.partListID = bpl.partListID
  join dbo.bill2 b on bpl.billID = b.billID
  where p.partID = @partID
    union all
  select b.billPath,b.descr
  from dbo.bill2 b
  join c on b.billPath = c.billPath.GetAncestor(1))
select distinct descr,billPath
from c
where billPath <> hierarchyID::GetRoot()
order by billPath;

begin  
  begin transaction
  declare @mp int;
  declare @mpl int;
  select @mp = MAX(partID)+10 from dbo.part;
  insert into dbo.part(partID,descr,cost) 
    output inserted.*
    values (@mp,'4GB USB 2.0 Memory Stick',20.0);
  select @mpl = MAX(partListID)+10 from dbo.partList;  
  insert into dbo.partList(partListID,partID,quantity)
    output inserted.*
    values (@mpl,@mp,1);

declare @root hierarchyID;
declare @newBillPath hierarchyID;
declare @newBillID int;

select @root = billPath
  from dbo.bill2 
  where billID = 110;

select @newBillPath = max(billPath) 
  from dbo.bill2 
  where billPath.IsDescendantOf(@root) =1 
  and billPath.GetLevel() = @root.GetLevel()+1;

select @newBillPath =@root.GetDescendant(@newBillPath,null);   

select @newBillID = nodeValue 
  from dbo.Vectorize(@newBillPath) 
  where position = @newBillPath.GetLevel();

insert into dbo.bill2
  output inserted.*
  values (@newBillPath,@newBillID,'Summer Bonus Package');

insert into dbo.billPartList(billID,partListID)
  output inserted.*
  values (@newBillID,@mpl);

commit;
end;

begin
declare @partToDelete int = 45;
begin transaction
delete from dbo.bill2 where billID in
  (  select billID from dbo.billPartList bpl
  join dbo.partList pl on bpl.partListID = pl.partListID
  join dbo.part p on pl.partID = p.partID
  where p.partID = @partToDelete);
delete from dbo.part where partID = @partToDelete;    
commit;
end;