USE [master]
GO
/****** Object:  Database [dbRSExport_XXXXXX]    Script Date: 2025-04-08 14:36:37 ******/
CREATE DATABASE [dbRSExport_XXXXXX]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'dbRSExport_XXXXXX', FILENAME = N'C:\Program Files\Microsoft SQL Server\SqlData\dbRSExport_XXXXXX.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'dbRSExport_XXXXXX_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\SqlData\dbRSExport_XXXXXX_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [dbRSExport_XXXXXX].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET ARITHABORT OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET  DISABLE_BROKER 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET  MULTI_USER 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET DB_CHAINING OFF 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET  READ_WRITE 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [dbRSExport_XXXXXX] SET QUERY_STORE = OFF
GO
USE [dbRSExport_XXXXXX]
GO
/****** Object:  UserDefinedTableType [dbo].[udtSetting]    Script Date: 2025-04-08 14:36:37 ******/
CREATE TYPE [dbo].[udtSetting] AS TABLE(
	[InstallationID] [int] NULL,
	[Installation] [nvarchar](100) NULL,
	[CalculatePerformanceFromWeightedProducedUnits] [bit] NULL,
	[MicrostopAsPerformanceLoss] [bit] NULL,
	[ReworkAsQualityLoss] [bit] NULL
)
GO
/****** Object:  UserDefinedTableType [dbo].[udtStop]    Script Date: 2025-04-08 14:36:37 ******/
CREATE TYPE [dbo].[udtStop] AS TABLE(
	[InstallationID] [int] NULL,
	[Installation] [nvarchar](100) NULL,
	[MeasurePointID] [uniqueidentifier] NULL,
	[MeasurePoint] [nvarchar](100) NULL,
	[IntervalStart] [datetime] NULL,
	[IntervalEnd] [datetime] NULL,
	[TotalStopDuration] [bigint] NULL,
	[StopReason] [nvarchar](300) NULL,
	[Categories] [nvarchar](300) NULL,
	[Station] [nvarchar](100) NULL,
	[Comment] [nvarchar](1000) NULL,
	[ShiftID] [nvarchar](1000) NULL
)
GO
/****** Object:  UserDefinedTableType [dbo].[udtStopOccasion]    Script Date: 2025-04-08 14:36:37 ******/
CREATE TYPE [dbo].[udtStopOccasion] AS TABLE(
	[InstallationID] [int] NULL,
	[Installation] [nvarchar](100) NULL,
	[MeasurePointID] [uniqueidentifier] NULL,
	[MeasurePoint] [nvarchar](100) NULL,
	[IntervalStart] [datetime] NULL,
	[IntervalEnd] [datetime] NULL,
	[Duration] [bigint] NULL,
	[TotalStopDuration] [bigint] NULL,
	[ScheduledDuration] [decimal](10, 2) NULL,
	[ProductiveDuration] [decimal](10, 2) NULL,
	[StopQuantity] [bigint] NULL,
	[NumberOfProductiveOccasions] [bigint] NULL,
	[MTBF] [decimal](10, 2) NULL,
	[MTTR] [decimal](10, 2) NULL
)
GO
/****** Object:  UserDefinedTableType [dbo].[udtWorktime]    Script Date: 2025-04-08 14:36:37 ******/
CREATE TYPE [dbo].[udtWorktime] AS TABLE(
	[InstallationID] [int] NULL,
	[Installation] [nvarchar](100) NULL,
	[MeasurePointID] [uniqueidentifier] NULL,
	[MeasurePoint] [nvarchar](100) NULL,
	[IntervalStart] [datetime] NULL,
	[IntervalEnd] [datetime] NULL,
	[ShiftID] [uniqueidentifier] NULL,
	[Shift] [nvarchar](100) NULL,
	[ProductionOrder] [nvarchar](50) NULL,
	[Article] [nvarchar](50) NULL,
	[ArticleName] [nvarchar](100) NULL,
	[ArticleType] [nvarchar](100) NULL,
	[TotalDuration] [decimal](10, 2) NULL,
	[ScheduledDuration] [decimal](10, 2) NULL,
	[ExcludedDuration] [decimal](10, 2) NULL,
	[StopDuration] [decimal](10, 2) NULL,
	[SetupStopDuration] [decimal](10, 2) NULL,
	[NoWorkTimeStopDuration] [decimal](10, 2) NULL,
	[MicroStopDuration] [decimal](10, 2) NULL,
	[ProductionTimeDuration] [decimal](10, 2) NULL,
	[UsedEffectiveTime] [decimal](10, 2) NULL,
	[ReworkedEffectiveTime] [decimal](10, 2) NULL,
	[ScrappedEffectiveTime] [decimal](10, 2) NULL,
	[OptimalProducedUnitsNoMicroStop] [decimal](10, 2) NULL,
	[OptimalProducedUnits] [decimal](10, 2) NULL,
	[ProducedUnits] [decimal](10, 2) NULL,
	[ReworkedUnits] [decimal](10, 2) NULL,
	[ScrappedUnits] [decimal](10, 2) NULL,
	[ApprovedUnits] [decimal](10, 2) NULL
)
GO
/****** Object:  Table [dbo].[tblSetting]    Script Date: 2025-04-08 14:36:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSetting](
	[InstallationID] [int] NOT NULL,
	[Installation] [nvarchar](100) NOT NULL,
	[CalculatePerformanceFromWeightedProducedUnits] [bit] NULL,
	[MicrostopAsPerformanceLoss] [bit] NULL,
	[ReworkAsQualityLoss] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[InstallationID] 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].[tblStop]    Script Date: 2025-04-08 14:36:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblStop](
	[InstallationID] [int] NOT NULL,
	[Installation] [nvarchar](100) NOT NULL,
	[MeasurePointID] [uniqueidentifier] NOT NULL,
	[MeasurePoint] [nvarchar](100) NOT NULL,
	[IntervalStart] [datetime] NOT NULL,
	[IntervalEnd] [datetime] NULL,
	[TotalStopDuration] [bigint] NULL,
	[StopReason] [nvarchar](300) NULL,
	[Categories] [nvarchar](300) NULL,
	[Station] [nvarchar](100) NULL,
	[Comment] [nvarchar](1000) NULL,
	[ShiftID] [nvarchar](1000) NULL,
PRIMARY KEY CLUSTERED 
(
	[InstallationID] ASC,
	[MeasurePointID] ASC,
	[IntervalStart] 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].[tblStopOccasion]    Script Date: 2025-04-08 14:36:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblStopOccasion](
	[InstallationID] [int] NOT NULL,
	[Installation] [nvarchar](100) NOT NULL,
	[MeasurePointID] [uniqueidentifier] NOT NULL,
	[MeasurePoint] [nvarchar](100) NOT NULL,
	[IntervalStart] [datetime] NOT NULL,
	[IntervalEnd] [datetime] NULL,
	[Duration] [bigint] NULL,
	[TotalStopDuration] [bigint] NULL,
	[ScheduledDuration] [decimal](10, 2) NULL,
	[ProductiveDuration] [decimal](10, 2) NULL,
	[StopQuantity] [bigint] NULL,
	[NumberOfProductiveOccasions] [bigint] NULL,
	[MTBF] [decimal](10, 2) NULL,
	[MTTR] [decimal](10, 2) NULL,
PRIMARY KEY CLUSTERED 
(
	[InstallationID] ASC,
	[MeasurePointID] ASC,
	[IntervalStart] 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].[tblWorktime]    Script Date: 2025-04-08 14:36:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblWorktime](
	[InstallationID] [int] NOT NULL,
	[Installation] [nvarchar](100) NOT NULL,
	[MeasurePointID] [uniqueidentifier] NOT NULL,
	[MeasurePoint] [nvarchar](100) NOT NULL,
	[IntervalStart] [datetime] NOT NULL,
	[IntervalEnd] [datetime] NULL,
	[ShiftID] [uniqueidentifier] NULL,
	[Shift] [nvarchar](100) NULL,
	[ProductionOrder] [nvarchar](50) NULL,
	[Article] [nvarchar](50) NULL,
	[ArticleName] [nvarchar](100) NULL,
	[ArticleType] [nvarchar](100) NULL,
	[TotalDuration] [decimal](10, 2) NULL,
	[ScheduledDuration] [decimal](10, 2) NULL,
	[ExcludedDuration] [decimal](10, 2) NULL,
	[StopDuration] [decimal](10, 2) NULL,
	[SetupStopDuration] [decimal](10, 2) NULL,
	[NoWorkTimeStopDuration] [decimal](10, 2) NULL,
	[MicroStopDuration] [decimal](10, 2) NULL,
	[ProductionTimeDuration] [decimal](10, 2) NULL,
	[UsedEffectiveTime] [decimal](10, 2) NULL,
	[ReworkedEffectiveTime] [decimal](10, 2) NULL,
	[ScrappedEffectiveTime] [decimal](10, 2) NULL,
	[OptimalProducedUnitsNoMicroStop] [decimal](10, 2) NULL,
	[OptimalProducedUnits] [decimal](10, 2) NULL,
	[ProducedUnits] [decimal](10, 2) NULL,
	[ReworkedUnits] [decimal](10, 2) NULL,
	[ScrappedUnits] [decimal](10, 2) NULL,
	[ApprovedUnits] [decimal](10, 2) NULL,
PRIMARY KEY CLUSTERED 
(
	[InstallationID] ASC,
	[MeasurePointID] ASC,
	[IntervalStart] 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:  StoredProcedure [dbo].[sp_LoadSettings]    Script Date: 2025-04-08 14:36:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

	CREATE PROCEDURE [dbo].[sp_LoadSettings] (@datatable udtSetting READONLY)
	AS
	BEGIN
		SET NOCOUNT ON;
		SET XACT_ABORT ON
		BEGIN TRANSACTION
	
		MERGE dbo.tblSetting AS target  
		USING @datatable AS source  
		ON (target.InstallationID = source.InstallationID)  
			WHEN  MATCHED THEN  
				UPDATE SET	[CalculatePerformanceFromWeightedProducedUnits] = source.[CalculatePerformanceFromWeightedProducedUnits],
							[MicrostopAsPerformanceLoss] = source.[MicrostopAsPerformanceLoss],
							[ReworkAsQualityLoss] = source.[ReworkAsQualityLoss]
			WHEN NOT MATCHED THEN  
				INSERT ([InstallationID], 
						[Installation], 
						[CalculatePerformanceFromWeightedProducedUnits], 
						[MicrostopAsPerformanceLoss], 
						[ReworkAsQualityLoss])  
				VALUES (source.[InstallationID], 
						source.[Installation], 
						source.[CalculatePerformanceFromWeightedProducedUnits], 
						source.[MicrostopAsPerformanceLoss], 
						source.[ReworkAsQualityLoss]);
	
		COMMIT TRANSACTION
	END
	
GO
/****** Object:  StoredProcedure [dbo].[sp_LoadStopOccasions]    Script Date: 2025-04-08 14:36:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

	CREATE PROCEDURE [dbo].[sp_LoadStopOccasions] (@datatable udtStopOccasion READONLY)
	AS
	BEGIN
		SET NOCOUNT ON;
		SET XACT_ABORT ON
		BEGIN TRANSACTION
	
		MERGE dbo.tblStopOccasion AS target  
		USING @datatable AS source  
		ON (target.[InstallationID] = source.[InstallationID] 
			AND target.[MeasurePointID] = CONVERT(uniqueidentifier, source.[MeasurePointID]) 
			AND target.[IntervalStart] = source.[IntervalStart])  
		
			WHEN MATCHED THEN  
				UPDATE SET [Installation] = source.[Installation],
						[MeasurePoint] = source.[MeasurePoint],
						[IntervalEnd] = source.[IntervalEnd],
						[Duration] = source.[Duration],
						[TotalStopDuration] = source.[TotalStopDuration],
						[ScheduledDuration] = source.[ScheduledDuration],
						[ProductiveDuration] = source.[ProductiveDuration],
						[StopQuantity] = source.[StopQuantity],
						[NumberOfProductiveOccasions] = source.[NumberOfProductiveOccasions],
						[MTBF] = source.[MTBF],
						[MTTR] = source.[MTTR]

			WHEN NOT MATCHED THEN  
				INSERT ([InstallationID], 
					[Installation], 
					[MeasurePointID], 
					[MeasurePoint], 
					[IntervalStart], 
					[IntervalEnd], 
					[Duration],
					[TotalStopDuration], 
					[ScheduledDuration], 
					[ProductiveDuration],
					[StopQuantity], 
					[NumberOfProductiveOccasions],
					[MTBF],
					[MTTR])  
				VALUES (source.[InstallationID], 
					source.[Installation], 
					CONVERT(uniqueidentifier,source.[MeasurePointID]), 
					source.[MeasurePoint], 
					source.[IntervalStart],
					source.[IntervalEnd], 
					source.[Duration],
					source.[TotalStopDuration], 
					source.[ScheduledDuration], 
					source.[ProductiveDuration],
					source.[StopQuantity], 
					source.[NumberOfProductiveOccasions], 
					source.[MTBF],
					source.[MTTR]);
	
		COMMIT TRANSACTION
	END
	
GO
/****** Object:  StoredProcedure [dbo].[sp_LoadStops]    Script Date: 2025-04-08 14:36:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_LoadStops] (@datatable udtStop READONLY)
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	BEGIN TRANSACTION;

	BEGIN TRY
		-- Step 1: Delete any records not in the latest export
		WITH StopTemp AS (
			SELECT InstallationID, MIN(IntervalStart) AS StartTime
			FROM @datatable
			GROUP BY InstallationID
		)

		DELETE target
		FROM dbo.tblStop AS target
		LEFT JOIN @datatable AS source
			ON source.[InstallationID] = target.[InstallationID]
			AND source.[MeasurePointID] = target.[MeasurePointID]
			AND source.[IntervalStart] = target.[IntervalStart]
		JOIN StopTemp AS temp
			ON temp.[InstallationID] = target.[InstallationID]
		WHERE source.InstallationID IS NULL
		  AND target.[IntervalStart] >= temp.StartTime;


		-- Step 2: Upsert the new data
		MERGE dbo.tblStop WITH (HOLDLOCK) AS target  
		USING @datatable AS source  
		ON (target.[InstallationID] = source.[InstallationID] 
			AND target.[MeasurePointID] = CONVERT(uniqueidentifier,source.[MeasurePointID]) 
			AND target.[IntervalStart] = source.[IntervalStart])  
		
			WHEN MATCHED THEN  
				UPDATE SET [Installation] = source.[Installation],
						[MeasurePoint] = source.[MeasurePoint],
						[IntervalEnd] = source.[IntervalEnd],
						[TotalStopDuration] = source.[TotalStopDuration],
						[StopReason] = source.[StopReason],
						[Categories] = source.[Categories],
						[Station] = source.[Station],
						[Comment] = source.[Comment],
						[ShiftID] = source.[ShiftID]

			WHEN NOT MATCHED THEN  
				INSERT ([InstallationID], 
					[Installation], 
					[MeasurePointID], 
					[MeasurePoint], 
					[IntervalStart], 
					[IntervalEnd], 
					[TotalStopDuration], 
					[StopReason], 
					[Categories],
					[Station], 
					[Comment],
					[ShiftID])  
				VALUES (source.[InstallationID], 
					source.[Installation], 
					CONVERT(uniqueidentifier,source.[MeasurePointID]), 
					source.[MeasurePoint], 
					source.[IntervalStart],
					source.[IntervalEnd], 
					source.[TotalStopDuration], 
					source.[StopReason], 
					source.[Categories],
					source.[Station], 
					source.[Comment], 
					source.[ShiftID]);
		COMMIT TRANSACTION;
	END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[sp_LoadWorktimes]    Script Date: 2025-04-08 14:36:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_LoadWorktimes] (@datatable udtWorktime READONLY)
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	BEGIN TRANSACTION;

	BEGIN TRY
		-- Step 1: Delete any records not in the latest export
		WITH WorktimeTemp AS (
			SELECT InstallationID, MIN(IntervalStart) AS StartTime
			FROM @datatable
			GROUP BY InstallationID
		)

		DELETE target
		FROM dbo.tblWorktime AS target
		LEFT JOIN @datatable AS source
			ON source.[InstallationID] = target.[InstallationID]
			AND source.[MeasurePointID] = target.[MeasurePointID]
			AND source.[IntervalStart] = target.[IntervalStart]
		JOIN WorktimeTemp AS temp
			ON temp.[InstallationID] = target.[InstallationID]
		WHERE source.InstallationID IS NULL
		  AND target.[IntervalStart] >= temp.StartTime;


		-- Step 2: Upsert the new data
		MERGE dbo.tblWorktime WITH (HOLDLOCK) AS target  
		USING @datatable AS source  
			ON (target.[InstallationID] = source.[InstallationID] 
				AND target.[MeasurePointID] = CONVERT(uniqueidentifier,source.[MeasurePointID]) 
				AND target.[IntervalStart] = source.[IntervalStart])  
		
			WHEN MATCHED THEN  
				UPDATE SET [Installation] = source.[Installation],
						[MeasurePoint] = source.[MeasurePoint],
						[IntervalEnd] = source.[IntervalEnd],
						[ShiftID] = source.[ShiftID],
						[Shift] = source.[Shift],
						[ProductionOrder] = source.[ProductionOrder],
						[Article] = source.[Article],
						[ArticleName] = source.[ArticleName],
						[ArticleType] = source.[ArticleType],
						[TotalDuration] = source.[TotalDuration],
						[ScheduledDuration] = source.[ScheduledDuration],
						[ExcludedDuration] = source.[ExcludedDuration],
						[StopDuration] = source.[StopDuration],
						[SetupStopDuration] = source.[SetupStopDuration],
						[NoWorkTimeStopDuration] = source.[NoWorkTimeStopDuration],
						[MicroStopDuration] = source.[MicroStopDuration],
						[ProductionTimeDuration] = source.[ProductionTimeDuration],
						[UsedEffectiveTime] = source.[UsedEffectiveTime],
						[ReworkedEffectiveTime] = source.[ReworkedEffectiveTime],
						[ScrappedEffectiveTime] = source.[ScrappedEffectiveTime],
						[OptimalProducedUnitsNoMicroStop] = source.[OptimalProducedUnitsNoMicroStop],
						[OptimalProducedUnits] = source.[OptimalProducedUnits],
						[ProducedUnits] = source.[ProducedUnits],
						[ReworkedUnits] = source.[ReworkedUnits],
						[ScrappedUnits] = source.[ScrappedUnits],
						[ApprovedUnits] = source.[ApprovedUnits]
					
			WHEN NOT MATCHED THEN  
				INSERT ([InstallationID], 
					[Installation], 
					[MeasurePointID], 
					[MeasurePoint], 
					[IntervalStart], 
					[IntervalEnd],
					[ShiftID],
					[Shift],
					[ProductionOrder],
					[Article],
					[ArticleName],
					[ArticleType],
					[TotalDuration],
					[ScheduledDuration],
					[ExcludedDuration],
					[StopDuration],
					[SetupStopDuration],
					[NoWorkTimeStopDuration],
					[MicroStopDuration],
					[ProductionTimeDuration],
					[UsedEffectiveTime],
					[ReworkedEffectiveTime],
					[ScrappedEffectiveTime],
					[OptimalProducedUnitsNoMicroStop],
					[OptimalProducedUnits],
					[ProducedUnits],
					[ReworkedUnits],
					[ScrappedUnits],
					[ApprovedUnits]
					)  
				VALUES (source.[InstallationID], 
					source.[Installation], 
					CONVERT(uniqueidentifier,source.[MeasurePointID]), 
					source.[MeasurePoint], 
					source.[IntervalStart],
					source.[IntervalEnd], 
					source.[ShiftID],
					source.[Shift],
					source.[ProductionOrder],
					source.[Article],
					source.[ArticleName],
					source.[ArticleType],
					source.[TotalDuration],
					source.[ScheduledDuration],
					source.[ExcludedDuration],
					source.[StopDuration],
					source.[SetupStopDuration],
					source.[NoWorkTimeStopDuration],
					source.[MicroStopDuration],
					source.[ProductionTimeDuration],
					source.[UsedEffectiveTime],
					source.[ReworkedEffectiveTime],
					source.[ScrappedEffectiveTime],
					source.[OptimalProducedUnitsNoMicroStop],
					source.[OptimalProducedUnits],
					source.[ProducedUnits],
					source.[ReworkedUnits],
					source.[ScrappedUnits],
					source.[ApprovedUnits]
					);
		COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH
END
GO