Update Quantities over multiple records

Question

Post reply

Update Quantities over multiple records

skilly2

Right there with Babe

Points: 761
More actions
March 25, 2015 at 12:30 pm

#398244

I have two tables that can be created with sample data using the DDL at the bottom of this post. What I'm looking to do is update the QtyReceived column in tblPurchaseOrderLineDetail from the Qty column in tblReceivedItems. However, the tricky part that I can't figure out is splitting these quantities out over multiple lines. I should only be allowed to receive up to the QtyOrdered column in tblPurchaseOrderLineDetail. For a specific example from the sample data we'll look at PurchaseOrderDetailID 28526. From the tblReceivedItems, there are three records with quantities of 48, 48, and 20. From the tblPurchaseOrderLineDetail there are three records of QtyOrdered of 55, 45, and 20. What I would like to happen is fulfill the records in the tblPurchaseOrderLineDetail sequentially (essentially in order of ExpectedDate). So, the QtyReceived would be 55, 45, and 16 for the corresponding records. If there is already a quantity in the QtyReceived column, but it's less than the QtyOrdered column, the quantity needs to be added to the column (not overwritten). I'm not really sure how to approach this.
Any help is appreciated. Hopefully I explained the problem well enough to understand.
DDL To CREATE Sample Tables and Data:
CREATE TABLE [dbo].[tblReceivedItems](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PurchaseOrderDetailID] [int] NULL,
[Qty] [int] NULL)
SET IDENTITY_INSERT [dbo].[tblReceivedItems] ON
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (1, 28191, 48)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (2, 28191, 48)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (3, 28194, 48)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (4, 28196, 48)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (5, 28197, 48)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (6, 28197, 48)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (7, 28250, 1584)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (8, 28251, 1584)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (9, 28252, 1584)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (10, 28520, 48)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (11, 28524, 48)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (12, 28524, 48)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (13, 28526, 48)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (14, 28526, 48)
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (15, 28526, 20)
SET IDENTITY_INSERT [dbo].[tblReceivedItems] OFF
CREATE TABLE [dbo].[tblPurchaseOrderLineDetail](
[PurchaseOrderLineDetailID] [int] IDENTITY(1,1) NOT NULL,
[PurchaseOrderDetailID] [int] NOT NULL,
[QtyOrdered] [int] NOT NULL,
[QtyReceived] [int] NOT NULL CONSTRAINT [DF_tblPurchaseOrderLineDetail_QtyReceived] DEFAULT ((0)),
[ExpectedDate] [datetime] NOT NULL,
[LineStatus] [tinyint] NOT NULL CONSTRAINT [DF_tblPurchaseOrderLineDetail_LineStatus] DEFAULT ((0)))
SET IDENTITY_INSERT [dbo].[tblPurchaseOrderLineDetail] ON
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20384, 28191, 480, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20387, 28194, 192, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20389, 28196, 48, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20390, 28197, 480, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20392, 28250, 1584, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20393, 28251, 1584, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20394, 28252, 1584, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20608, 28520, 288, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20614, 28524, 96, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20616, 28526, 55, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20617, 28526, 45, 0, CAST(N'2015-03-19 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20618, 28526, 20, 0, CAST(N'2015-04-20 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20619, 28528, 15, 0, CAST(N'2015-03-19 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20620, 28528, 33, 0, CAST(N'2015-04-20 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20621, 28529, 195, 0, CAST(N'2015-04-07 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20622, 28530, 288, 0, CAST(N'2015-04-13 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20623, 28531, 144, 0, CAST(N'2015-03-19 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20637, 28548, 6000, 0, CAST(N'2015-02-02 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20641, 28551, 40000, 0, CAST(N'2015-02-02 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20642, 28552, 6000, 0, CAST(N'2015-02-02 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20725, 28638, 40000, 0, CAST(N'2015-03-02 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20726, 28638, 40000, 0, CAST(N'2015-03-23 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20735, 28652, 40000, 0, CAST(N'2015-02-23 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20797, 28709, 7500, 0, CAST(N'2015-03-19 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20798, 28709, 46665, 0, CAST(N'2015-04-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20800, 28718, 6000, 0, CAST(N'2015-03-09 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20875, 28806, 10000, 0, CAST(N'2015-03-23 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20892, 28826, 378, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20893, 28829, 50, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20894, 28828, 1512, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20895, 28827, 882, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20896, 28830, 262, 0, CAST(N'2015-05-04 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20897, 28831, 262, 0, CAST(N'2015-05-04 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20898, 28832, 262, 0, CAST(N'2015-05-04 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20918, 28849, 216, 0, CAST(N'2015-03-18 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (20919, 28849, 360, 0, CAST(N'2015-03-31 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21043, 28996, 12000, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21071, 29035, 100, 0, CAST(N'2015-03-09 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21072, 29034, 80, 0, CAST(N'2015-03-09 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21092, 29062, 20000, 0, CAST(N'2015-03-30 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21097, 29069, 20000, 0, CAST(N'2015-03-23 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21098, 29068, 20000, 0, CAST(N'2015-03-23 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21100, 29072, 2000, 0, CAST(N'2015-03-23 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21110, 29085, 1, 0, CAST(N'2015-03-04 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21111, 29088, 1500, 0, CAST(N'2015-03-04 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21112, 29089, 1000, 0, CAST(N'2015-03-04 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21113, 29087, 2000, 0, CAST(N'2015-03-04 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21114, 29086, 1500, 0, CAST(N'2015-03-04 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21115, 29090, 2000, 0, CAST(N'2015-03-04 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21116, 29091, 400, 0, CAST(N'2015-03-23 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21117, 29094, 1536, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21152, 29161, 20000, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21158, 29168, 72, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21159, 29170, 10000, 0, CAST(N'2015-03-23 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21189, 29201, 20000, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21190, 29202, 2160, 0, CAST(N'2015-03-23 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21191, 29203, 2160, 0, CAST(N'2015-03-23 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21214, 29232, 3000, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21215, 29230, 2625, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21216, 29229, 1500, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21217, 29227, 1000, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21218, 29228, 1500, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21219, 29231, 1000, 0, CAST(N'2015-03-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21222, 29235, 10000, 0, CAST(N'2015-03-18 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21223, 29239, 4000, 0, CAST(N'2015-03-18 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21250, 29263, 1500, 0, CAST(N'2015-03-30 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21261, 29279, 900, 0, CAST(N'2015-03-18 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21262, 29280, 1000, 0, CAST(N'2015-03-18 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21263, 29278, 3000, 0, CAST(N'2015-03-18 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21264, 29281, 3000, 0, CAST(N'2015-03-17 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21267, 29284, 12000, 0, CAST(N'2015-03-24 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21269, 29287, 500, 0, CAST(N'2015-03-24 00:00:00.000' AS DateTime), 0)
INSERT [dbo].[tblPurchaseOrderLineDetail] ([PurchaseOrderLineDetailID], [PurchaseOrderDetailID], [QtyOrdered], [QtyReceived], [ExpectedDate], [LineStatus]) VALUES (21270, 29286, 1200, 0, CAST(N'2015-03-24 00:00:00.000' AS DateTime), 0)
SET IDENTITY_INSERT [dbo].[tblPurchaseOrderLineDetail] OFF

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

Man, this looks like deja vu all over again!

http://www.sqlservercentral.com/Forums/Topic1671044-3412-1.aspx?Update=1]

Never let it be said I can't learn from the master (that being Itzik Ben-Gan):

WITH RcdItems AS

(

SELECT *,

rt=SUM(Qty) OVER (PARTITION BY PurchaseOrderDetailID ORDER BY ID ROWS UNBOUNDED PRECEDING)

FROM dbo.tblReceivedItems

--WHERE PurchaseOrderDetailID = 28191

),

POLD AS

(

SELECT *,

rt=SUM(QtyOrdered) OVER (PARTITION BY PurchaseOrderDetailID ORDER BY PurchaseOrderDetailID ROWS UNBOUNDED PRECEDING)

FROM dbo.tblPurchaseOrderLineDetail

--WHERE PurchaseOrderDetailID = 28191

)

UPDATE a

SET QtyReceived = CASE

WHEN b.rt >= a.rt THEN a.QtyOrdered

WHEN a.rt - a.QtyOrdered > b.rt THEN b.rt - (a.rt - a.QtyOrdered)

WHEN a.rt > b.rt THEN b.rt

ELSE 0

END

FROM POLD a

OUTER APPLY

(

SELECT TOP (1) *

FROM RcdItems b

WHERE b.PurchaseOrderDetailID = a.PurchaseOrderDetailID

ORDER BY b.ID DESC

) b;

See if that doesn't do it for you.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

skilly2 Right there with Babe Points: 761 More actions · Answer 2

This worked perfectly! I'm still trying to wrap my head around it though. I understand what the Over (Partition statement does normally, but I've never used the Rows Unbounded PrecedIing clause. That and the Outer Apply are what's confusing me. I'm working through documentation to see if I can understand those bits, but your solution does exactly what I needed.

Thanks!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

skilly2 (3/26/2015)
This worked perfectly! I'm still trying to wrap my head around it though. I understand what the Over (Partition statement does normally, but I've never used the Rows Unbounded PrecedIing clause. That and the Outer Apply are what's confusing me. I'm working through documentation to see if I can understand those bits, but your solution does exactly what I needed.
Thanks!

Don't thank me! Thank Itzik Ben-Gan, although I do confess to having to slightly tweak the solution he posted on Stack Overflow to that other guy's problem.

He's pretty brilliant, while I'm just a tag-along. But at least I was able to recognize the similar pattern.

Some good references on using APPLY:

http://www.sqlservercentral.com/articles/APPLY/69953/url]

http://www.sqlservercentral.com/articles/APPLY/69954/

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

I'm going to recommend incredible caution here. Even Itzik said it wasn't optimized in his original post.

Internally, this as bad as a double cross join because it generates 1005 rows from the tblReceivedItems (15 items extracted 67 times), sorts those items 67 times, spools out 2010 items into an aggregate that's executed 67 times, and spits out the original 15 rows to yet another sort that get's executed 67 time, and... you get the idea.

I've not done a deep dive on the required indexing for this method (been too busy) but it would appear that they'd need to be relatively wide perhaps fully covering indexes.

Just for this relatively small bit of data, the code uses 686 logical reads and we're talking about a total of less than 100 rows combined in both tables.

I believe that some "Divide'n'Conquer" is in order here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

Divide and conquer is what I did in the deja vu link, so if Jeff's right (and he usually is), it might work better for you.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St