locked
Production Plan Each 20 Quantity Per Week RRS feed

  • Question

  • User-807418713 posted

    Hi

    This is my Table schema and Data scripts

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Order_Table](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Order_Date] [datetime] NULL,
    	[Order_No] [varchar](50) NULL,
    	[Customer] [varchar](50) NULL,
    	[Item_Name] [varchar](50) NULL,
    	[Order_Quantity] [float] NULL,
    	[Required_Material_Name1] [varchar](50) NULL,
    	[Required_Quantity_Name1] [float] NULL,
    	[Required_Material_Name2] [varchar](50) NULL,
    	[Required_Quantity_Name2] [float] NULL,
    	[Customer_Delivery_Date] [datetime] NULL,
    	[Customer_Delivery_Week] [int] NULL,
     CONSTRAINT [PK_Order_Table] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[Order_Table] ON
    INSERT [dbo].[Order_Table] ([ID], [Order_Date], [Order_No], [Customer], [Item_Name], [Order_Quantity], [Required_Material_Name1], [Required_Quantity_Name1], [Required_Material_Name2], [Required_Quantity_Name2], [Customer_Delivery_Date], [Customer_Delivery_Week]) VALUES (5, CAST(0x00009DA600000000 AS DateTime), N'A1', N'M1', N'Item1', 10, N'AA', 6, N'BB', 4, CAST(0x0000AA8B00000000 AS DateTime), 29)
    INSERT [dbo].[Order_Table] ([ID], [Order_Date], [Order_No], [Customer], [Item_Name], [Order_Quantity], [Required_Material_Name1], [Required_Quantity_Name1], [Required_Material_Name2], [Required_Quantity_Name2], [Customer_Delivery_Date], [Customer_Delivery_Week]) VALUES (6, CAST(0x0000AA8100000000 AS DateTime), N'A2', N'K1', N'Item2', 40, N'BB', 25, N'DD', 15, CAST(0x0000AA9400000000 AS DateTime), 30)
    INSERT [dbo].[Order_Table] ([ID], [Order_Date], [Order_No], [Customer], [Item_Name], [Order_Quantity], [Required_Material_Name1], [Required_Quantity_Name1], [Required_Material_Name2], [Required_Quantity_Name2], [Customer_Delivery_Date], [Customer_Delivery_Week]) VALUES (7, CAST(0x0000AA8500000000 AS DateTime), N'A3', N'C1', N'Item3', 15, N'AA', 4, N'KK', 11, CAST(0x0000AA8A00000000 AS DateTime), 28)
    INSERT [dbo].[Order_Table] ([ID], [Order_Date], [Order_No], [Customer], [Item_Name], [Order_Quantity], [Required_Material_Name1], [Required_Quantity_Name1], [Required_Material_Name2], [Required_Quantity_Name2], [Customer_Delivery_Date], [Customer_Delivery_Week]) VALUES (8, CAST(0x0000AA8700000000 AS DateTime), N'A4', N'D1', N'Item4', 20, N'AA', 15, N'BB', 5, CAST(0x0000AA8A00000000 AS DateTime), 28)
    SET IDENTITY_INSERT [dbo].[Order_Table] OFF
    

    This is my order Table Data

    Order Date Order No Customer  Item Name Order Quantity Required Material Name 1 Required Quantity Name 1 Required Material Name 2 Required Quantity Name 2 Customer Delivery Date Customer Delivery Week
    1-Jul-10 A1 M1 Item1 10 AA 6 BB 4 15-Jul-19 29
    5-Jul-19 A2 K1 Item2 40 BB 25 DD 15 24-Jul-19 30
    9-Jul-19 A3 C1 Item3 15 AA 4 KK 11 14-Jul-19 28
    11-Jul-19 A4 D1 Item4 20 AA 15 BB 5 14-Jul-19 28

    Per Week Capacity Is 20 Quantity, Order Date IS FIFO For Planing I Need Result set Like This below

    Week Number Order No Customer Item Name AA BB CC DD KK
    2019 - Week 26  A3 C1 Item3 4   0 0 11
    2019 - Week 26 A4 D1 Item4 5 0 0 0 0
    2019 - Week 27 A4 D1 Item4 10 5 0 0 0
    2019 - Week 27 A1 M1 Item1 5 0 0 0 0
    2019 - Week 28 A1 M1 Item1 1 4 0 0 0
    2019 - Week 28 A2 K1 Item2 0 15 0 0 0
    2019 - Week 29 A2 K1 Item2 0 10 0 10 0
    2019 - Week 29 A2 K1 Item2 0 0 0 5 0

    How Would Be SQl Query In Pivot Mode

    Thank You

    Thursday, July 18, 2019 6:16 PM

Answers

  • User77042963 posted

    I suggest you to redesign your table structure before your struggle with hard queries.

    It does not serve you well if you spend too much time on a bad design. You may get a working query but it will fall parts if you change slightly down the road.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 18, 2019 8:14 PM

All replies

  • User77042963 posted

    I suggest you to redesign your table structure before your struggle with hard queries.

    It does not serve you well if you spend too much time on a bad design. You may get a working query but it will fall parts if you change slightly down the road.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 18, 2019 8:14 PM
  • User-807418713 posted
    Hello

    Can you give me your own sample example with my above order table data.

    Thank You
    Friday, July 19, 2019 5:25 AM