Prev: Conditional UNION
Next: Threading in CLR procedure
From: Jordan on 27 May 2010 16:43
We are trying to get some statistics on our Inventory people to see if they
are issuing material to work orders on time. My work order table has a
field that shows the last transaction date of the order, but if Inventory
issue all the material then has to issue more because of scrap later they
get dinged as being late.

We have a Transaction History table that shows every issue however I don't
know a good way to find out when the total number of needed parts was
reached. For Example I am creating a shop order that needs 3 parts, 50 of
each:

ShopOrder PartNeeded Due Date Due Qty
1000001 ABC 5/1/2010 50
1000001 DEF 5/1/2010 50
1000001 GHI 5/1/2010 50

Shop Order PartIssued DateIssued IssuedQty
1000001 ABC 4/1/2010 25
1000001 DEF 4/1/2010 25
1000001 GHI 4/1/2010 25
1000001 ABC 4/20/2010 25
1000001 DEF 4/20/2010 25
1000001 GHI 4/20/2010 25
1000001 GHI 5/2/2010 10

In the data above you can see that all the needed parts were issued by 4/20
so Inventory did their job, however because of whatever reason they had to
issue 10 extra on 5/2 a day after the due date. When I use MAX for the date
issue it will look like Inventory was late. I need something that total
from first to last issue and find the date when the 50 for any given part
was reached.



From: Dan Guzman on 28 May 2010 08:47
> I need something that total from first to last issue and find the date
> when the 50 for any given part was reached.

Below is one method:

CREATE TABLE dbo.WorkOrder
(
ShopOrder int NOT NULL,
PartNeeded char(3) NOT NULL,
DueDate date NOT NULL,
DueQty int NOT NULL,
CONSTRAINT PK_WorkOrder PRIMARY KEY (ShopOrder, PartNeeded, DueDate)
);

INSERT INTO dbo.WorkOrder
VALUES
(1000001,'ABC','2010-05-01',50),
(1000001,'DEF','2010-05-01',50),
(1000001,'GHI','2010-05-01',50),
(1000002,'ABC','2010-05-01',50),
(1000002,'DEF','2010-05-01',50),
(1000002,'GHI','2010-05-01',50);

CREATE TABLE dbo.TransactionHistory
(
ShopOrder int NOT NULL,
PartIssued char(3) NOT NULL,
DateIssued date NOT NULL,
IssuedQty int NOT NULL,
CONSTRAINT PK_TransactionHistory PRIMARY KEY (ShopOrder, PartIssued,
DateIssued)
);

INSERT INTO dbo.TransactionHistory
VALUES
(1000001,'ABC','2010-04-01',25),
(1000001,'DEF','2010-04-01',25),
(1000001,'GHI','2010-04-01',25),
(1000002,'ABC','2010-04-01',25),
(1000002,'DEF','2010-04-01',60),
(1000001,'ABC','2010-04-20',25),
(1000001,'DEF','2010-04-20',25),
(1000001,'GHI','2010-04-20',25),
(1000001,'GHI','2010-05-02',10);
GO

SELECT
WorkOrder.ShopOrder,
WorkOrder.PartNeeded,
WorkOrder.DueDate,
DailyFilledOrders.DateIssued
FROM
(SELECT
ShopOrder,
PartIssued,
DateIssued,
(SELECT SUM(IssuedQty)
FROM dbo.TransactionHistory prev
WHERE
prev.ShopOrder = curr.ShopOrder
AND prev.PartIssued = curr.PartIssued
AND prev.DateIssued <= curr.DateIssued) AS TotalIssuedQty
FROM dbo.TransactionHistory curr
GROUP BY
ShopOrder,
PartIssued,
DateIssued) AS DailyFilledOrders
RIGHT JOIN dbo.WorkOrder ON
WorkOrder.ShopOrder = DailyFilledOrders.ShopOrder
AND WorkOrder.PartNeeded = DailyFilledOrders.PartIssued
AND WorkOrder.DueQty <= DailyFilledOrders.TotalIssuedQty
AND WorkOrder.DueDate >= DailyFilledOrders.DateIssued;


--NULL DateIssued indicates missed DueDate
ShopOrder PartNeeded DueDate DateIssued
1000001 ABC 2010-05-01 2010-04-20
1000001 DEF 2010-05-01 2010-04-20
1000001 GHI 2010-05-01 2010-04-20
1000002 ABC 2010-05-01 NULL
1000002 DEF 2010-05-01 2010-04-01
1000002 GHI 2010-05-01 NULL

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Jordan" <none(a)here.com> wrote in message
news:#I6$N0d$KHA.3880(a)TK2MSFTNGP04.phx.gbl...
> We are trying to get some statistics on our Inventory people to see if
> they are issuing material to work orders on time. My work order table has
> a field that shows the last transaction date of the order, but if
> Inventory issue all the material then has to issue more because of scrap
> later they get dinged as being late.
>
> We have a Transaction History table that shows every issue however I don't
> know a good way to find out when the total number of needed parts was
> reached. For Example I am creating a shop order that needs 3 parts, 50 of
> each:
>
> ShopOrder PartNeeded Due Date Due Qty
> 1000001 ABC 5/1/2010 50
> 1000001 DEF 5/1/2010 50
> 1000001 GHI 5/1/2010 50
>
> Shop Order PartIssued DateIssued IssuedQty
> 1000001 ABC 4/1/2010 25
> 1000001 DEF 4/1/2010 25
> 1000001 GHI 4/1/2010 25
> 1000001 ABC 4/20/2010 25
> 1000001 DEF 4/20/2010 25
> 1000001 GHI 4/20/2010 25
> 1000001 GHI 5/2/2010 10
>
> In the data above you can see that all the needed parts were issued by
> 4/20 so Inventory did their job, however because of whatever reason they
> had to issue 10 extra on 5/2 a day after the due date. When I use MAX for
> the date issue it will look like Inventory was late. I need something
> that total from first to last issue and find the date when the 50 for any
> given part was reached.
>
>
 | 
Pages: 1
Prev: Conditional UNION
Next: Threading in CLR procedure