Inventory Management

[wbodger]

Given this dataset (ok, data that looks like this, but much more of it)

Fabric P/N	JOB_DELIVERY_DATE	REMAINING_QTY	QTY_ON_HAND
1C3264-1E_D                   	3/23/2016	4.00	15
1C3264-1E_D                   	4/2/2016	0.00	15
1C3264-1E_D                   	4/3/2016	1.00	15
1C3264-1E_D                   	4/3/2016	1.00	15
1C3264-1E_D                   	4/5/2016	2.00	15
1C3264-1E_D                   	4/9/2016	1.00	15
1C3264-1E_D                   	4/11/2016	2.00	15
1C3264-1E_D                   	4/13/2016	4.00	15
1C3264-1E_D                   	4/17/2016	3.00	15
1C3264-1E_D                   	4/20/2016	4.00	15
1C3264-1E_D                   	5/11/2016	2.00	15
1C6184-4E_NC                  	3/23/2016	4.00	13
1C6184-4E_NC                  	4/3/2016	1.00	13
1C6184-4E_NC                  	4/13/2016	4.00	13
1C6184-4E_NC                  	4/17/2016	3.00	13
1C6184-4E_NC                  	4/20/2016	4.00	13
1C6185-3E_NC                  	3/23/2016	4.00	14
1C6185-3E_NC                  	4/3/2016	1.00	14
1C6185-3E_NC                  	4/13/2016	4.00	14
1C6185-3E_NC                  	4/17/2016	3.00	14
1C6185-3E_NC                  	4/20/2016	4.00	14
1C6186-1_NC                   	4/7/2016	6.00	6
1C6186-1_NC                   	4/7/2016	0.00	6
1C6186-1_NC                   	4/29/2016	4.00	6
2E5022-15_NC                  	4/4/2016	1.00	0
2E5022-15_NC                  	4/8/2016	2.00	0
2E5022-15_NC                  	4/13/2016	1.00	0
2E5022-15_NC                  	4/20/2016	1.00	0
2E5022-17_NC                  	4/5/2016	1.00	0
2E5022-17_NC                  	4/6/2016	1.00	0
2E5022-17_NC                  	4/8/2016	1.00	0
2E5022-17_NC                  	4/12/2016	1.00	0
2E5022-17_NC                  	4/20/2016	3.00	0
2E5022-17_NC                  	4/20/2016	1.00	0
2E5022-17_NC                  	4/22/2016	1.00	0
2E5022-17_NC                  	5/6/2016	3.00	0
2E5022-17_NC                  	5/6/2016	2.00	0
2E5022-17_NC                  	5/6/2016	1.00	0
2E5022-17_NC                  	5/6/2016	1.00	0
2E5022-7_B                    	4/20/2016	1.00	0
2E5022-7_B                    	4/21/2016	1.00	0
2E5022-7_B                    	5/6/2016	1.00	0
2E5022-8_A                    	4/21/2016	1.00	0
2E5022-8_B                    	5/5/2016	16.00	11
2E5026-3_A                    	6/30/2016	30.00	0
2E5026-3_B                    	4/15/2016	32.00	17
2E5026-5_A                    	4/8/2016	0.00	0
2E5026-5_A                    	4/20/2016	4.00	0
2E5026-5_A                    	4/20/2016	2.00	0

I need to come up with a report that tells me on which day I will run out of my current stock. This seems like it shouldn't be a problem, but I cannot seem to get this one right. For instance Fabric P/N 1C6234-1E_D I will not have enough stock for the Job that is due on 4/17/2016. I have tried various things and none of them have worked, so I am ready to start over. This data is from a view that I created, but I did not include a running total.

Anybody have any suggestions? I am stumped.
Thanks,
Willie

 

[wbodger]

I keep ending up with code like this (not pretty, I know)

