TSQL Dynamic columns and SUM at the end

Question

TSQL Dynamic columns and SUM at the end

hexdecimal1989

SSC Rookie

Points: 38
More actions
March 20, 2016 at 4:38 am

#325082

Hi!
I have my tsql dynamic query which is functional and it works.
However, I dont know how to add SUM at the end of each row
Here is my main datatable
also I called several joins for other tables. I hope you will understand my query.
I've created dynamic columns based on input dates. I just want to add TOTAL or SUM column at the end of the last date, to COUNT for each row how many is for all dates in range EventTypeID.
DECLARE
@cols AS NVARCHAR(MAX),
@selcols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@startdate datetime,
@enddate datetime,
@paramdef nvarchar(max)
SET @startdate = '2013-02-01'
SET @enddate = '2013-05-10';
SET @paramdef = '@startdate datetime, @enddate datetime';
SELECT
@selcols = STUFF((SELECT ','+'ISNULL(' + QUOTENAME(convert(varchar(10), [Date], 120)) + ', 0) AS ' + QUOTENAME(convert(varchar(10), [Date], 120))
FROM AdeccoView
--where datein > @startdate
-- and datein <= @enddate
GROUP BY [Date]
ORDER BY [Date]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SELECT
@cols = STUFF((SELECT ',' + QUOTENAME(convert(varchar(10), [Date], 120))
FROM AdeccoView
--where datein > @startdate
-- and datein <= @enddate
GROUP BY [Date]
ORDER BY [Date]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @query = N'SELECT CreatedBy,Name, Surname,EventTypeID,Event, '+ @selcols + '
from
(
select av.EventTypeID,av.CreatedBy,emp.Name,emp.Surname,Event,
convert(varchar(10), [Date], 120) [Date],
row_number() over(PARTITION BY [date],av.EventTypeID,av.CreatedBy order BY av.EventTypeID,av.CreatedBy ) m
from AdeccoView av
left join EventType et on et.EventTypeID = av.EventTypeID
left join Employee emp on av.CreatedBy = emp.EmployeeD
--where datein > @startdate
-- and datein <= @enddate
) x
pivot
(
max(m)
for [Date] in ('+@cols+')
) p '
exec sp_executesql @query, @paramdef, @startdate = @startdate, @enddate =@enddate;
Thanks.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182950 More actions · Answer 1

Can you post the DDL for the AdeccoView table and some sample data as an insert statement, makes answering the question much easier.

😎

hexdecimal1989 SSC Rookie Points: 38 More actions · Answer 2

USE [SalesManagement]

GO

/****** Object: Table [dbo].[AdeccoView] Script Date: 20/03/2016 11:53:27 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[AdeccoView](

[ViewID] [int] IDENTITY(1,1) NOT NULL,

[Date] [date] NOT NULL,

[ClientID] [int] NOT NULL,

[CreatedTimeStamp] [datetime] NOT NULL,

[CreatedBy] [int] NOT NULL,

[ModifiedTimeStamp] [datetime] NOT NULL,

[ModifiedBy] [int] NOT NULL,

[EventTypeID] [int] NOT NULL,

CONSTRAINT [PK_View] PRIMARY KEY CLUSTERED

(

[ViewID] 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

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_Client] FOREIGN KEY([ClientID])

REFERENCES [dbo].[Client] ([ClientID])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_Client]

GO

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_Employee] FOREIGN KEY([ModifiedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_Employee]

GO

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_Employee1] FOREIGN KEY([CreatedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_Employee1]

GO

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_EventType] FOREIGN KEY([EventTypeID])

REFERENCES [dbo].[EventType] ([EventTypeID])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_EventType]

GO

Also, here is the code for EventType:

USE [SalesManagement]

GO

/****** Object: Table [dbo].[EventType] Script Date: 20/03/2016 11:55:06 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[EventType](

[EventTypeID] [int] IDENTITY(1,1) NOT NULL,

[Event] [nvarchar](50) NOT NULL,

[Sequence] [int] NOT NULL,

[CreatedTimeStamp] [datetime] NOT NULL,

[CreatedBy] [int] NOT NULL,

[ModifiedTimeStamp] [datetime] NOT NULL,

[ModifiedBy] [int] NOT NULL,

CONSTRAINT [PK_EventType] PRIMARY KEY CLUSTERED

(

[EventTypeID] 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

ALTER TABLE [dbo].[EventType] WITH CHECK ADD CONSTRAINT [FK_EventType_Employee1] FOREIGN KEY([CreatedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[EventType] CHECK CONSTRAINT [FK_EventType_Employee1]

GO

ALTER TABLE [dbo].[EventType] WITH CHECK ADD CONSTRAINT [FK_EventType_Employee2] FOREIGN KEY([ModifiedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[EventType] CHECK CONSTRAINT [FK_EventType_Employee2]

GO

Maybe you need to remove FK keys for other tables, because there are irrelevant.

Thanks in advance

Eirikur Eiriksson SSC Guru Points: 182950 More actions · Answer 3

Thanks for this, now just one more thing, do you have some sample data as an insert statement for the two tables, safes me having to speculate what could/should be there?

😎

hexdecimal1989 SSC Rookie Points: 38 More actions · Answer 4

Of course! Sorry!

USE [SalesManagement]

GO

/****** Object: Table [dbo].[AdeccoView] Script Date: 20/03/2016 1:33:40 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[AdeccoView](

[ViewID] [int] IDENTITY(1,1) NOT NULL,

[Date] [date] NOT NULL,

[ClientID] [int] NOT NULL,

[CreatedTimeStamp] [datetime] NOT NULL,

[CreatedBy] [int] NOT NULL,

[ModifiedTimeStamp] [datetime] NOT NULL,

[ModifiedBy] [int] NOT NULL,

[EventTypeID] [int] NOT NULL,

CONSTRAINT [PK_View] PRIMARY KEY CLUSTERED

(

[ViewID] 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].[Client] Script Date: 20/03/2016 1:33:40 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Client](

[ClientID] [int] IDENTITY(1,1) NOT NULL,

[ClientName] [nvarchar](50) NOT NULL,

[CreatedTimeStamp] [datetime] NOT NULL,

[CreatedBy] [int] NOT NULL,

[ModifiedTimeStamp] [datetime] NOT NULL,

[ModifiedBy] [int] NOT NULL,

CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED

(

[ClientID] 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].[EventType] Script Date: 20/03/2016 1:33:40 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[EventType](

[EventTypeID] [int] IDENTITY(1,1) NOT NULL,

[Event] [nvarchar](50) NOT NULL,

[Sequence] [int] NOT NULL,

[CreatedTimeStamp] [datetime] NOT NULL,

[CreatedBy] [int] NOT NULL,

[ModifiedTimeStamp] [datetime] NOT NULL,

[ModifiedBy] [int] NOT NULL,

CONSTRAINT [PK_EventType] PRIMARY KEY CLUSTERED

(

[EventTypeID] 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].[AdeccoView] ON

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (118, CAST(N'2016-03-02' AS Date), 10, CAST(N'2016-03-19 13:41:58.627' AS DateTime), 2, CAST(N'2016-03-19 13:41:58.627' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (119, CAST(N'2016-03-02' AS Date), 21, CAST(N'2016-03-19 13:42:08.103' AS DateTime), 2, CAST(N'2016-03-19 13:42:08.103' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (120, CAST(N'2016-03-02' AS Date), 15, CAST(N'2016-03-19 13:48:40.990' AS DateTime), 2, CAST(N'2016-03-19 13:48:40.990' AS DateTime), 2, 9)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (121, CAST(N'2016-03-03' AS Date), 9, CAST(N'2016-03-19 13:48:47.910' AS DateTime), 2, CAST(N'2016-03-19 13:48:47.910' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (122, CAST(N'2016-03-03' AS Date), 9, CAST(N'2016-03-19 13:48:51.930' AS DateTime), 2, CAST(N'2016-03-19 13:48:51.930' AS DateTime), 2, 6)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (123, CAST(N'2016-03-03' AS Date), 23, CAST(N'2016-03-19 13:48:56.447' AS DateTime), 2, CAST(N'2016-03-19 13:48:56.447' AS DateTime), 2, 9)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (124, CAST(N'2016-03-08' AS Date), 12, CAST(N'2016-03-19 13:49:03.617' AS DateTime), 2, CAST(N'2016-03-19 13:49:03.617' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (125, CAST(N'2016-03-25' AS Date), 11, CAST(N'2016-03-19 13:49:09.367' AS DateTime), 2, CAST(N'2016-03-19 13:49:09.367' AS DateTime), 2, 6)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (126, CAST(N'2016-03-06' AS Date), 22, CAST(N'2016-03-19 13:49:31.833' AS DateTime), 2, CAST(N'2016-03-19 13:49:31.833' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (127, CAST(N'2016-03-04' AS Date), 12, CAST(N'2016-03-19 13:49:36.703' AS DateTime), 2, CAST(N'2016-03-19 13:49:36.703' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (128, CAST(N'2016-03-09' AS Date), 9, CAST(N'2016-03-19 13:49:45.820' AS DateTime), 2, CAST(N'2016-03-19 13:49:45.820' AS DateTime), 2, 9)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (129, CAST(N'2016-03-10' AS Date), 8, CAST(N'2016-03-19 13:50:01.713' AS DateTime), 4, CAST(N'2016-03-19 13:50:01.713' AS DateTime), 4, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (130, CAST(N'2016-03-08' AS Date), 11, CAST(N'2016-03-19 13:50:06.840' AS DateTime), 4, CAST(N'2016-03-19 13:50:06.840' AS DateTime), 4, 7)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (131, CAST(N'2016-03-10' AS Date), 7, CAST(N'2016-03-19 13:50:11.787' AS DateTime), 4, CAST(N'2016-03-19 13:50:11.787' AS DateTime), 4, 9)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (132, CAST(N'2016-03-07' AS Date), 12, CAST(N'2016-03-19 13:50:16.997' AS DateTime), 4, CAST(N'2016-03-19 13:50:16.997' AS DateTime), 4, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (133, CAST(N'2016-03-04' AS Date), 7, CAST(N'2016-03-19 13:50:22.247' AS DateTime), 4, CAST(N'2016-03-19 13:50:22.247' AS DateTime), 4, 7)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (134, CAST(N'2016-03-02' AS Date), 9, CAST(N'2016-03-19 13:52:28.807' AS DateTime), 3, CAST(N'2016-03-19 13:52:28.807' AS DateTime), 3, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (135, CAST(N'2016-03-04' AS Date), 11, CAST(N'2016-03-19 13:52:33.527' AS DateTime), 3, CAST(N'2016-03-19 13:52:33.527' AS DateTime), 3, 7)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (136, CAST(N'2016-03-13' AS Date), 9, CAST(N'2016-03-19 13:52:38.730' AS DateTime), 3, CAST(N'2016-03-19 13:52:38.730' AS DateTime), 3, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (137, CAST(N'2016-03-04' AS Date), 14, CAST(N'2016-03-19 13:52:47.960' AS DateTime), 3, CAST(N'2016-03-19 13:52:47.960' AS DateTime), 3, 7)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (138, CAST(N'2016-03-08' AS Date), 11, CAST(N'2016-03-19 13:52:53.200' AS DateTime), 3, CAST(N'2016-03-19 13:52:53.200' AS DateTime), 3, 9)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (139, CAST(N'2016-03-07' AS Date), 9, CAST(N'2016-03-19 13:52:58.203' AS DateTime), 3, CAST(N'2016-03-19 13:52:58.203' AS DateTime), 3, 10)

SET IDENTITY_INSERT [dbo].[AdeccoView] OFF

SET IDENTITY_INSERT [dbo].[Client] ON

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (5, N'O zone', CAST(N'2016-01-15 12:27:50.000' AS DateTime), 2, CAST(N'2016-01-18 09:36:06.590' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (6, N'KPMG', CAST(N'2016-01-15 12:28:00.283' AS DateTime), 2, CAST(N'2016-01-15 12:28:00.283' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (7, N'Dijamant', CAST(N'2016-01-15 12:29:11.007' AS DateTime), 2, CAST(N'2016-01-15 12:29:11.007' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (8, N'Ginger', CAST(N'2016-01-15 12:29:15.507' AS DateTime), 2, CAST(N'2016-01-15 12:29:15.507' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (9, N'IPS Energy', CAST(N'2016-01-15 12:29:20.190' AS DateTime), 2, CAST(N'2016-01-15 12:29:20.190' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (10, N'Perutnina Ptuj', CAST(N'2016-01-15 12:29:33.507' AS DateTime), 2, CAST(N'2016-01-15 12:29:33.507' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (11, N'Delphi', CAST(N'2016-01-15 12:29:39.003' AS DateTime), 2, CAST(N'2016-01-15 12:29:39.003' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (12, N'Sonex', CAST(N'2016-01-15 12:29:44.693' AS DateTime), 2, CAST(N'2016-01-15 12:29:44.693' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (14, N'Betware', CAST(N'2016-01-15 12:29:55.357' AS DateTime), 2, CAST(N'2016-01-15 12:29:55.357' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (15, N'Gefco', CAST(N'2016-01-15 12:30:03.220' AS DateTime), 2, CAST(N'2016-01-15 12:30:03.220' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (17, N'Cineplexx', CAST(N'2016-01-15 12:30:15.090' AS DateTime), 2, CAST(N'2016-01-15 12:30:15.090' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (18, N'Europapier', CAST(N'2016-01-15 12:30:22.317' AS DateTime), 2, CAST(N'2016-01-15 12:30:22.317' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (20, N'Auto One test5555', CAST(N'2016-03-01 16:49:57.000' AS DateTime), 2, CAST(N'2016-03-01 17:28:22.223' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (21, N'Sonex X 2', CAST(N'2016-01-15 12:30:51.053' AS DateTime), 2, CAST(N'2016-01-15 12:30:51.053' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (22, N'Stada IT Solutions', CAST(N'2016-01-15 12:31:03.990' AS DateTime), 2, CAST(N'2016-01-15 12:31:03.990' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (23, N'Nacionalna logistika', CAST(N'2016-01-29 10:11:44.497' AS DateTime), 4, CAST(N'2016-01-29 10:11:44.497' AS DateTime), 4)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (24, N'stefanns', CAST(N'2016-03-16 20:38:53.230' AS DateTime), 2, CAST(N'2016-03-16 20:38:53.230' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (25, N'OPAAAA', CAST(N'2016-03-16 20:39:20.067' AS DateTime), 2, CAST(N'2016-03-16 20:39:20.067' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (26, N'stefadsdsds', CAST(N'2016-03-16 20:47:30.800' AS DateTime), 2, CAST(N'2016-03-16 20:47:30.800' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (28, N'12871y7qdwhbjh', CAST(N'2016-03-16 20:51:57.097' AS DateTime), 2, CAST(N'2016-03-16 20:51:57.097' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (29, N'Jikaaa', CAST(N'2016-03-16 20:55:00.020' AS DateTime), 2, CAST(N'2016-03-16 20:55:00.020' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (30, N'dfsfsd', CAST(N'2016-03-17 20:53:07.257' AS DateTime), 2, CAST(N'2016-03-17 20:53:07.257' AS DateTime), 2)

SET IDENTITY_INSERT [dbo].[Client] OFF

SET IDENTITY_INSERT [dbo].[EventType] ON

INSERT [dbo].[EventType] ([EventTypeID], [Event], [Sequence], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (6, N'COMMERCIAL VISITS', 1, CAST(N'2016-01-14 16:37:06.697' AS DateTime), 2, CAST(N'2016-01-14 16:37:06.697' AS DateTime), 2)

INSERT [dbo].[EventType] ([EventTypeID], [Event], [Sequence], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (7, N'PROSPECTS VISITS', 1, CAST(N'2016-01-14 16:37:30.107' AS DateTime), 2, CAST(N'2016-01-14 16:37:30.107' AS DateTime), 2)

INSERT [dbo].[EventType] ([EventTypeID], [Event], [Sequence], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (9, N'PROPOSALS MADE', 2, CAST(N'2016-01-14 16:38:03.410' AS DateTime), 2, CAST(N'2016-01-14 16:38:03.410' AS DateTime), 2)

INSERT [dbo].[EventType] ([EventTypeID], [Event], [Sequence], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (10, N'PROPOSALS ACCEPTED', 2, CAST(N'2016-01-14 16:38:21.450' AS DateTime), 2, CAST(N'2016-01-14 16:38:21.450' AS DateTime), 2)

INSERT [dbo].[EventType] ([EventTypeID], [Event], [Sequence], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (17, N'sgsdgsd', 123, CAST(N'2016-03-17 20:53:22.493' AS DateTime), 2, CAST(N'2016-03-17 20:53:22.493' AS DateTime), 2)

SET IDENTITY_INSERT [dbo].[EventType] OFF

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_Client] FOREIGN KEY([ClientID])

REFERENCES [dbo].[Client] ([ClientID])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_Client]

GO

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_Employee] FOREIGN KEY([ModifiedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_Employee]

GO

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_Employee1] FOREIGN KEY([CreatedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_Employee1]

GO

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_EventType] FOREIGN KEY([EventTypeID])

REFERENCES [dbo].[EventType] ([EventTypeID])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_EventType]

GO

ALTER TABLE [dbo].[Client] WITH CHECK ADD CONSTRAINT [FK_Client_Employee] FOREIGN KEY([CreatedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [FK_Client_Employee]

GO

ALTER TABLE [dbo].[Client] WITH CHECK ADD CONSTRAINT [FK_Client_Employee1] FOREIGN KEY([CreatedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [FK_Client_Employee1]

GO

ALTER TABLE [dbo].[Client] WITH CHECK ADD CONSTRAINT [FK_Client_Employee2] FOREIGN KEY([ModifiedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [FK_Client_Employee2]

GO

ALTER TABLE [dbo].[EventType] WITH CHECK ADD CONSTRAINT [FK_EventType_Employee1] FOREIGN KEY([CreatedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[EventType] CHECK CONSTRAINT [FK_EventType_Employee1]

GO

ALTER TABLE [dbo].[EventType] WITH CHECK ADD CONSTRAINT [FK_EventType_Employee2] FOREIGN KEY([ModifiedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[EventType] CHECK CONSTRAINT [FK_EventType_Employee2]

GO

[\code]

Thanks.

hexdecimal1989 SSC Rookie Points: 38 More actions · Answer 5

Of course! Sorry!

USE [SalesManagement]

GO

/****** Object: Table [dbo].[AdeccoView] Script Date: 20/03/2016 1:33:40 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[AdeccoView](

[ViewID] [int] IDENTITY(1,1) NOT NULL,

[Date] [date] NOT NULL,

[ClientID] [int] NOT NULL,

[CreatedTimeStamp] [datetime] NOT NULL,

[CreatedBy] [int] NOT NULL,

[ModifiedTimeStamp] [datetime] NOT NULL,

[ModifiedBy] [int] NOT NULL,

[EventTypeID] [int] NOT NULL,

CONSTRAINT [PK_View] PRIMARY KEY CLUSTERED

(

[ViewID] 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].[Client] Script Date: 20/03/2016 1:33:40 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Client](

[ClientID] [int] IDENTITY(1,1) NOT NULL,

[ClientName] [nvarchar](50) NOT NULL,

[CreatedTimeStamp] [datetime] NOT NULL,

[CreatedBy] [int] NOT NULL,

[ModifiedTimeStamp] [datetime] NOT NULL,

[ModifiedBy] [int] NOT NULL,

CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED

(

[ClientID] 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].[EventType] Script Date: 20/03/2016 1:33:40 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[EventType](

[EventTypeID] [int] IDENTITY(1,1) NOT NULL,

[Event] [nvarchar](50) NOT NULL,

[Sequence] [int] NOT NULL,

[CreatedTimeStamp] [datetime] NOT NULL,

[CreatedBy] [int] NOT NULL,

[ModifiedTimeStamp] [datetime] NOT NULL,

[ModifiedBy] [int] NOT NULL,

CONSTRAINT [PK_EventType] PRIMARY KEY CLUSTERED

(

[EventTypeID] 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].[AdeccoView] ON

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (118, CAST(N'2016-03-02' AS Date), 10, CAST(N'2016-03-19 13:41:58.627' AS DateTime), 2, CAST(N'2016-03-19 13:41:58.627' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (119, CAST(N'2016-03-02' AS Date), 21, CAST(N'2016-03-19 13:42:08.103' AS DateTime), 2, CAST(N'2016-03-19 13:42:08.103' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (120, CAST(N'2016-03-02' AS Date), 15, CAST(N'2016-03-19 13:48:40.990' AS DateTime), 2, CAST(N'2016-03-19 13:48:40.990' AS DateTime), 2, 9)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (121, CAST(N'2016-03-03' AS Date), 9, CAST(N'2016-03-19 13:48:47.910' AS DateTime), 2, CAST(N'2016-03-19 13:48:47.910' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (122, CAST(N'2016-03-03' AS Date), 9, CAST(N'2016-03-19 13:48:51.930' AS DateTime), 2, CAST(N'2016-03-19 13:48:51.930' AS DateTime), 2, 6)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (123, CAST(N'2016-03-03' AS Date), 23, CAST(N'2016-03-19 13:48:56.447' AS DateTime), 2, CAST(N'2016-03-19 13:48:56.447' AS DateTime), 2, 9)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (124, CAST(N'2016-03-08' AS Date), 12, CAST(N'2016-03-19 13:49:03.617' AS DateTime), 2, CAST(N'2016-03-19 13:49:03.617' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (125, CAST(N'2016-03-25' AS Date), 11, CAST(N'2016-03-19 13:49:09.367' AS DateTime), 2, CAST(N'2016-03-19 13:49:09.367' AS DateTime), 2, 6)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (126, CAST(N'2016-03-06' AS Date), 22, CAST(N'2016-03-19 13:49:31.833' AS DateTime), 2, CAST(N'2016-03-19 13:49:31.833' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (127, CAST(N'2016-03-04' AS Date), 12, CAST(N'2016-03-19 13:49:36.703' AS DateTime), 2, CAST(N'2016-03-19 13:49:36.703' AS DateTime), 2, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (128, CAST(N'2016-03-09' AS Date), 9, CAST(N'2016-03-19 13:49:45.820' AS DateTime), 2, CAST(N'2016-03-19 13:49:45.820' AS DateTime), 2, 9)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (129, CAST(N'2016-03-10' AS Date), 8, CAST(N'2016-03-19 13:50:01.713' AS DateTime), 4, CAST(N'2016-03-19 13:50:01.713' AS DateTime), 4, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (130, CAST(N'2016-03-08' AS Date), 11, CAST(N'2016-03-19 13:50:06.840' AS DateTime), 4, CAST(N'2016-03-19 13:50:06.840' AS DateTime), 4, 7)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (131, CAST(N'2016-03-10' AS Date), 7, CAST(N'2016-03-19 13:50:11.787' AS DateTime), 4, CAST(N'2016-03-19 13:50:11.787' AS DateTime), 4, 9)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (132, CAST(N'2016-03-07' AS Date), 12, CAST(N'2016-03-19 13:50:16.997' AS DateTime), 4, CAST(N'2016-03-19 13:50:16.997' AS DateTime), 4, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (133, CAST(N'2016-03-04' AS Date), 7, CAST(N'2016-03-19 13:50:22.247' AS DateTime), 4, CAST(N'2016-03-19 13:50:22.247' AS DateTime), 4, 7)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (134, CAST(N'2016-03-02' AS Date), 9, CAST(N'2016-03-19 13:52:28.807' AS DateTime), 3, CAST(N'2016-03-19 13:52:28.807' AS DateTime), 3, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (135, CAST(N'2016-03-04' AS Date), 11, CAST(N'2016-03-19 13:52:33.527' AS DateTime), 3, CAST(N'2016-03-19 13:52:33.527' AS DateTime), 3, 7)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (136, CAST(N'2016-03-13' AS Date), 9, CAST(N'2016-03-19 13:52:38.730' AS DateTime), 3, CAST(N'2016-03-19 13:52:38.730' AS DateTime), 3, 10)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (137, CAST(N'2016-03-04' AS Date), 14, CAST(N'2016-03-19 13:52:47.960' AS DateTime), 3, CAST(N'2016-03-19 13:52:47.960' AS DateTime), 3, 7)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (138, CAST(N'2016-03-08' AS Date), 11, CAST(N'2016-03-19 13:52:53.200' AS DateTime), 3, CAST(N'2016-03-19 13:52:53.200' AS DateTime), 3, 9)

INSERT [dbo].[AdeccoView] ([ViewID], [Date], [ClientID], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy], [EventTypeID]) VALUES (139, CAST(N'2016-03-07' AS Date), 9, CAST(N'2016-03-19 13:52:58.203' AS DateTime), 3, CAST(N'2016-03-19 13:52:58.203' AS DateTime), 3, 10)

SET IDENTITY_INSERT [dbo].[AdeccoView] OFF

SET IDENTITY_INSERT [dbo].[Client] ON

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (5, N'O zone', CAST(N'2016-01-15 12:27:50.000' AS DateTime), 2, CAST(N'2016-01-18 09:36:06.590' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (6, N'KPMG', CAST(N'2016-01-15 12:28:00.283' AS DateTime), 2, CAST(N'2016-01-15 12:28:00.283' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (7, N'Dijamant', CAST(N'2016-01-15 12:29:11.007' AS DateTime), 2, CAST(N'2016-01-15 12:29:11.007' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (8, N'Ginger', CAST(N'2016-01-15 12:29:15.507' AS DateTime), 2, CAST(N'2016-01-15 12:29:15.507' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (9, N'IPS Energy', CAST(N'2016-01-15 12:29:20.190' AS DateTime), 2, CAST(N'2016-01-15 12:29:20.190' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (10, N'Perutnina Ptuj', CAST(N'2016-01-15 12:29:33.507' AS DateTime), 2, CAST(N'2016-01-15 12:29:33.507' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (11, N'Delphi', CAST(N'2016-01-15 12:29:39.003' AS DateTime), 2, CAST(N'2016-01-15 12:29:39.003' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (12, N'Sonex', CAST(N'2016-01-15 12:29:44.693' AS DateTime), 2, CAST(N'2016-01-15 12:29:44.693' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (14, N'Betware', CAST(N'2016-01-15 12:29:55.357' AS DateTime), 2, CAST(N'2016-01-15 12:29:55.357' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (15, N'Gefco', CAST(N'2016-01-15 12:30:03.220' AS DateTime), 2, CAST(N'2016-01-15 12:30:03.220' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (17, N'Cineplexx', CAST(N'2016-01-15 12:30:15.090' AS DateTime), 2, CAST(N'2016-01-15 12:30:15.090' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (18, N'Europapier', CAST(N'2016-01-15 12:30:22.317' AS DateTime), 2, CAST(N'2016-01-15 12:30:22.317' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (20, N'Auto One test5555', CAST(N'2016-03-01 16:49:57.000' AS DateTime), 2, CAST(N'2016-03-01 17:28:22.223' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (21, N'Sonex X 2', CAST(N'2016-01-15 12:30:51.053' AS DateTime), 2, CAST(N'2016-01-15 12:30:51.053' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (22, N'Stada IT Solutions', CAST(N'2016-01-15 12:31:03.990' AS DateTime), 2, CAST(N'2016-01-15 12:31:03.990' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (23, N'Nacionalna logistika', CAST(N'2016-01-29 10:11:44.497' AS DateTime), 4, CAST(N'2016-01-29 10:11:44.497' AS DateTime), 4)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (24, N'stefanns', CAST(N'2016-03-16 20:38:53.230' AS DateTime), 2, CAST(N'2016-03-16 20:38:53.230' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (25, N'OPAAAA', CAST(N'2016-03-16 20:39:20.067' AS DateTime), 2, CAST(N'2016-03-16 20:39:20.067' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (26, N'stefadsdsds', CAST(N'2016-03-16 20:47:30.800' AS DateTime), 2, CAST(N'2016-03-16 20:47:30.800' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (28, N'12871y7qdwhbjh', CAST(N'2016-03-16 20:51:57.097' AS DateTime), 2, CAST(N'2016-03-16 20:51:57.097' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (29, N'Jikaaa', CAST(N'2016-03-16 20:55:00.020' AS DateTime), 2, CAST(N'2016-03-16 20:55:00.020' AS DateTime), 2)

INSERT [dbo].[Client] ([ClientID], [ClientName], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (30, N'dfsfsd', CAST(N'2016-03-17 20:53:07.257' AS DateTime), 2, CAST(N'2016-03-17 20:53:07.257' AS DateTime), 2)

SET IDENTITY_INSERT [dbo].[Client] OFF

SET IDENTITY_INSERT [dbo].[EventType] ON

INSERT [dbo].[EventType] ([EventTypeID], [Event], [Sequence], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (6, N'COMMERCIAL VISITS', 1, CAST(N'2016-01-14 16:37:06.697' AS DateTime), 2, CAST(N'2016-01-14 16:37:06.697' AS DateTime), 2)

INSERT [dbo].[EventType] ([EventTypeID], [Event], [Sequence], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (7, N'PROSPECTS VISITS', 1, CAST(N'2016-01-14 16:37:30.107' AS DateTime), 2, CAST(N'2016-01-14 16:37:30.107' AS DateTime), 2)

INSERT [dbo].[EventType] ([EventTypeID], [Event], [Sequence], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (9, N'PROPOSALS MADE', 2, CAST(N'2016-01-14 16:38:03.410' AS DateTime), 2, CAST(N'2016-01-14 16:38:03.410' AS DateTime), 2)

INSERT [dbo].[EventType] ([EventTypeID], [Event], [Sequence], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (10, N'PROPOSALS ACCEPTED', 2, CAST(N'2016-01-14 16:38:21.450' AS DateTime), 2, CAST(N'2016-01-14 16:38:21.450' AS DateTime), 2)

INSERT [dbo].[EventType] ([EventTypeID], [Event], [Sequence], [CreatedTimeStamp], [CreatedBy], [ModifiedTimeStamp], [ModifiedBy]) VALUES (17, N'sgsdgsd', 123, CAST(N'2016-03-17 20:53:22.493' AS DateTime), 2, CAST(N'2016-03-17 20:53:22.493' AS DateTime), 2)

SET IDENTITY_INSERT [dbo].[EventType] OFF

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_Client] FOREIGN KEY([ClientID])

REFERENCES [dbo].[Client] ([ClientID])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_Client]

GO

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_Employee] FOREIGN KEY([ModifiedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_Employee]

GO

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_Employee1] FOREIGN KEY([CreatedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_Employee1]

GO

ALTER TABLE [dbo].[AdeccoView] WITH CHECK ADD CONSTRAINT [FK_AdeccoView_EventType] FOREIGN KEY([EventTypeID])

REFERENCES [dbo].[EventType] ([EventTypeID])

GO

ALTER TABLE [dbo].[AdeccoView] CHECK CONSTRAINT [FK_AdeccoView_EventType]

GO

ALTER TABLE [dbo].[Client] WITH CHECK ADD CONSTRAINT [FK_Client_Employee] FOREIGN KEY([CreatedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [FK_Client_Employee]

GO

ALTER TABLE [dbo].[Client] WITH CHECK ADD CONSTRAINT [FK_Client_Employee1] FOREIGN KEY([CreatedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [FK_Client_Employee1]

GO

ALTER TABLE [dbo].[Client] WITH CHECK ADD CONSTRAINT [FK_Client_Employee2] FOREIGN KEY([ModifiedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [FK_Client_Employee2]

GO

ALTER TABLE [dbo].[EventType] WITH CHECK ADD CONSTRAINT [FK_EventType_Employee1] FOREIGN KEY([CreatedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[EventType] CHECK CONSTRAINT [FK_EventType_Employee1]

GO

ALTER TABLE [dbo].[EventType] WITH CHECK ADD CONSTRAINT [FK_EventType_Employee2] FOREIGN KEY([ModifiedBy])

REFERENCES [dbo].[Employee] ([EmployeeD])

GO

ALTER TABLE [dbo].[EventType] CHECK CONSTRAINT [FK_EventType_Employee2]

GO

Thanks!

hexdecimal1989 SSC Rookie Points: 38 More actions · Answer 6