Menu
I have the following two tables:
DueDates:
DECLARE @DueDates TABLE (
uniqueln varchar(10),
ship_dts smalldatetime,
qty decimal(18,4))
INSERT INTO @DueDates
SELECT '51351621AS','1/1/2013',7
UNION ALL
SELECT '51351621AS','1/7/2013',7
UNION ALL
SELECT '51351621AS','1/14/2013',7
UNION ALL
SELECT '51351621AS','1/21/2013',7
UNION ALL
SELECT '51351621AS','1/28/2013',7
UNION ALL
SELECT 'V4351621AS','1/5/2013',10
UNION ALL
SELECT 'V4351621AS','1/10/2013',10
UNION ALL
SELECT 'V4351621AS','1/15/2013',10
UNION ALL
SELECT 'V4351621AS','1/20/2013',10
UNION ALL
SELECT 'V4351621AS','1/25/2013',10
PlDetail
DECLARE @PlDetail TABLE (
uniqueln varchar(10),
shipdate smalldatetime,
shippedqty decimal(18,4))
INSERT INTO @PlDetail
SELECT '51351621AS','1/1/2013',10
UNION ALL
SELECT '51351621AS','1/7/2013',10
UNION ALL
SELECT '51351621AS','1/14/2013',10
UNION ALL
SELECT '51351621AS','1/21/2013',5
UNION ALL
SELECT 'V4351621AS','1/5/2013',13
UNION ALL
SELECT 'V4351621AS','1/15/2013',9
UNION ALL
SELECT 'V4351621AS','1/25/2013',12
UNION ALL
SELECT 'V4351621AS','1/30/2013',10
UNION ALL
SELECT 'V4351621AS','2/5/2013',6
The shipments in PlDetail can be one to one with the orders in the DueDates table, but often they are not.
I am trying to calculate an on-time delivery for each uniqueln schedule using a FIFO method (I cannot change how the data is stored in the tables). Basically, I want to apply the earliest shipments to the earliest deliveries.
If a shipment qty exceeds the balance in a DueDates record, it should have the balance applied to the next scheduled delivery.
The end result should look something like this:
uniqueln ship_dts qty shipdate shippedqty daysLate
51351621AS 1/1/2013 7 1/1/2013 7 0
51351621AS 1/7/2013 7 1/1/2013 3 -6
51351621AS 1/7/2013 7 1/7/2013 4 0
51351621AS 1/14/2013 7 1/7/2013 6 -7
51351621AS 1/14/2013 7 1/14/2013 1 0
51351621AS 1/21/2013 7 1/14/2013 7 -7
51351621AS 1/28/2013 7 1/14/2013 2 -14
51351621AS 1/28/2013 7 1/21/2013 5 -7
V4351621AS 1/5/2013 10 1/5/2013 10 0
V4351621AS 1/10/2013 10 1/5/2013 3 -5
V4351621AS 1/10/2013 10 1/15/2013 7 5
V4351621AS 1/15/2013 10 1/15/2013 2 0
V4351621AS 1/15/2013 10 1/25/2013 8 10
V4351621AS 1/20/2013 10 1/25/2013 4 5
V4351621AS 1/20/2013 10 1/30/2013 6 10
V4351621AS 1/25/2013 10 1/30/2013 4 5
V4351621AS 1/25/2013 10 2/5/2013 6 11
I know how to group the PlDetail shipments strictly by date so any shipment that falls on or before the next due date is grouped together, but it HAS to factor in the scheduled qty vs the shipped qty.
I don't want to create a cursor and cycle through the shipment records, but I can if this type of join won't work. However, I believe it is possible, but I am not sure how to group or join the tables.
It sounds like SQL Server 2012 has some new methods that will make this easier, but right now I am using SQL SERVER 2008 R2 and have to keep it that way for the near future.
What is the best way to approach this? Is a cursor really the only way?
UPDATE: This is what I have added so far. The end result is a table showing the from and to qty and ship_dts for each uniqueln
WITH DSeq AS (
SELECT TOP 100 PERCENT
Seq = Row_Number() OVER (partition by uniqueln ORDER BY ship_dts),
D.UNIQUELN,
D.SHIP_DTS,
SchBal = (SELECT TOP 100 PERCENT SUM(B.Qty) FROM @DueDates B WHERE b.SHIP_DTS<= D.SHIP_DTS AND b.UNIQUELN=d.UNIQUELN ORDER BY d.SHIP_DTS)
FROM @DueDates D
GROUP BY UNIQUELN,D.QTY,D.UNIQUELN,D.SHIP_DTS
ORDER BY D.UNIQUELN, D.SHIP_DTS
)
--SELECT * FROM DSeq
, Slices AS (
SELECT
LN = D.UNIQUELN,
FromQty = COALESCE(N.SchBal,0),
ToQty = D.SchBal,
D.SHIP_DTS
FROM
DSeq D
LEFT OUTER JOIN DSeq N
ON D.Seq -1 = N.Seq AND D.UNIQUELN=N.UNIQUELN
)
SELECT * FROM Slices
CURSOR APPROACH:
As has been stated, the best approach might be a cursor. Hopefully someone has a join method that will meet all the needs, but until then we are using a cursor.
In case someone is looking for a solution to this with a cursor approach, the code below is how we have done it. The user selects a date range and it produces the results. Please note that you HAVE to run the cursor for ALL records of the uniqueln even if they shipped prior to the selected date range, otherwise the FIFO application of shipments will be wrong.
DECLARE @startdate smalldatetime, @endDate smalldatetime
DECLARE @OnTime TABLE (Uniqueln varchar(10),DueQty int,dueDate smalldatetime,shipDate smalldatetime,shipQty int DEFAULT 0,daysLate int,balQty int)
DECLARE @uniqln1 varchar(10),@toQty int, @dueDate smalldatetime,@bQty int
DECLARE @uniqln2 varchar(10),@shipQty int, @shipDate smalldatetime
DECLARE ot_cursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT uniqueln,qty,ship_dts,qty
FROM @DueDates
ORDER BY uniqueln,ship_dts
OPEN ot_cursor;
FETCH NEXT FROM ot_cursor INTO @uniqln1,@toQty,@dueDate,@bQty
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE s_cursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT Uniqueln,shippedqty,shipdate
FROM @PlDetail p
WHERE uniqueln = @uniqln1
ORDER BY 3
OPEN s_cursor ;
FETCH NEXT FROM s_cursor INTO @uniqln2,@shipQty,@shipDate
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @OnTime(Uniqueln,DueQty,dueDate,shipDate,shipQty,daysLate,balQty)
SELECT @uniqln1,@toQty,@dueDate,@shipDate,CASE WHEN @bQty>@shipQty THEN @shipQty ELSE @bQty END,DATEDIFF(d,@dueDate,@shipDate),CASE WHEN @bQty>@shipQty THEN @bQty-@shipQty ELSE 0 END
SET @bQty=@bQty-@shipQty
IF @bQty < 0
BEGIN
SET @shipQty = -@bQty
FETCH NEXT FROM ot_cursor INTO @uniqln1,@toQty,@dueDate,@bQty
END
ELSE
IF @bQty =0
BEGIN
BREAK
END
ELSE
BEGIN
SET @shipQty = @bQty
FETCH NEXT FROM s_cursor INTO @uniqln2,@shipQty,@shipDate
END
END
CLOSE s_cursor
DEALLOCATE s_cursor
FETCH NEXT FROM ot_cursor INTO @uniqln1,@toQty,@dueDate,@bQty
END
CLOSE ot_cursor
DEALLOCATE ot_cursor
SELECT * FROM @OnTime
WHERE shipDate BETWEEN @startdate AND @endDate
ORDER BY Uniqueln,dueDate
693
Tried putting something together using CTEs and a pidgin cross join between the tables. (don't ask, it was ugly)
Anywho, after a bit of reading, and knowing how large the tables are, I feel pretty safe answering this with... drumroll... use a cursor. It'll be ugly and slow, but the logic will make much more sense on paper. There's a lot to be said about maintainable code...
Update: Going on vacation. Here's what I was playing with.
DECLARE @DueDates TABLE (
uniqueln varchar(10),
ship_dts smalldatetime,
qty decimal(18,4))
INSERT INTO @DueDates
SELECT '51351621AS','1/1/2013',7
UNION ALL
SELECT '51351621AS','1/7/2013',7
UNION ALL
SELECT '51351621AS','1/14/2013',7
UNION ALL
SELECT '51351621AS','1/21/2013',7
UNION ALL
SELECT '51351621AS','1/28/2013',7
UNION ALL
SELECT 'V4351621AS','1/5/2013',10
UNION ALL
SELECT 'V4351621AS','1/10/2013',10
UNION ALL
SELECT 'V4351621AS','1/15/2013',10
UNION ALL
SELECT 'V4351621AS','1/20/2013',10
UNION ALL
SELECT 'V4351621AS','1/25/2013',10
DECLARE @PlDetail TABLE (
uniqueln varchar(10),
shipdate smalldatetime,
shippedqty decimal(18,4))
INSERT INTO @PlDetail
SELECT '51351621AS','1/1/2013',10
UNION ALL
SELECT '51351621AS','1/7/2013',10
UNION ALL
SELECT '51351621AS','1/14/2013',10
UNION ALL
SELECT '51351621AS','1/21/2013',5
UNION ALL
SELECT 'V4351621AS','1/5/2013',13
UNION ALL
SELECT 'V4351621AS','1/15/2013',9
UNION ALL
SELECT 'V4351621AS','1/25/2013',12
UNION ALL
SELECT 'V4351621AS','1/30/2013',10
UNION ALL
SELECT 'V4351621AS','2/5/2013',6
; WITH DueDates AS
(
SELECT b.*
FROM @DueDates a
JOIN @DueDates b
ON a.uniqueln = b.uniqueln
AND b.ship_dts >= a.ship_dts
)
, PlDetail AS
(
SELECT b.*
FROM @PlDetail a
JOIN @PlDetail b
ON a.uniqueln = b.uniqueln
AND b.shipdate >= a.shipdate
)
SELECT a.uniqueln
, SUM(a.qty) AS ordered_running_total
, SUM(b.shippedqty) AS shipped_running_total
, a.ship_dts
, b.shipdate
, SUM(b.shippedqty) - SUM(a.qty) AS leftover_running_total
FROM DueDates a
JOIN PlDetail b
ON a.uniqueln = b.uniqueln
AND a.ship_dts >= b.shipdate
GROUP BY a.uniqueln, a.ship_dts, b.shipdate
HAVING SUM(a.qty) <= SUM(b.shippedqty)
ORDER BY a.uniqueln, a.ship_dts, b.shipdate
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
