jafar_palideh

اعتبار سنجی ساعت طبق شیفت وعدم تجاوز مجموع ساعات تولیداز طول شیفت

با سلام.

من یه جدول شیفت دارم که      آی دی شیفت - ساعت شروع - ساعت پایان - روز بعد؟        رو در اون نگه میدارم.

حالا میخوام تولیدات شرکت رو ثبت کنم (از مستر و دیتیل استفاده میکنم)

دو تا جدول دارم . هدر و فوتر 

در هدر  :

 تاریخ و شیفت رو نگه میدارم

در فوتر  :

 آی دی ماشین(که قبلا در جدول ماشین ثبت شده) - آی دی محصول(که قبلا در جدول محصول ثبت شده) - تعداد تولید - ساعت شروع تولید - ساعت پایان تولید - زمان رگلاژ - زمان استراحت

حالا چی میخوام؟ :

1- اعتبار سنجی زمان شروع و پایان تولید طبق شیفت (مخصوصا شیفت های که میفته در روز بعد  مثلا 23 امروز تا 7 روز بعد)

 2 - مجموع زمان تولید (که میشه (ساعت پایان تولید  منهای ساعت شروع تولید)  ) از زمان کل شیفت به ازای هر  ماشین  بیشتر نشه .

3 - عدم همپوشانی ساعتها به ازای هر ماشین

لذت یادگیری با توسینسو
به عنوان شخصی که مدت هاست از سایت توسینسو استفاده می کنم باید بگم که واقعاً یکی از بهترین مرجع ها برای ارتقاء دانش شخصی هست. دوره های سایت، راهکارها و مطالب، همگی عالی هستند.
jafar_palideh

یه اسکریپت از جداول و دیتاهای فرضی میزارم .

