jafarpalideh
یک شنبه 26 خرداد 1398, 07:30 صبح
اینم اسکریپت جداول با داده های فرضی
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.l df' , 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
jafarpalideh
دوشنبه 27 خرداد 1398, 09:27 صبح
کوئری که مشکل رو نشون میده رو میزارم :
SELECT tm.MashinName, ISNULL(SUM(DATEDIFF(MINUTE, ttc.TimeBeginTimely, ttc.TimeEndTimely)), 0) AS TotalWorkMashin, DATEDIFF(MINUTE, ts.ShiftTimeBegin, ts.ShiftTimeEnd) - SUM(DATEDIFF(MINUTE, ttc.TimeBeginTimely, ttc.TimeEndTimely)) - SUM(DATEPART(hour, ttc.TimeReglaj) * 60 + DATEPART(minute, ttc.TimeReglaj) * 1) - SUM(DATEPART(hour, ttc.TimeRest) * 60 + DATEPART(minute, ttc.TimeRest) * 1) AS TotalNoWork, SUM(DATEPART(hour, ttc.TimeReglaj) * 60 + DATEPART(minute,
ttc.TimeReglaj) * 1) AS timereglaj, SUM(DATEPART(hour, ttc.TimeRest) * 60 + DATEPART(minute, ttc.TimeRest) * 1) AS timerest, ttc.Mashin_Id, ts.ShiftTimeBegin, ts.ShiftTimeEnd, ts.ShiftEndDay,
tth.DateTimely, tth.TimelyHeaderId, tm.MashinId
FROM dbo.TblTimelyHeader AS tth LEFT OUTER JOIN
dbo.TblShift AS ts ON tth.Shift_Id = ts.ShiftId LEFT OUTER JOIN
dbo.TblTimelyContent AS ttc ON tth.TimelyHeaderId = ttc.TimelyHeader_Id LEFT OUTER JOIN
dbo.TblMashin AS tm ON tm.MashinId = ttc.Mashin_Id
GROUP BY tm.MashinName, ts.ShiftTimeBegin, ts.ShiftTimeEnd, ttc.Mashin_Id, ts.ShiftEndDay, tth.DateTimely, tth.TimelyHeaderId, tm.MashinId
اینم عکس مشکل:
150371
Mahmoud.Afrad
چهارشنبه 29 خرداد 1398, 18:39 عصر
SELECT TblMashin.MashinId,
TblMashin.MashinName,
SUM(CASE
WHEN TimeBeginTimely < TimeEndTimely
THEN DATEDIFF(SECOND, TimeBeginTimely, TimeEndTimely)
WHEN TimeBeginTimely > TimeEndTimely
THEN DATEDIFF(SECOND, TimeBeginTimely, '23:59:59') + 1 + DATEDIFF(SECOND, 0, TimeEndTimely)
END) AS TotalWorkTime, --زمان کارکرد
SUM(DATEDIFF(SECOND, 0, TblTimelyContent.TimeReglaj) + DATEDIFF(SECOND, 0, TblTimelyContent.TimeRest)) AS TotalMaintenanceTime, --زمان تعمیرات
TblTimelyContent.Mashin_Id,
TblShift.ShiftTimeBegin,
TblShift.ShiftTimeEnd,
TblShift.ShiftEndDay,
TblTimelyHeader.DateTimely,
TblTimelyHeader.TimelyHeaderId
FROM TblTimelyHeader
LEFT OUTER JOIN TblShift ON TblTimelyHeader.Shift_Id = TblShift.ShiftId
LEFT OUTER JOIN TblTimelyContent ON TblTimelyHeader.TimelyHeaderId = TblTimelyContent.TimelyHeader_Id
LEFT OUTER JOIN TblMashin ON TblMashin.MashinId = TblTimelyContent.Mashin_Id
GROUP BY TblMashin.MashinName,
TblShift.ShiftTimeBegin,
TblShift.ShiftTimeEnd,
TblTimelyContent.Mashin_Id,
TblShift.ShiftEndDay,
TblTimelyHeader.DateTimely,
TblTimelyHeader.TimelyHeaderId,
TblMashin.MashinId;
vBulletin® v4.2.5, Copyright ©2000-1404, Jelsoft Enterprises Ltd.