USE [bwslb2]
GO
/****** Object: Table [dbo].[b2_block_info] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_block_info](
[blockId] [int] IDENTITY(1,1) NOT NULL,
[block] [nvarchar](50) NULL,
[c_time] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[blockId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_building_info] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_building_info](
[building_id] [int] IDENTITY(1,1) NOT NULL,
[building_name] [nvarchar](50) NULL,
[company] [int] NULL,
CONSTRAINT [PK_b2_building_info] PRIMARY KEY CLUSTERED
(
[building_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_buyer_info] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_buyer_info](
[buyer_id] [int] IDENTITY(1,1) NOT NULL,
[buyer] [nvarchar](50) NULL,
[country] [int] NULL,
[email] [nvarchar](50) NULL,
[phone] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL,
[execution_time] [datetime] NULL,
[status] [int] NULL,
[unit] [int] NULL,
[user_id] [int] NULL,
CONSTRAINT [PK_b2_buyer_info] PRIMARY KEY CLUSTERED
(
[buyer_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_company_info] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_company_info](
[company_id] [int] IDENTITY(1,1) NOT NULL,
[company_name] [nvarchar](50) NULL,
[company_name_bangla] [nvarchar](50) NULL,
[company_address] [nvarchar](50) NULL,
[company_address_bangla] [nvarchar](50) NULL,
[company_logo] [image] NULL,
[execution_time] [datetime] NULL,
CONSTRAINT [PK_b2_company_info] PRIMARY KEY CLUSTERED
(
[company_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_country] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_country](
[country_id] [int] IDENTITY(1,1) NOT NULL,
[country] [nvarchar](100) NULL,
[country_bangla] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED
(
[country_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_department_info] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_department_info](
[deptId] [int] IDENTITY(1,1) NOT NULL,
[department] [nvarchar](50) NULL,
[c_time] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[deptId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_designation_info] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_designation_info](
[desigId] [int] IDENTITY(1,1) NOT NULL,
[designation] [nvarchar](100) NULL,
[designation_bangla] [nvarchar](100) NULL,
[salary_grade] [int] NULL,
[c_time] [datetime] NULL,
[user_id] [int] NULL,
PRIMARY KEY CLUSTERED
(
[desigId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_emp_basic] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_emp_basic](
[emp_id] [int] IDENTITY(1,1) NOT NULL,
[emp_cardno] [nvarchar](50) NOT NULL,
[emp_name] [nvarchar](100) NULL,
[section] [int] NULL,
[designation] [int] NULL,
[IsActive] [int] NULL,
[unit] [int] NULL,
[emp_name_bangla] [nvarchar](100) NULL,
[department] [int] NULL,
[block] [int] NULL,
[sromikType] [int] NULL,
[shift] [int] NULL,
[grade] [int] NULL,
[joiningDate] [date] NULL,
[c_time] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[emp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_floor_production_list] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_floor_production_list](
[prod_id] [int] IDENTITY(1,1) NOT NULL,
[emp_id] [int] NULL,
[section] [int] NULL,
[prod_date] [date] NULL,
[style] [int] NULL,
[process] [int] NULL,
[quantity] [int] NULL,
[sku] [int] NULL,
[c_time] [datetime] NULL,
[unit] [int] NULL,
[process_by] [int] NULL,
PRIMARY KEY CLUSTERED
(
[prod_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_goods_received] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_goods_received](
[rcv_id] [int] IDENTITY(1,1) NOT NULL,
[rcv_date] [date] NULL,
[rcv_by_section] [int] NULL,
[rcv_from_section] [int] NULL,
[style] [int] NULL,
[process] [int] NULL,
[color] [int] NULL,
[size] [int] NULL,
[rcv_qty] [int] NULL,
[rcv_cmnt] [nvarchar](50) NULL,
[c_time] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[rcv_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_process_info] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_process_info](
[process_id] [int] IDENTITY(1,1) NOT NULL,
[section] [int] NULL,
[style] [int] NULL,
[process_name] [nvarchar](50) NULL,
[last_process] [int] NULL,
[sku] [nvarchar](255) NULL,
[unit] [int] NULL,
[user_id] [int] NULL,
CONSTRAINT [PK_b2_process_info] PRIMARY KEY CLUSTERED
(
[process_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_section_info] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_section_info](
[section_id] [int] IDENTITY(1,1) NOT NULL,
[department] [int] NULL,
[section] [nvarchar](50) NULL,
[manpower] [int] NULL,
[execution_time] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[section_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_shift_info] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_shift_info](
[shiftId] [int] IDENTITY(1,1) NOT NULL,
[shift] [nvarchar](50) NULL,
[c_time] [datetime] NULL,
[user_id] [int] NULL,
PRIMARY KEY CLUSTERED
(
[shiftId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_style_color] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_style_color](
[color_id] [int] IDENTITY(1,1) NOT NULL,
[section] [int] NULL,
[style] [int] NULL,
[process] [int] NULL,
[color] [nvarchar](50) NULL,
[c_time] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[color_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_style_color_size] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_style_color_size](
[size_id] [int] IDENTITY(1,1) NOT NULL,
[color] [int] NULL,
[size] [nvarchar](50) NULL,
[c_time] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[size_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_style_info] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_style_info](
[style_id] [int] IDENTITY(1,1) NOT NULL,
[buyer] [int] NULL,
[section] [int] NULL,
[style] [nvarchar](255) NULL,
[status] [int] NULL,
[c_time] [datetime] NULL,
[unit] [int] NULL,
[user_id] [int] NULL,
CONSTRAINT [PK_b2_style_info] PRIMARY KEY CLUSTERED
(
[style_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_style_piece_rate] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_style_piece_rate](
[rate_id] [int] IDENTITY(1,1) NOT NULL,
[section] [int] NULL,
[style] [int] NULL,
[process] [int] NULL,
[rate] [float] NULL,
[c_time] [datetime] NULL,
[user_id] [int] NULL,
[unit] [int] NULL,
PRIMARY KEY CLUSTERED
(
[rate_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_user_permission] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_user_permission](
[Id] [int] IDENTITY(1,1) NOT NULL,
[userId] [int] NULL,
[P1] [int] NULL,
[P2] [int] NULL,
[P3] [int] NULL,
[P4] [int] NULL,
[P5] [int] NULL,
[P6] [int] NULL,
[P7] [int] NULL,
[P8] [int] NULL,
[P9] [int] NULL,
[P10] [int] NULL,
[P11] [int] NULL,
[P12] [int] NULL,
[P13] [int] NULL,
[P14] [int] NULL,
[P15] [int] NULL,
[P16] [int] NULL,
[P17] [int] NULL,
[P18] [int] NULL,
[P19] [int] NULL,
[P20] [int] NULL,
[P21] [int] NULL,
[P22] [int] NULL,
[P23] [int] NULL,
[P24] [int] NULL,
[P25] [int] NULL,
[P26] [int] NULL,
[P27] [int] NULL,
[P28] [int] NULL,
[P29] [int] NULL,
[P30] [int] NULL,
[P31] [int] NULL,
[P32] [int] NULL,
[P33] [int] NULL,
[P34] [int] NULL,
[P35] [int] NULL,
[P36] [int] NULL,
[P37] [int] NULL,
[P38] [int] NULL,
[P39] [int] NULL,
[P40] [int] NULL,
[P41] [int] NULL,
[P42] [int] NULL,
[P43] [int] NULL,
[P44] [int] NULL,
[P45] [int] NULL,
[P46] [int] NULL,
[P47] [int] NULL,
[P48] [int] NULL,
[P49] [int] NULL,
[P50] [int] NULL,
[c_time] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_user_role] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_user_role](
[role_id] [int] IDENTITY(1,1) NOT NULL,
[role_name] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[role_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[b2_users] Script Date: 8/6/2023 10:52:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[b2_users](
[user_id] [int] IDENTITY(1,1) NOT NULL,
[username] [varchar](50) NULL,
[password] [varchar](50) NULL,
[user_role] [int] NULL,
[execution_time] [datetime] NULL,
[section] [int] NULL,
[name] [nvarchar](50) NULL,
[building] [int] NULL,
PRIMARY KEY CLUSTERED
(
[user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[b2_building_info] WITH CHECK ADD CONSTRAINT [FK_b2_building_info_b2_company_info] FOREIGN KEY([company])
REFERENCES [dbo].[b2_company_info] ([company_id])
GO
ALTER TABLE [dbo].[b2_building_info] CHECK CONSTRAINT [FK_b2_building_info_b2_company_info]
GO
ALTER TABLE [dbo].[b2_buyer_info] WITH CHECK ADD CONSTRAINT [FK_b2_buyer_info_b2_building_info] FOREIGN KEY([unit])
REFERENCES [dbo].[b2_building_info] ([building_id])
GO
ALTER TABLE [dbo].[b2_buyer_info] CHECK CONSTRAINT [FK_b2_buyer_info_b2_building_info]
GO
ALTER TABLE [dbo].[b2_buyer_info] WITH CHECK ADD CONSTRAINT [FK_b2_buyer_info_b2_country] FOREIGN KEY([country])
REFERENCES [dbo].[b2_country] ([country_id])
GO
ALTER TABLE [dbo].[b2_buyer_info] CHECK CONSTRAINT [FK_b2_buyer_info_b2_country]
GO
ALTER TABLE [dbo].[b2_buyer_info] WITH CHECK ADD CONSTRAINT [FK_b2_buyer_info_b2_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[b2_users] ([user_id])
GO
ALTER TABLE [dbo].[b2_buyer_info] CHECK CONSTRAINT [FK_b2_buyer_info_b2_users]
GO
ALTER TABLE [dbo].[b2_designation_info] WITH CHECK ADD CONSTRAINT [FK_b2_designation_info_b2_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[b2_users] ([user_id])
ON UPDATE SET NULL
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[b2_designation_info] CHECK CONSTRAINT [FK_b2_designation_info_b2_users]
GO
ALTER TABLE [dbo].[b2_emp_basic] WITH CHECK ADD CONSTRAINT [FK_b2_emp_basic_b2_block_info] FOREIGN KEY([block])
REFERENCES [dbo].[b2_block_info] ([blockId])
GO
ALTER TABLE [dbo].[b2_emp_basic] CHECK CONSTRAINT [FK_b2_emp_basic_b2_block_info]
GO
ALTER TABLE [dbo].[b2_emp_basic] WITH CHECK ADD CONSTRAINT [FK_b2_emp_basic_b2_building_info] FOREIGN KEY([unit])
REFERENCES [dbo].[b2_building_info] ([building_id])
GO
ALTER TABLE [dbo].[b2_emp_basic] CHECK CONSTRAINT [FK_b2_emp_basic_b2_building_info]
GO
ALTER TABLE [dbo].[b2_emp_basic] WITH CHECK ADD CONSTRAINT [FK_b2_emp_basic_b2_department_info] FOREIGN KEY([department])
REFERENCES [dbo].[b2_department_info] ([deptId])
GO
ALTER TABLE [dbo].[b2_emp_basic] CHECK CONSTRAINT [FK_b2_emp_basic_b2_department_info]
GO
ALTER TABLE [dbo].[b2_emp_basic] WITH CHECK ADD CONSTRAINT [FK_b2_emp_basic_b2_designation_info1] FOREIGN KEY([designation])
REFERENCES [dbo].[b2_designation_info] ([desigId])
GO
ALTER TABLE [dbo].[b2_emp_basic] CHECK CONSTRAINT [FK_b2_emp_basic_b2_designation_info1]
GO
ALTER TABLE [dbo].[b2_emp_basic] WITH CHECK ADD CONSTRAINT [FK_b2_emp_basic_b2_section_info] FOREIGN KEY([section])
REFERENCES [dbo].[b2_section_info] ([section_id])
GO
ALTER TABLE [dbo].[b2_emp_basic] CHECK CONSTRAINT [FK_b2_emp_basic_b2_section_info]
GO
ALTER TABLE [dbo].[b2_emp_basic] WITH CHECK ADD CONSTRAINT [FK_b2_emp_basic_b2_shift_info] FOREIGN KEY([shift])
REFERENCES [dbo].[b2_shift_info] ([shiftId])
GO
ALTER TABLE [dbo].[b2_emp_basic] CHECK CONSTRAINT [FK_b2_emp_basic_b2_shift_info]
GO
ALTER TABLE [dbo].[b2_floor_production_list] WITH CHECK ADD CONSTRAINT [FK_b2_floor_production_b2_building_info] FOREIGN KEY([unit])
REFERENCES [dbo].[b2_building_info] ([building_id])
GO
ALTER TABLE [dbo].[b2_floor_production_list] CHECK CONSTRAINT [FK_b2_floor_production_b2_building_info]
GO
ALTER TABLE [dbo].[b2_floor_production_list] WITH CHECK ADD CONSTRAINT [FK_b2_floor_production_b2_emp_basic] FOREIGN KEY([emp_id])
REFERENCES [dbo].[b2_emp_basic] ([emp_id])
GO
ALTER TABLE [dbo].[b2_floor_production_list] CHECK CONSTRAINT [FK_b2_floor_production_b2_emp_basic]
GO
ALTER TABLE [dbo].[b2_floor_production_list] WITH CHECK ADD CONSTRAINT [FK_b2_floor_production_b2_process_info] FOREIGN KEY([process])
REFERENCES [dbo].[b2_process_info] ([process_id])
GO
ALTER TABLE [dbo].[b2_floor_production_list] CHECK CONSTRAINT [FK_b2_floor_production_b2_process_info]
GO
ALTER TABLE [dbo].[b2_floor_production_list] WITH CHECK ADD CONSTRAINT [FK_b2_floor_production_b2_section_info] FOREIGN KEY([section])
REFERENCES [dbo].[b2_section_info] ([section_id])
GO
ALTER TABLE [dbo].[b2_floor_production_list] CHECK CONSTRAINT [FK_b2_floor_production_b2_section_info]
GO
ALTER TABLE [dbo].[b2_floor_production_list] WITH CHECK ADD CONSTRAINT [FK_b2_floor_production_b2_style_info] FOREIGN KEY([style])
REFERENCES [dbo].[b2_style_info] ([style_id])
GO
ALTER TABLE [dbo].[b2_floor_production_list] CHECK CONSTRAINT [FK_b2_floor_production_b2_style_info]
GO
ALTER TABLE [dbo].[b2_floor_production_list] WITH CHECK ADD CONSTRAINT [FK_b2_floor_production_b2_users] FOREIGN KEY([process_by])
REFERENCES [dbo].[b2_users] ([user_id])
GO
ALTER TABLE [dbo].[b2_floor_production_list] CHECK CONSTRAINT [FK_b2_floor_production_b2_users]
GO
ALTER TABLE [dbo].[b2_floor_production_list] WITH CHECK ADD CONSTRAINT [FK_b2_floor_production_SKU] FOREIGN KEY([sku])
REFERENCES [dbo].[b2_process_info] ([process_id])
GO
ALTER TABLE [dbo].[b2_floor_production_list] CHECK CONSTRAINT [FK_b2_floor_production_SKU]
GO
ALTER TABLE [dbo].[b2_goods_received] WITH CHECK ADD CONSTRAINT [FK_b2_goods_received_b2_process_info] FOREIGN KEY([process])
REFERENCES [dbo].[b2_process_info] ([process_id])
GO
ALTER TABLE [dbo].[b2_goods_received] CHECK CONSTRAINT [FK_b2_goods_received_b2_process_info]
GO
ALTER TABLE [dbo].[b2_goods_received] WITH CHECK ADD CONSTRAINT [FK_b2_goods_received_b2_section_info] FOREIGN KEY([rcv_by_section])
REFERENCES [dbo].[b2_section_info] ([section_id])
GO
ALTER TABLE [dbo].[b2_goods_received] CHECK CONSTRAINT [FK_b2_goods_received_b2_section_info]
GO
ALTER TABLE [dbo].[b2_goods_received] WITH CHECK ADD CONSTRAINT [FK_b2_goods_received_b2_section_info_2] FOREIGN KEY([rcv_from_section])
REFERENCES [dbo].[b2_section_info] ([section_id])
GO
ALTER TABLE [dbo].[b2_goods_received] CHECK CONSTRAINT [FK_b2_goods_received_b2_section_info_2]
GO
ALTER TABLE [dbo].[b2_goods_received] WITH CHECK ADD CONSTRAINT [FK_b2_goods_received_b2_style_color] FOREIGN KEY([color])
REFERENCES [dbo].[b2_style_color] ([color_id])
GO
ALTER TABLE [dbo].[b2_goods_received] CHECK CONSTRAINT [FK_b2_goods_received_b2_style_color]
GO
ALTER TABLE [dbo].[b2_goods_received] WITH CHECK ADD CONSTRAINT [FK_b2_goods_received_b2_style_color_size] FOREIGN KEY([size])
REFERENCES [dbo].[b2_style_color_size] ([size_id])
GO
ALTER TABLE [dbo].[b2_goods_received] CHECK CONSTRAINT [FK_b2_goods_received_b2_style_color_size]
GO
ALTER TABLE [dbo].[b2_goods_received] WITH CHECK ADD CONSTRAINT [FK_b2_goods_received_b2_style_info] FOREIGN KEY([style])
REFERENCES [dbo].[b2_style_info] ([style_id])
GO
ALTER TABLE [dbo].[b2_goods_received] CHECK CONSTRAINT [FK_b2_goods_received_b2_style_info]
GO
ALTER TABLE [dbo].[b2_process_info] WITH CHECK ADD CONSTRAINT [FK_b2_process_info_b2_building_info] FOREIGN KEY([unit])
REFERENCES [dbo].[b2_building_info] ([building_id])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[b2_process_info] CHECK CONSTRAINT [FK_b2_process_info_b2_building_info]
GO
ALTER TABLE [dbo].[b2_process_info] WITH CHECK ADD CONSTRAINT [FK_b2_process_info_b2_section_info] FOREIGN KEY([section])
REFERENCES [dbo].[b2_section_info] ([section_id])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[b2_process_info] CHECK CONSTRAINT [FK_b2_process_info_b2_section_info]
GO
ALTER TABLE [dbo].[b2_process_info] WITH CHECK ADD CONSTRAINT [FK_b2_process_info_b2_style_info] FOREIGN KEY([style])
REFERENCES [dbo].[b2_style_info] ([style_id])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[b2_process_info] CHECK CONSTRAINT [FK_b2_process_info_b2_style_info]
GO
ALTER TABLE [dbo].[b2_process_info] WITH CHECK ADD CONSTRAINT [FK_b2_process_info_b2_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[b2_users] ([user_id])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[b2_process_info] CHECK CONSTRAINT [FK_b2_process_info_b2_users]
GO
ALTER TABLE [dbo].[b2_section_info] WITH CHECK ADD CONSTRAINT [FK_b2_section_info_b2_department_info] FOREIGN KEY([department])
REFERENCES [dbo].[b2_department_info] ([deptId])
GO
ALTER TABLE [dbo].[b2_section_info] CHECK CONSTRAINT [FK_b2_section_info_b2_department_info]
GO
ALTER TABLE [dbo].[b2_style_color] WITH CHECK ADD CONSTRAINT [FK_b2_style_color_b2_process_info] FOREIGN KEY([process])
REFERENCES [dbo].[b2_process_info] ([process_id])
GO
ALTER TABLE [dbo].[b2_style_color] CHECK CONSTRAINT [FK_b2_style_color_b2_process_info]
GO
ALTER TABLE [dbo].[b2_style_color] WITH CHECK ADD CONSTRAINT [FK_b2_style_color_b2_section_info] FOREIGN KEY([section])
REFERENCES [dbo].[b2_section_info] ([section_id])
GO
ALTER TABLE [dbo].[b2_style_color] CHECK CONSTRAINT [FK_b2_style_color_b2_section_info]
GO
ALTER TABLE [dbo].[b2_style_color] WITH CHECK ADD CONSTRAINT [FK_b2_style_color_b2_style_info] FOREIGN KEY([style])
REFERENCES [dbo].[b2_style_info] ([style_id])
GO
ALTER TABLE [dbo].[b2_style_color] CHECK CONSTRAINT [FK_b2_style_color_b2_style_info]
GO
ALTER TABLE [dbo].[b2_style_color_size] WITH CHECK ADD CONSTRAINT [FK_b2_style_color_size_b2_style_color] FOREIGN KEY([color])
REFERENCES [dbo].[b2_style_color] ([color_id])
GO
ALTER TABLE [dbo].[b2_style_color_size] CHECK CONSTRAINT [FK_b2_style_color_size_b2_style_color]
GO
ALTER TABLE [dbo].[b2_style_info] WITH CHECK ADD CONSTRAINT [FK_b2_style_info_b2_building_info] FOREIGN KEY([unit])
REFERENCES [dbo].[b2_building_info] ([building_id])
GO
ALTER TABLE [dbo].[b2_style_info] CHECK CONSTRAINT [FK_b2_style_info_b2_building_info]
GO
ALTER TABLE [dbo].[b2_style_info] WITH CHECK ADD CONSTRAINT [FK_b2_style_info_b2_buyer_info] FOREIGN KEY([buyer])
REFERENCES [dbo].[b2_buyer_info] ([buyer_id])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[b2_style_info] CHECK CONSTRAINT [FK_b2_style_info_b2_buyer_info]
GO
ALTER TABLE [dbo].[b2_style_info] WITH CHECK ADD CONSTRAINT [FK_b2_style_info_b2_section_info] FOREIGN KEY([section])
REFERENCES [dbo].[b2_section_info] ([section_id])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[b2_style_info] CHECK CONSTRAINT [FK_b2_style_info_b2_section_info]
GO
ALTER TABLE [dbo].[b2_style_info] WITH CHECK ADD CONSTRAINT [FK_b2_style_info_b2_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[b2_users] ([user_id])
GO
ALTER TABLE [dbo].[b2_style_info] CHECK CONSTRAINT [FK_b2_style_info_b2_users]
GO
ALTER TABLE [dbo].[b2_style_piece_rate] WITH CHECK ADD CONSTRAINT [FK_b2_style_piece_rate_b2_building_info] FOREIGN KEY([unit])
REFERENCES [dbo].[b2_building_info] ([building_id])
GO
ALTER TABLE [dbo].[b2_style_piece_rate] CHECK CONSTRAINT [FK_b2_style_piece_rate_b2_building_info]
GO
ALTER TABLE [dbo].[b2_style_piece_rate] WITH CHECK ADD CONSTRAINT [FK_b2_style_piece_rate_b2_process_info] FOREIGN KEY([process])
REFERENCES [dbo].[b2_process_info] ([process_id])
GO
ALTER TABLE [dbo].[b2_style_piece_rate] CHECK CONSTRAINT [FK_b2_style_piece_rate_b2_process_info]
GO
ALTER TABLE [dbo].[b2_style_piece_rate] WITH CHECK ADD CONSTRAINT [FK_b2_style_piece_rate_b2_section_info] FOREIGN KEY([section])
REFERENCES [dbo].[b2_section_info] ([section_id])
GO
ALTER TABLE [dbo].[b2_style_piece_rate] CHECK CONSTRAINT [FK_b2_style_piece_rate_b2_section_info]
GO
ALTER TABLE [dbo].[b2_style_piece_rate] WITH CHECK ADD CONSTRAINT [FK_b2_style_piece_rate_b2_style_info] FOREIGN KEY([style])
REFERENCES [dbo].[b2_style_info] ([style_id])
GO
ALTER TABLE [dbo].[b2_style_piece_rate] CHECK CONSTRAINT [FK_b2_style_piece_rate_b2_style_info]
GO
ALTER TABLE [dbo].[b2_style_piece_rate] WITH CHECK ADD CONSTRAINT [FK_b2_style_piece_rate_b2_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[b2_users] ([user_id])
GO
ALTER TABLE [dbo].[b2_style_piece_rate] CHECK CONSTRAINT [FK_b2_style_piece_rate_b2_users]
GO
ALTER TABLE [dbo].[b2_users] WITH CHECK ADD CONSTRAINT [FK_b2_users_b2_building_info] FOREIGN KEY([building])
REFERENCES [dbo].[b2_building_info] ([building_id])
GO
ALTER TABLE [dbo].[b2_users] CHECK CONSTRAINT [FK_b2_users_b2_building_info]
GO
ALTER TABLE [dbo].[b2_users] WITH CHECK ADD CONSTRAINT [FK_b2_users_role] FOREIGN KEY([user_role])
REFERENCES [dbo].[b2_user_role] ([role_id])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[b2_users] CHECK CONSTRAINT [FK_b2_users_role]
GO
ALTER TABLE [dbo].[b2_users] WITH CHECK ADD CONSTRAINT [FK_b2_users_section] FOREIGN KEY([section])
REFERENCES [dbo].[b2_section_info] ([section_id])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[b2_users] CHECK CONSTRAINT [FK_b2_users_section]
GO
Full database file here. Here I use the below query to show my result,
USE [bwslb2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--ALTER PROCEDURE [dbo].[b2_monthly_emp_prod_sheet_sp]
-- @empId int,
-- @fromdate date,
-- @todate date
--AS
declare @empId int
declare @fromdate date
declare @todate date
if @empId = 0 set @empId = null
set @fromdate = '2022-11-15'
set @todate = '2023-11-16'
begin
SELECT b2_emp_basic.emp_cardno,
--string_agg(day(b2_floor_production_list.prod_date),',') as ProdDate,
(select distinct string_agg(b2_floor_production_list.prod_date,',')) as ProdDate,
b2_style_info.style,b2_process_info.process_name,
sum(b2_floor_production_list.quantity) as Quantity
FROM b2_floor_production_list INNER JOIN
b2_process_info ON b2_floor_production_list.process = b2_process_info.process_id INNER JOIN
b2_style_info ON b2_process_info.style = b2_style_info.style_id INNER JOIN
b2_emp_basic ON b2_floor_production_list.emp_id = b2_emp_basic.emp_id
where (b2_floor_production_list.prod_date >= CONVERT(date,@fromdate))
and (b2_floor_production_list.prod_date <= CONVERT(date,@todate))
and (b2_floor_production_list.emp_id = @empId or @empId is null)
group by b2_emp_basic.emp_cardno, b2_style_info.style, b2_process_info.process_name
end
Output result gives me duplicate values.
How can I get single values from this query?