— SIMPLE EXAMPLE OF SUPPLY ORDER BACKORDER QUERIES

— ADD ITEM TO SUPPLY ORDERS
— THEN POST ITEM AND UPDATE SUPPLY ORDERS AND PRODUCT TABLE

— Order Supplies

–Create Table SupplyOrders
–(
–id int identity(1,1) primary Key,
–Orderdate DateTime Not null,
–RecievedDate DateTime null,
–ProductId int references Product (ProductId),
–OrderedQuan int not null,
–Recieved int default 0
–);

Declare @numToOrder int
set @numToOrder = 2
Declare @productId int
set @productId = 4

— Place monkey order
Declare @temp table (id int)
Insert into SupplyOrders output inserted.id into @temp values (GETDATE(),null, @ProductId, @numToOrder, 0)

— The monkies arrived
Update SupplyOrders
Set RecievedDate = GetDate(), Recieved = @numToOrder where id = (select * from @temp)

Update Product
Set Backordered = Backordered – @numToOrder
where ProductId = @productId

select * from product

–Create Table Product
–(
–ProductId int not null primary key identity(1,1),
–ProductName varchar(30) not null,
–Cost Money not null,
–QuantityOnHand int default(0),
–Backordered int default(0),
–);