USE [master]
GO
/****** Object:  Database [TestDb]    Script Date: 26/03/1398 09:44:37 ******/
CREATE DATABASE [TestDb]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDb.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDb_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDb_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TestDb] SET COMPATIBILITY_LEVEL = 120
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestDb].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TestDb] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [TestDb] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [TestDb] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [TestDb] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [TestDb] SET ARITHABORT OFF 
GO
ALTER DATABASE [TestDb] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [TestDb] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [TestDb] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [TestDb] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [TestDb] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [TestDb] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [TestDb] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [TestDb] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [TestDb] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [TestDb] SET  DISABLE_BROKER 
GO
ALTER DATABASE [TestDb] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [TestDb] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [TestDb] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [TestDb] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [TestDb] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [TestDb] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [TestDb] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [TestDb] SET RECOVERY FULL 
GO
ALTER DATABASE [TestDb] SET  MULTI_USER 
GO
ALTER DATABASE [TestDb] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [TestDb] SET DB_CHAINING OFF 
GO
ALTER DATABASE [TestDb] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [TestDb] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
ALTER DATABASE [TestDb] SET DELAYED_DURABILITY = DISABLED 
GO
EXEC sys.sp_db_vardecimal_storage_format N'TestDb', N'ON'
GO
USE [TestDb]
GO
/****** Object:  Table [dbo].[TblMashin]    Script Date: 26/03/1398 09:44:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblMashin](
	[MashinId] [int] IDENTITY(1,1) NOT NULL,
	[MashinName] [nvarchar](50) NULL,
 CONSTRAINT [PK_TblMashin] PRIMARY KEY CLUSTERED 
(
	[MashinId] 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
/****** Object:  Table [dbo].[TblMazrof]    Script Date: 26/03/1398 09:44:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblMazrof](
	[MazrofID] [int] IDENTITY(1,1) NOT NULL,
	[MazrofName] [nvarchar](50) NULL,
 CONSTRAINT [PK_TblMazrof] PRIMARY KEY CLUSTERED 
(
	[MazrofID] 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
/****** Object:  Table [dbo].[TblShift]    Script Date: 26/03/1398 09:44:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblShift](
	[ShiftId] [int] IDENTITY(1,1) NOT NULL,
	[ShiftName] [nvarchar](50) NULL,
	[ShiftTimeBegin] [time](7) NULL,
	[ShiftTimeEnd] [time](7) NULL,
	[ShiftEndDay] [bit] NULL,
	[ShiftActive] [bit] NULL,
 CONSTRAINT [PK_TblShift] PRIMARY KEY CLUSTERED 
(
	[ShiftId] 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
/****** Object:  Table [dbo].[TblTimelyContent]    Script Date: 26/03/1398 09:44:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblTimelyContent](
	[TimelyContentId] [int] IDENTITY(1,1) NOT NULL,
	[Mashin_Id] [int] NULL,
	[Mazrof_Id] [int] NULL,
	[TedadTolid] [int] NULL,
	[TedadZaieat] [int] NULL,
	[TimeBeginTimely] [time](7) NULL,
	[TimeEndTimely] [time](7) NULL,
	[TimeReglaj] [time](7) NULL,
	[TimeRest] [time](7) NULL,
	[TimelyHeader_Id] [int] NULL,
 CONSTRAINT [PK_TblTimelyContent] PRIMARY KEY CLUSTERED 
(
	[TimelyContentId] 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
/****** Object:  Table [dbo].[TblTimelyHeader]    Script Date: 26/03/1398 09:44:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblTimelyHeader](
	[TimelyHeaderId] [int] IDENTITY(1,1) NOT NULL,
	[DateTimely] [datetime] NULL,
	[Shift_Id] [int] NULL,
 CONSTRAINT [PK_TblTimelyHeader] PRIMARY KEY CLUSTERED 
(
	[TimelyHeaderId] 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 IDENTITY_INSERT [dbo].[TblMashin] ON 

INSERT [dbo].[TblMashin] ([MashinId], [MashinName]) VALUES (1, N'چاپ1')
INSERT [dbo].[TblMashin] ([MashinId], [MashinName]) VALUES (2, N'چاپ2')
INSERT [dbo].[TblMashin] ([MashinId], [MashinName]) VALUES (3, N'چاپ3')
INSERT [dbo].[TblMashin] ([MashinId], [MashinName]) VALUES (4, N'چاپ4')
SET IDENTITY_INSERT [dbo].[TblMashin] OFF
SET IDENTITY_INSERT [dbo].[TblMazrof] ON 

INSERT [dbo].[TblMazrof] ([MazrofID], [MazrofName]) VALUES (1, N'کالا1')
INSERT [dbo].[TblMazrof] ([MazrofID], [MazrofName]) VALUES (2, N'کالا2')
INSERT [dbo].[TblMazrof] ([MazrofID], [MazrofName]) VALUES (3, N'کالا3')
INSERT [dbo].[TblMazrof] ([MazrofID], [MazrofName]) VALUES (4, N'کالا4')
INSERT [dbo].[TblMazrof] ([MazrofID], [MazrofName]) VALUES (5, N'کالا5')
SET IDENTITY_INSERT [dbo].[TblMazrof] OFF
SET IDENTITY_INSERT [dbo].[TblShift] ON 

INSERT [dbo].[TblShift] ([ShiftId], [ShiftName], [ShiftTimeBegin], [ShiftTimeEnd], [ShiftEndDay], [ShiftActive]) VALUES (1, N'7تا15', CAST(N'07:00:00' AS Time), CAST(N'15:00:00' AS Time), 0, 1)
INSERT [dbo].[TblShift] ([ShiftId], [ShiftName], [ShiftTimeBegin], [ShiftTimeEnd], [ShiftEndDay], [ShiftActive]) VALUES (2, N'15تا23', CAST(N'15:00:00' AS Time), CAST(N'23:00:00' AS Time), 0, 1)
INSERT [dbo].[TblShift] ([ShiftId], [ShiftName], [ShiftTimeBegin], [ShiftTimeEnd], [ShiftEndDay], [ShiftActive]) VALUES (3, N'23تا7روزبعد', CAST(N'23:00:00' AS Time), CAST(N'07:00:00' AS Time), 1, 1)
INSERT [dbo].[TblShift] ([ShiftId], [ShiftName], [ShiftTimeBegin], [ShiftTimeEnd], [ShiftEndDay], [ShiftActive]) VALUES (4, N'19تا3روز بعد', CAST(N'19:00:00' AS Time), CAST(N'03:00:00' AS Time), 1, 1)
SET IDENTITY_INSERT [dbo].[TblShift] OFF
SET IDENTITY_INSERT [dbo].[TblTimelyContent] ON 

INSERT [dbo].[TblTimelyContent] ([TimelyContentId], [Mashin_Id], [Mazrof_Id], [TedadTolid], [TedadZaieat], [TimeBeginTimely], [TimeEndTimely], [TimeReglaj], [TimeRest], [TimelyHeader_Id]) VALUES (1, 1, 1, 1000, 10, CAST(N'07:00:00' AS Time), CAST(N'09:00:00' AS Time), CAST(N'00:10:00' AS Time), CAST(N'00:15:00' AS Time), 1)
INSERT [dbo].[TblTimelyContent] ([TimelyContentId], [Mashin_Id], [Mazrof_Id], [TedadTolid], [TedadZaieat], [TimeBeginTimely], [TimeEndTimely], [TimeReglaj], [TimeRest], [TimelyHeader_Id]) VALUES (2, 1, 2, 1500, 20, CAST(N'09:00:00' AS Time), CAST(N'14:00:00' AS Time), CAST(N'00:20:00' AS Time), CAST(N'00:10:00' AS Time), 1)
INSERT [dbo].[TblTimelyContent] ([TimelyContentId], [Mashin_Id], [Mazrof_Id], [TedadTolid], [TedadZaieat], [TimeBeginTimely], [TimeEndTimely], [TimeReglaj], [TimeRest], [TimelyHeader_Id]) VALUES (3, 1, 3, 500, 5, CAST(N'14:00:00' AS Time), CAST(N'15:00:00' AS Time), CAST(N'00:05:00' AS Time), CAST(N'00:07:00' AS Time), 1)
INSERT [dbo].[TblTimelyContent] ([TimelyContentId], [Mashin_Id], [Mazrof_Id], [TedadTolid], [TedadZaieat], [TimeBeginTimely], [TimeEndTimely], [TimeReglaj], [TimeRest], [TimelyHeader_Id]) VALUES (4, 2, 4, 5000, 20, CAST(N'23:00:00' AS Time), CAST(N'01:00:00' AS Time), CAST(N'00:00:00' AS Time), CAST(N'00:05:00' AS Time), 2)
INSERT [dbo].[TblTimelyContent] ([TimelyContentId], [Mashin_Id], [Mazrof_Id], [TedadTolid], [TedadZaieat], [TimeBeginTimely], [TimeEndTimely], [TimeReglaj], [TimeRest], [TimelyHeader_Id]) VALUES (5, 2, 4, 6000, 20, CAST(N'01:00:00' AS Time), CAST(N'07:00:00' AS Time), CAST(N'00:00:00' AS Time), CAST(N'00:05:00' AS Time), 2)
SET IDENTITY_INSERT [dbo].[TblTimelyContent] OFF
SET IDENTITY_INSERT [dbo].[TblTimelyHeader] ON 

INSERT [dbo].[TblTimelyHeader] ([TimelyHeaderId], [DateTimely], [Shift_Id]) VALUES (1, CAST(N'2019-06-16 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[TblTimelyHeader] ([TimelyHeaderId], [DateTimely], [Shift_Id]) VALUES (2, CAST(N'2019-06-16 00:00:00.000' AS DateTime), 3)
SET IDENTITY_INSERT [dbo].[TblTimelyHeader] OFF
ALTER TABLE [dbo].[TblTimelyContent]  WITH CHECK ADD  CONSTRAINT [FK_TblTimelyContent_TblMashin] FOREIGN KEY([Mashin_Id])
REFERENCES [dbo].[TblMashin] ([MashinId])
GO
ALTER TABLE [dbo].[TblTimelyContent] CHECK CONSTRAINT [FK_TblTimelyContent_TblMashin]
GO
ALTER TABLE [dbo].[TblTimelyContent]  WITH CHECK ADD  CONSTRAINT [FK_TblTimelyContent_TblMazrof] FOREIGN KEY([Mazrof_Id])
REFERENCES [dbo].[TblMazrof] ([MazrofID])
GO
ALTER TABLE [dbo].[TblTimelyContent] CHECK CONSTRAINT [FK_TblTimelyContent_TblMazrof]
GO
ALTER TABLE [dbo].[TblTimelyContent]  WITH CHECK ADD  CONSTRAINT [FK_TblTimelyContent_TblTimelyHeader] FOREIGN KEY([TimelyHeader_Id])
REFERENCES [dbo].[TblTimelyHeader] ([TimelyHeaderId])
GO
ALTER TABLE [dbo].[TblTimelyContent] CHECK CONSTRAINT [FK_TblTimelyContent_TblTimelyHeader]
GO
ALTER TABLE [dbo].[TblTimelyHeader]  WITH CHECK ADD  CONSTRAINT [FK_TblTimelyHeader_TblShift] FOREIGN KEY([Shift_Id])
REFERENCES [dbo].[TblShift] ([ShiftId])
GO
ALTER TABLE [dbo].[TblTimelyHeader] CHECK CONSTRAINT [FK_TblTimelyHeader_TblShift]
GO
USE [master]
GO
ALTER DATABASE [TestDb] SET  READ_WRITE 
GO

پاسخ شما
برای ارسال پاسخ خود وارد شوید.
از سرتاسر توسینسو
تنظیمات حریم خصوصی
تائید صرفنظر
×

تو می تونی بهترین نتیجه رو تضمینی با بهترین های ایران بدست بیاری ، پس مقایسه کن و بعد خرید کن : فقط توی جشنواره تابستانه می تونی امروز ارزونتر از فردا خرید کنی ....