Remove From My Forums

priority wise show data

Question

User-807418713 posted

hello

this is order table data & stock table data

USE [master]
GO
/****** Object:  Table [dbo].[stock]    Script Date: 09/17/2020 13:07:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[stock](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[belongs] [varchar](50) NULL,
	[available_qty] [float] NULL,
	[order_cancel_qty] [float] NULL,
 CONSTRAINT [PK_stock] 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].[stock] ON
INSERT [dbo].[stock] ([id], [belongs], [available_qty], [order_cancel_qty]) VALUES (5, N'A', 50, 0)
INSERT [dbo].[stock] ([id], [belongs], [available_qty], [order_cancel_qty]) VALUES (6, N'B', 0, 0)
INSERT [dbo].[stock] ([id], [belongs], [available_qty], [order_cancel_qty]) VALUES (7, N'C', 20, 0)
INSERT [dbo].[stock] ([id], [belongs], [available_qty], [order_cancel_qty]) VALUES (8, N'II', 500, 0)
SET IDENTITY_INSERT [dbo].[stock] OFF
/****** Object:  Table [dbo].[order_deatils]    Script Date: 09/17/2020 13:07:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[order_deatils](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[order_date] [datetime] NULL,
	[order_no] [varchar](50) NULL,
	[customer_code] [varchar](50) NULL,
	[article] [varchar](50) NULL,
	[color] [varchar](50) NULL,
	[belongs] [varchar](50) NULL,
	[selection] [varchar](50) NULL,
	[thickness] [varchar](50) NULL,
	[quantity] [float] NULL,
	[unit] [varchar](50) NULL,
	[priority] [float] NULL,
 CONSTRAINT [PK_order_deatils] 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_deatils] ON
INSERT [dbo].[order_deatils] ([ID], [order_date], [order_no], [customer_code], [article], [color], [belongs], [selection], [thickness], [quantity], [unit], [priority]) VALUES (25, CAST(0x0000AC0A00000000 AS DateTime), N'5', N'YY', N'Article 6', N'GREEN', N'WB', N'A', N'0.6-0.9', 500, N'LKG', 1)
INSERT [dbo].[order_deatils] ([ID], [order_date], [order_no], [customer_code], [article], [color], [belongs], [selection], [thickness], [quantity], [unit], [priority]) VALUES (26, CAST(0x0000AC0B00000000 AS DateTime), N'1', N'MM', N'Article 1', N'RED', N'WB', N'B', N'0.5-0.6', 5000, N'LKG', 5)
INSERT [dbo].[order_deatils] ([ID], [order_date], [order_no], [customer_code], [article], [color], [belongs], [selection], [thickness], [quantity], [unit], [priority]) VALUES (27, CAST(0x0000AC0B00000000 AS DateTime), N'1', N'MM', N'Article 7', N'BLUE', N'WB', N'B', N'0.5-0.6', 3000, N'LKG', 4)
INSERT [dbo].[order_deatils] ([ID], [order_date], [order_no], [customer_code], [article], [color], [belongs], [selection], [thickness], [quantity], [unit], [priority]) VALUES (28, CAST(0x0000AC0C00000000 AS DateTime), N'2', N'NN', N'Article 3', N'BLACK', N'FC', N'II', N'0.9-1.0', 2000, N'LKG', 3)
INSERT [dbo].[order_deatils] ([ID], [order_date], [order_no], [customer_code], [article], [color], [belongs], [selection], [thickness], [quantity], [unit], [priority]) VALUES (29, CAST(0x0000AC0C00000000 AS DateTime), N'2', N'NN', N'Article 4', N'BROWN', N'WB', N'A', N'0.7-0.9', 6000, N'LKG', 2)
SET IDENTITY_INSERT [dbo].[order_deatils] OFF

i want output based on priority like this below

LEATHER PREQUIRMENT DETAIL
PRIORITY	ORDER NO	CUSTOMER ARTICLE	CUSTOMER COLOR	BELONGS	Selection	THICKNESS	ORDER QUANTITY	UNIT	STOCK QTY	BALANCE REQUIRED QUANTITY
01,02	05,02	ARTICLE 6, ARTICLE 4	GREEN, BROWN	WB	A	0.6-0.9 & 0.7-0.9	6500	LKG	50	6000
03	O2	ARTICLE 3	BLACK	FC	II	0.9-1.0	2000	LKG	500	1500
04,05	02	ARTCILE 7, ARTCIEL 1	BLUE, RED	WB	B	0.5-0.6	8000	LKG	0	8000

Thursday, September 17, 2020 7:35 AM

Answers

User-939850651 posted

Hi Gopi.MCA,

According to your description, I think you might want to group data according to Selection. If this is the case, you could refer to this query:

select STRING_AGG(priority,',') as PRIORITY,
		STRING_AGG(order_no,',') as ORDER_NO,
		STRING_AGG(article,',') as CUSTOMER_ARTICLE,
		STRING_AGG(color,',') as CUSTOMER_COLOR,
		MAX(OD.belongs) as BELONGS,
		MAX(OD.selection) as Selection,
		STRING_AGG(thickness,'&') as THICKNESS,
		SUM(quantity) as ORDER_QUANTITY,
		MAX(OD.unit) as UNIT,
		MAX(stock.available_qty) as STOCK_QTY,
		SUM(quantity)-MAX(stock.available_qty) as BALANCE_REQUIRED_QUANTITY
from order_deatils OD
join stock on OD.selection = stock.belongs
group by selection
order by priority,selection

Result:

If I misunderstood something, please let me know.

Best regards,

Xudong Peng

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Monday, September 21, 2020 11:45 AM

All replies