get planing from order data in ms sql

Question

get planing from order data in ms sql

RAVI 1,076

This is my customer order table

User's image

Our Production Capacity Is Per Day 80 QUANITY Each Machine Capacity Is 20 Quantity

This is my machine breakdown table

User's image

If machine failure it should plan for next available machine

I want output like this

User's image

need sql query

Erland Sommarskog 132.3K Reputation points MVP Volunteer Moderator

2023-12-27T22:37:10.94+00:00

For this type of question, you help yourself enormously if you post CREATE TABLE statements for your tables and INSERT statements for the sample data. You also help yourself a lot if you explain the logic to get the expected results. From where do these MACHINE 1 etc come from? What do the values in the TOTAL column from? From where does 25-Dec come from? Etc.

No one can write a query for you, if we don't know your business logic.

RAVI 1,076

Hello

Here Is My Table With Data

USE [master]
GO
/****** Object:  Table [dbo].[ORDER_TABLE]    Script Date: 12/28/2023 23:05:26 ******/
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_NO] [varchar](150) NULL,
	[ITEM_NAME] [varchar](150) NULL,
	[Order_Date] [datetime] NULL,
	[Ordder_Quantity] [float] NULL,
	[Order_Priority] [int] NULL,
	[Start_Plan_Date] [datetime] 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_NO], [ITEM_NAME], [Order_Date], [Ordder_Quantity], [Order_Priority], [Start_Plan_Date]) VALUES (1, N'M001', N'L SOAP', CAST(0x0000B0DF00000000 AS DateTime), 100, 1, CAST(0x0000B0E000000000 AS DateTime))
INSERT [dbo].[ORDER_TABLE] ([ID], [ORDER_NO], [ITEM_NAME], [Order_Date], [Ordder_Quantity], [Order_Priority], [Start_Plan_Date]) VALUES (2, N'K01', N'M SOAP', CAST(0x0000B0E000000000 AS DateTime), 120, 2, CAST(0x0000B0E000000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[ORDER_TABLE] OFF
/****** Object:  Table [dbo].[MACHINE_BREAK_TABLE]    Script Date: 12/28/2023 23:05:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MACHINE_BREAK_TABLE](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Date] [datetime] NULL,
	[MACHINER_NAME] [varchar](50) NULL,
	[Reason] [varchar](50) NULL,
 CONSTRAINT [PK_MACHINE_BREAK_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].[MACHINE_BREAK_TABLE] ON
INSERT [dbo].[MACHINE_BREAK_TABLE] ([ID], [Date], [MACHINER_NAME], [Reason]) VALUES (1, CAST(0x0000B0E100000000 AS DateTime), N'MACHINE 3', N'POWER FAILURE')
SET IDENTITY_INSERT [dbo].[MACHINE_BREAK_TABLE] OFF

I need output like this

User's image

example of M001-LSOAP-20 As M001 is Orderno and LSAP is Itemname, 20 is machine capcity

Erland Sommarskog 132.3K Reputation points MVP Volunteer Moderator

2023-12-28T22:15:42.2766667+00:00

I repeat what I said earlier:

You also help yourself a lot if you explain the logic to get the expected results. From where do these MACHINE 1 etc come from? What do the values in the TOTAL column from? From where does 25-Dec come from? Etc. No one can write a query for you, if we don't know your business logic.

We know your data, we know what result you want. But we have no idea how to get to the data to the result. You need to explain it in English so we can translate it to SQL.
Viorel 126.3K Reputation points

2023-12-29T16:11:24.3333333+00:00

Maybe it will be difficult to implement the hypothetical logic using a query. Perhaps a longer script is required (using loops, cursors, temporary tables). Or maybe the distribution can be done easier in other programming languages.

1 answer

Your answer

Erland Sommarskog 132.3K Reputation points MVP Volunteer Moderator

2023-12-27T22:37:10.94+00:00

For this type of question, you help yourself enormously if you post CREATE TABLE statements for your tables and INSERT statements for the sample data. You also help yourself a lot if you explain the logic to get the expected results. From where do these MACHINE 1 etc come from? What do the values in the TOTAL column from? From where does 25-Dec come from? Etc.

No one can write a query for you, if we don't know your business logic.
Erland Sommarskog 132.3K Reputation points MVP Volunteer Moderator

2023-12-28T22:15:42.2766667+00:00

I repeat what I said earlier:

You also help yourself a lot if you explain the logic to get the expected results. From where do these MACHINE 1 etc come from? What do the values in the TOTAL column from? From where does 25-Dec come from? Etc. No one can write a query for you, if we don't know your business logic.

We know your data, we know what result you want. But we have no idea how to get to the data to the result. You need to explain it in English so we can translate it to SQL.
Viorel 126.3K Reputation points

2023-12-29T16:11:24.3333333+00:00

Maybe it will be difficult to implement the hypothetical logic using a query. Perhaps a longer script is required (using loops, cursors, temporary tables). Or maybe the distribution can be done easier in other programming languages.

Answer 1

Olaf Helper 47,611

This is my customer order table

No, that's an image, I can't query images and I don't have your database.

Please post table design as DDL, some sample data as DML statement and the expected result.

Share via

get planing from order data in ms sql

1 answer

Your answer