; with cte as
(
SELECT TOP 100 percent T1.[Fabric P/N], T1.JOB_DELIVERY_DATE, T1.REMAINING_QTY, T1.QTY_ON_HAND, RANK() OVER(PARTITION BY T1.[Fabric P/N] ORDER BY [Fabric P/N],JOB_DELIVERY_DATE) as rnk,
(SELECT SUM(REMAINING_QTY)
FROM [OnLineStatus].[dbo].[view_NegFutureFree] T2
WHERE T1.[Fabric P/N]=T2.[Fabric P/N] and T2.ROWORDER <= T1.ROWORDER) AS RunningTotal
FROM [OnLineStatus].[dbo].[view_NegFutureFree] T1
WHERE T1.SUPPLIER='PATT02'
ORDER BY T1.[Fabric P/N], T1.JOB_DELIVERY_DATE
)

select distinct [Fabric P/N], min(JOB_DELIVERY_DATE) as JOB_DELIVERY_DATE,REMAINING_QTY,QTY_ON_HAND,min(rnk)as rnk,RunningTotal 
from cte where RunningTotal>QTY_ON_HAND
group by [Fabric P/N], REMAINING_QTY,QTY_ON_HAND,RunningTotal
order by [Fabric P/N],JOB_DELIVERY_DATE

Which gives me everything starting at the time we run out going forward, but I really want just where we run out...

 

[Olaf Doschke]

Well, what are rows of interest, now? Where RunningTotal becomes higher than QTY_ON_HAND?

Why don't you compute a running total as QTY_ON_HAND-SUM(REMAINING_QTY) and check for it becoming lower than zero?

Bye, Olaf.

 

[Olaf Doschke]

Well, as you want one record per [Fabric P/N], only group by [Fabric P/N] and aggregate everything else.
QTY_ON_HAND is constant, anyway, isn't it? Then min(QTY_ON_HAND) helps you keep it out of the grouping.
To lookup what REMAINING_QTY is needed at that date is the job of a further query, it'll not give you an insight of what the best reorder is anyway, in lieu of next dates, so that's simply removed. I instead added min(RunningTotal-QTY_ON_HAND)as MissingOnThatDate. In lieu of quantity needed on further dates a larger reorder might still be more appropriate.

Finally, in regard of the running total, the minimum of it higher than QTY_ON_HAND is what interests you, so select min(RunningTotal)...WHERE RunningTotal>QTY_ON_HAND:

select [Fabric P/N], min(JOB_DELIVERY_DATE) as FIRST_FAILING_JOB_DELIVERY_DATE, 
min(QTY_ON_HAND) as QTY_ON_HAND, min(rnk) as rnk, min(RunningTotal) as RunningTotal,
min(RunningTotal-QTY_ON_HAND) as MissingOnThatDate
from cte where RunningTotal>QTY_ON_HAND
group by [Fabric P/N]
order by [Fabric P/N]

Bye, Olaf.

PS: In the end rather the last row per [Fabric P/N] of the result you have at hand will tell you the qty needed to fullfill all currently known JOBs. So when you need to reorder parts or produce them, that's rather an important figure than the date at which you run out of stock first and what is missing at that date. Just my personal view.

Bye, Olaf.

 

[wbodger]

Perfect, thank you! I was grouping by too many fields.

wb

 

[Olaf Doschke]

You're welcome.

I'm unsure, as I haven't put your sample data into a sample table for testing, min(RunningTotal-QTY_ON_HAND) might be wrong and differing from min(RunningTotal)-min(QTY_ON_HAND).

Maybe more helpful: You have new ways of computing running totals now:

SELECT [Fabric P/N], JOB_DELILVERY_DATE, REMAINING_QTY, 
SUM(REMAINING_QTY) OVER 
   (PARTITION BY [Fabric P/N] ORDER BY JOB_DELILVERY_DATE 
    [b]ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW[/b]) As RunningTotal
FROM [OnLineStatus].[dbo].[view_NegFutureFree]

Bye, Olaf.

PS: Needs SQL2012 or higher.

 

[wbodger]

Interestingly those both give the same values for my dataset.

wb

 

[Olaf Doschke]

Yes, that's the intention. This way to sum a running total is much faster, though. It doesn't need a tricky self join.
See

http://sqlperformance.com/2012/07/t-sql-queries/running-totals

Bye, Olaf.