Is This View Optimised? What can I do to Improve it? Baby Steps

New_Creative · February 1, 2020, 9:40pm

I have this view working. Its taken a few tries to get it to display the information I need, however overall it works fine, its as quick as I would want it to be, the results are accurate and although it is significantly nested, all seems to work fine.

However, I am very new to this, and I feel the statement could be tidied significantly if I knew a little more. Perhaps I am wrong, but I think this may be a time bomb waiting in the wings if I leave it more or less as is.

I am planning to build a SP on this, now it is returning the records I want, but I thought before I take that step I would ask for this helpful community to review and suggest what I need to change and perhaps as importantly why?

SELECT        dbo.StockDelivered.DeliveryDate, dbo.Staffs.StaffName, ShtStock_1.Description, dbo.Site.SiteName, dbo.WaterBody.WBName, dbo.NZ
                             ((SELECT        PartNumber
                                 FROM            dbo.ShtStock
                                 WHERE        (ShtStockId = StockLink_1.AssociatedID)), ShtStock_1.PartNumber) AS PN, dbo.StockDelivered.Quantity / CONVERT(Float, dbo.NZ
                             ((SELECT        dbo.StockLink.Divisor
                                 FROM            dbo.StockLink INNER JOIN
                                                          dbo.ShtStock AS ShtStock_3 ON dbo.StockLink.AssociatedID = ShtStock_3.ShtStockId
                                 WHERE        (dbo.StockLink.StockID = ShtStock_1.ShtStockId)), 1)) AS Quant, dbo.ActiveTemp.JobNumber
FROM            dbo.Staffs INNER JOIN
                         dbo.StockDelivered ON dbo.Staffs.RegistrantEmail = dbo.StockDelivered.RegistrantEmail LEFT OUTER JOIN
                         dbo.Site INNER JOIN
                         dbo.ActiveTemp ON dbo.Site.SiteID = dbo.ActiveTemp.LookupID RIGHT OUTER JOIN
                         dbo.WaterBody ON dbo.Site.SiteID = dbo.WaterBody.SiteID ON dbo.StockDelivered.WaterBodyID = dbo.WaterBody.WaterBodyID FULL OUTER JOIN
                         dbo.ShtStock AS ShtStock_2 LEFT OUTER JOIN
                         dbo.StockLink AS StockLink_1 ON ShtStock_2.ShtStockId = StockLink_1.AssociatedID ON dbo.StockDelivered.ShtStockId = StockLink_1.StockID FULL OUTER JOIN
                         dbo.ShtStock AS ShtStock_1 ON dbo.StockDelivered.ShtStockId = ShtStock_1.ShtStockId
WHERE        (dbo.StockDelivered.DeliveryDate >=
                             (SELECT        MAX(ChangeDate) AS ActChangeDate
                               FROM            dbo.MonthChange
                               WHERE        (MonthChangeID =
                                                             (SELECT        MAX(MonthChangeID - 1) AS Expr1
                                                               FROM            dbo.MonthChange AS MonthChange_1)))) AND (dbo.StockDelivered.DeliveryDate <
                             (SELECT        MAX(ChangeDate) AS ChangeDate
                               FROM            dbo.MonthChange AS MonthChange_2)) AND (dbo.ActiveTemp.LookupTable = 2) AND (dbo.ActiveTemp.JobNumber = N'130')

On a directly related subject as it will be part of this statement...And I have no idea how to approach this.

There are some stock items that are listed by PartNumber in

CREATE TABLE [dbo].[ShtStockDrumDeposit](

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

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

 CONSTRAINT [PK_ShtStockDrumDeposit] PRIMARY KEY CLUSTERED

(

[StockDepositID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

These items and only these items with the part number in shtStockDrumDeposit I need to add a record to the output of the statement showing the drum charge as Drum Sell which is AssociatedStockID 145, for the identical details as the item with part number matching ShtStockDrumDeposit.

In practical terms:
Delivered
Part Number 0506CH6020 Job 135 Staff Scott Date 1/2/20

Then as there is a match for 0506CH6020 in ShtStockDrumDposit
Add
Part Number (As AssociatedID finds it) Job 135 Staff Scott Date 1/2/20

To the output from the original statement.

jotorre_riversidedpss.org · February 1, 2020, 11:09pm

Please provide create table(s) and insert script with sample data.

New_Creative · February 2, 2020, 12:36am

I hope I got all the tables...

CREATE TABLE [dbo].[ActiveTemp](
[JobID] [int] IDENTITY(1,1) NOT NULL,
[BookingDate] [datetime2](7) NOT NULL,
[Department] [int] NOT NULL,
[Division] [int] NULL,
[InvoiceDate] [datetime] NULL,
[InvoiceOptionID] [int] NULL,
[JobDescription] [nvarchar](900) NOT NULL,
[JobNumber] [nvarchar](max) NULL,
[OrderNumber] [nvarchar](120) NOT NULL,
[LookupTable] [int] NOT NULL,
[LookupID] [int] NOT NULL,
 CONSTRAINT [PK_ActiveTemp1] PRIMARY KEY CLUSTERED
([JobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Data in Excel

ShtStock - Links Barcodes to Stock Numbers as many of our products have many barcodes
GO
/****** Object: Table [dbo].[ShtStock] Script Date: 2/02/2020 7:52:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ShtStock](
[ShtStockId] [int] IDENTITY(1,1) NOT NULL,
[Barcode] nvarchar NULL,
[PartNumber] nvarchar NOT NULL,
[Description] nvarchar NOT NULL,

 CONSTRAINT [PK__ShtS__1B17B9B1711A71B9] PRIMARY KEY CLUSTERED
([ShtStockId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Typical Data

At moment ShtStock only contains 390 records. As we are only bar coding chemicals at this time. There are probably only 140 part numbers however as mentioned there are a lot of different bar codes on teh same products for reasons known only to the manufacturers...and they aren't saying but I digress.

GO
/****** Object: Table [dbo].[ShtStockDrumDeposit] Script Date: 2/02/2020 7:52:09 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ShtStockDrumDeposit](
[StockDepositID] [int] IDENTITY(1,1) NOT NULL,
[ShtPartNumber] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_ShtStockDrumDeposit] PRIMARY KEY CLUSTERED
([StockDepositID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This table only contains a few items. This is the complete table content.

GO
/****** Object: Table [dbo].[Site] Script Date: 2/02/2020 7:52:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Site](
[SiteID] [int] IDENTITY(1,1) NOT NULL,
[Clientid] [int] NOT NULL,
[SiteAddress] [nvarchar](520) NOT NULL,
[SiteName] [nvarchar](180) NOT NULL,
[SitePostCode] [nvarchar](8) NOT NULL,
[SiteSuburb] [nvarchar](80) NOT NULL,
[SiteTypeID] [int] NOT NULL,
 CONSTRAINT [PK_Site] PRIMARY KEY CLUSTERED
(
[SiteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Some of the Site data is confidential so I have made some edits in this data. Its unlikely any of these sites will align with the other records as these sites are not active.

GO
/****** Object: Table [dbo].[Staffs] Script Date: 2/02/2020 7:52:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Staffs](
[StaffID] [int] IDENTITY(1,1) NOT NULL,
[DivisionID] [int] NOT NULL,
[HireDate] [datetime2](7) NOT NULL,
[StaffName] [nvarchar](320) NOT NULL,
[TerminationDate] [datetime2](7) NULL,
[StaffMobileNumber] [varchar](50) NULL,
[RegistrantEmail] [nchar](190) NULL,

 CONSTRAINT [PK_Staffs] PRIMARY KEY CLUSTERED
(
[StaffID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Details altered for confidentiality

GO

/****** Object: Table [dbo].[StockDelivered] Script Date: 2/02/2020 7:52:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockDelivered](
[DeliveredId] [int] IDENTITY(1,1) NOT NULL,
[DeliveryDate] [datetime] NOT NULL,
[Barcode] [nvarchar](500) NULL,
[ShtStockId] [int] NOT NULL,
[Quantity] [float] NOT NULL,
[WaterBodyID] [int] NOT NULL,
[RegistrantEmail] [varchar](50) NOT NULL,

PRIMARY KEY CLUSTERED
(
[DeliveredId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Data only partially modified

GO
/****** Object: Table [dbo].[StockLink] Script Date: 2/02/2020 7:52:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockLink](
[StockLinkID] [int] IDENTITY(1,1) NOT NULL,
[StockID] [nchar](40) NOT NULL,
[Divisor] [int] NOT NULL,
[AssociatedID] [nchar](40) NOT NULL,
 CONSTRAINT [PK_StockLink] PRIMARY KEY CLUSTERED
(
[StockLinkID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Stock Link Table. These are items I buy in bulk and sell to clients in smaller amounts...or the odd item we have that doesn't have a bar code at all

GO

/****** Object: Table [dbo].[WaterBody] Script Date: 2/02/2020 7:52:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WaterBody](
[WaterBodyID] [int] IDENTITY(1,1) NOT NULL,
[ConstructionID] [int] NOT NULL,
[Depth] [decimal](18, 2) NOT NULL,
[Length] [decimal](18, 2) NOT NULL,
[LocationID] [int] NOT NULL,
[PoolTypeID] [int] NOT NULL,
[SiteID] [int] NOT NULL,
[WBName] [nvarchar](500) NOT NULL,
[Width] [decimal](18, 2) NOT NULL,
[IsTestBody] [bit] NULL,
 CONSTRAINT [PK_WaterBody] PRIMARY KEY CLUSTERED
(
[WaterBodyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Again I have had to filter the data a little, but should make sense.

yosiasz · February 2, 2020, 12:50am

Good job. Now create the insert statements which are dml data manipulation language.

Just as you saw how much it is to create the tables, same with providing the data. Not as foto attachments or excel screen shots but as dml

Insert into dbo.sampleData
Select 1, '73778383u83', 7677 union
Select 2, '84744849488', 85848

New_Creative · February 2, 2020, 2:56am

Uhmmm...is there a tool for this?

New_Creative · February 2, 2020, 4:31am

GO
/****** Object:  Table [dbo].[ActiveTemp]    Script Date: 2/02/2020 11:54:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ActiveTemp](
	[JobID] [int] IDENTITY(1,1) NOT NULL,
	[BookingDate] [datetime2](7) NOT NULL,
	[Department] [int] NOT NULL,
	[Division] [int] NULL,
	[InvoiceDate] [datetime] NULL,
	[InvoiceOptionID] [int] NULL,
	[JobDescription] [nvarchar](900) NOT NULL,
	[JobNumber] [nvarchar](max) NULL,
	[OrderNumber] [nvarchar](120) NOT NULL,
	[LookupTable] [int] NOT NULL,
	[LookupID] [int] NOT NULL,
 CONSTRAINT [PK_ActiveTemp1] PRIMARY KEY CLUSTERED 
(
	[JobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ShtStock]    Script Date: 2/02/2020 11:54:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ShtStock](
	[ShtStockId] [int] IDENTITY(1,1) NOT NULL,
	[Barcode] [nvarchar](500) NULL,
	[PartNumber] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](500) NOT NULL,
 CONSTRAINT [PK__ShtS__1B17B9B1711A71B9] PRIMARY KEY CLUSTERED 
(
	[ShtStockId] 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].[ShtStockDrumDeposit]    Script Date: 2/02/2020 11:54:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ShtStockDrumDeposit](
	[StockDepositID] [int] IDENTITY(1,1) NOT NULL,
	[ShtPartNumber] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ShtStockDrumDeposit] PRIMARY KEY CLUSTERED 
(
	[StockDepositID] 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].[Site]    Script Date: 2/02/2020 11:54:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Site](
	[SiteID] [int] IDENTITY(1,1) NOT NULL,
	[Clientid] [int] NOT NULL,
	[SiteAddress] [nvarchar](520) NOT NULL,
	[SiteName] [nvarchar](180) NOT NULL,
	[SitePostCode] [nvarchar](8) NOT NULL,
	[SiteSuburb] [nvarchar](80) NOT NULL,
	[SiteTypeID] [int] NOT NULL,
 CONSTRAINT [PK_Site] PRIMARY KEY CLUSTERED 
(
	[SiteID] 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].[StockDelivered]    Script Date: 2/02/2020 11:54:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockDelivered](
	[DeliveredId] [int] IDENTITY(1,1) NOT NULL,
	[DeliveryDate] [datetime] NOT NULL,
	[Barcode] [nvarchar](500) NULL,
	[ShtStockId] [int] NOT NULL,
	[Quantity] [float] NOT NULL,
	[WaterBodyID] [int] NOT NULL,
	[RegistrantEmail] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[DeliveredId] 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].[StockLink]    Script Date: 2/02/2020 11:54:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockLink](
	[StockLinkID] [int] IDENTITY(1,1) NOT NULL,
	[StockID] [nchar](40) NOT NULL,
	[Divisor] [int] NOT NULL,
	[AssociatedID] [nchar](40) NOT NULL,
 CONSTRAINT [PK_StockLink] PRIMARY KEY CLUSTERED 
(
	[StockLinkID] 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].[ActiveTemp] ON 

INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1604, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Regular Pool Service February 2020', N'124', N'Reg                                                                                                                     ', 2, 91)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1605, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Daily Operations February 2020', N'125', N'095436                                                                                                                  ', 2, 74)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1607, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Regular Pool Service February 2020', N'127', N'3232                                                                                                                    ', 2, 44)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1608, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Regular Pool and Spa Service February 2020', N'128', N'Email  Tuesday, August 13, 2019 3:39 PM                                                                                 ', 2, 83)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1609, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Regular Pool Service February 2020', N'129', N'Jemma Email 3/05/2017                                                                                                   ', 2, 66)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1610, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Regular Pool Service February 2020', N'130', N'Reg                                                                                                                     ', 2, 38)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1611, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Regular Pool Service February 2020', N'131', N'Reg                                                                                                                     ', 2, 59)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1612, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Daily Operations February 2020', N'132', N'Reg                                                                                                                     ', 2, 22)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1613, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Daily Operations February 2020', N'133', N'PU116458                                                                                                                ', 2, 20)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1614, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Daily Operations February 2020', N'134', N'95436                                                                                                                   ', 2, 81)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1615, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Daily Operations February 2020', N'135', N'David York                                                                                                              ', 2, 47)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1616, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Daily Operations February 2020', N'136', N'EMHS20193283                                                                                                            ', 2, 82)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1617, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Regular Pool Service February 2020', N'137', N'Reg                                                                                                                     ', 2, 61)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1618, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Regular Pool and Spa Service February 2020', N'138', N'Reg                                                                                                                     ', 2, 43)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1619, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Daily Operations February 2020', N'139', N'Reg                                                                                                                     ', 2, 58)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1620, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Daily Operations February 2020', N'140', N'Reg                                                                                                                     ', 2, 56)
INSERT [dbo].[ActiveTemp] ([JobID], [BookingDate], [Department], [Division], [InvoiceDate], [InvoiceOptionID], [JobDescription], [JobNumber], [OrderNumber], [LookupTable], [LookupID]) VALUES (1621, CAST(N'2020-01-30T12:11:56.2600000' AS DateTime2), 3, 1, NULL, NULL, N'Feb Internal Consumables February 2020', N'141', N'Reg                                                                                                                     ', 2, 84)
SET IDENTITY_INSERT [dbo].[ActiveTemp] OFF
SET IDENTITY_INSERT [dbo].[ShtStock] ON 

INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (448, N'9317370908074', N'0506CH6221', N'LIQUID CHLORINE 15 LTR             ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (449, NULL, N'0506CH6420               ', N'DRUM DEPOSIT & RETURNS 20LTR       ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (450, NULL, N'0506CH6421               ', N'CHLORINE DRUM TAP                  ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (451, NULL, N'0506CH6423               ', N'CHLORINE DRUM SPANNERS             ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (452, NULL, N'0507CE4683               ', N'MAGNOR TABLET DISPENSERS (SUN)     ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (453, NULL, N'0507CH6220               ', N'20 LITRE CHLORINE                  ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (454, N'', N'0507CH67093              ', N'1 KG CHLORINE TABLETS              ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (455, NULL, N'0507CH67094              ', N'2KG CHLORINE TABLETS               ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (456, N'', N'0508CH6005               ', N'HYDROCHLORIC ACID   5 LTR          ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (457, N'9319737142619', N'0508CH6010               ', N'NON FUMING ACID 15 LTR             ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (458, N'', N'0508CH6020               ', N'HYDROCHLORIC ACID 20 LTR           ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (459, N'', N'0509CH67134              ', N'BUFFER TA INCREASER 10KG           ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (460, N'9319737123403', N'0509CH67626              ', N'BUFFER  2KG                        ')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (1993, N'SEAD1993CPS', N'CPSpecial', N'1000 Litre Bulk Sodium Hypochlorite')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (1994, N'SEAD1994CPS2
', N'CPSpecial2', N'Chemical - Calcium Chloride Per Kg')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (1995, N'SEAD1995CPS3', N'CPSpecial3', N'Chemical - Sodium Bicarbonate Per Kg')
INSERT [dbo].[ShtStock] ([ShtStockId], [Barcode], [PartNumber], [Description]) VALUES (1996, N'SEAD1996CPS4', N'CPSpecial4', N'Chemical-Cyanuric Acid Per Kg')
SET IDENTITY_INSERT [dbo].[ShtStock] OFF
SET IDENTITY_INSERT [dbo].[ShtStockDrumDeposit] ON 

INSERT [dbo].[ShtStockDrumDeposit] ([StockDepositID], [ShtPartNumber]) VALUES (1, N'0507CH6220                                        ')
INSERT [dbo].[ShtStockDrumDeposit] ([StockDepositID], [ShtPartNumber]) VALUES (2, N'0508CH6020                                        ')
INSERT [dbo].[ShtStockDrumDeposit] ([StockDepositID], [ShtPartNumber]) VALUES (3, N'0506CH6221                                        ')
INSERT [dbo].[ShtStockDrumDeposit] ([StockDepositID], [ShtPartNumber]) VALUES (4, N'0508CH6010                                        ')
SET IDENTITY_INSERT [dbo].[ShtStockDrumDeposit] OFF

New_Creative · February 2, 2020, 4:32am

And ……..

SET IDENTITY_INSERT [dbo].[Site] ON 

INSERT [dbo].[Site] ([SiteID], [Clientid], [SiteAddress], [SiteName], [SitePostCode], [SiteSuburb], [SiteTypeID]) VALUES (1, 63, N'Keith Rd', N'Rossmoyne Senior High School', N'6148', N'Rossmoyne ', 4)
INSERT [dbo].[Site] ([SiteID], [Clientid], [SiteAddress], [SiteName], [SitePostCode], [SiteSuburb], [SiteTypeID]) VALUES (2, 80, N'16 Camp Rd', N'Murray Leisure Centre', N'6208', N'Pinjarra ', 26)
INSERT [dbo].[Site] ([SiteID], [Clientid], [SiteAddress], [SiteName], [SitePostCode], [SiteSuburb], [SiteTypeID]) VALUES (3, 69, N'54 Johnson Street', N'Bruce Rock', N'6418', N'Bruce Rock', 26)
INSERT [dbo].[Site] ([SiteID], [Clientid], [SiteAddress], [SiteName], [SitePostCode], [SiteSuburb], [SiteTypeID]) VALUES (4, 68, N'Lot 246, Poinciana St', N'Ashburton Pools', N'6751', N'Tom Price', 26)
INSERT [dbo].[Site] ([SiteID], [Clientid], [SiteAddress], [SiteName], [SitePostCode], [SiteSuburb], [SiteTypeID]) VALUES (5, 71, N'Cnr Larke Crescent & Kirkwood Street', N'Corrigin Pool', N'6375', N'Corrigin', 26)
INSERT [dbo].[Site] ([SiteID], [Clientid], [SiteAddress], [SiteName], [SitePostCode], [SiteSuburb], [SiteTypeID]) VALUES (6, 72, N'Myers Street', N'Dalwallinu Aquatic Centre', N'6609', N'Dalwallinu', 26)
INSERT [dbo].[Site] ([SiteID], [Clientid], [SiteAddress], [SiteName], [SitePostCode], [SiteSuburb], [SiteTypeID]) VALUES (7, 73, N'New Street', N'Iluka Aquatic Centre (Gingin)', N'6503', N'Gingin ', 26)
SET IDENTITY_INSERT [dbo].[Site] OFF
SET IDENTITY_INSERT [dbo].[StockDelivered] ON 

INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (55, CAST(N'2019-12-17T16:45:00.000' AS DateTime), N'9319737090507', 2035, 3, 29, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (56, CAST(N'2019-12-17T16:46:00.000' AS DateTime), N'9319737489943', 510, 2, 29, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (57, CAST(N'2019-12-17T18:18:00.000' AS DateTime), N'9319737090507', 2035, 3, 44, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (58, CAST(N'2019-12-17T18:18:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 1, 44, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (59, CAST(N'2019-12-17T18:19:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 2, 29, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (60, CAST(N'2019-12-17T18:22:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 2, 44, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (61, CAST(N'2019-12-17T20:03:00.000' AS DateTime), N'9319737090507', 2035, 5, 28, N'VicP@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (62, CAST(N'2019-12-17T20:04:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 3, 28, N'VicP@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (63, CAST(N'2019-12-17T02:05:00.000' AS DateTime), N'SEAQ0515CHPK0099', 487, 0.05, 34, N'geneW@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (64, CAST(N'2019-12-17T02:05:00.000' AS DateTime), N'SEAQ0518CHK01800', 500, 0.05, 34, N'geneW@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (65, CAST(N'2019-12-18T17:09:00.000' AS DateTime), N'9319737489738', 512, 2, 66, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (66, CAST(N'2019-12-18T17:10:00.000' AS DateTime), N'9319737142619', 457, 1, 66, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (67, CAST(N'2019-12-18T17:11:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 1, 66, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (68, CAST(N'2019-12-18T19:01:00.000' AS DateTime), N'9319737142619', 457, 2, 29, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (69, CAST(N'2019-12-18T19:23:00.000' AS DateTime), N'9319737090507', 2035, 5, 79, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (70, CAST(N'2019-12-18T19:28:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 2, 79, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (71, CAST(N'2019-12-18T19:40:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 1, 79, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (72, CAST(N'2019-12-18T20:58:00.000' AS DateTime), N'SEAD1995CPS3', 1995, 50, 48, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (73, CAST(N'2019-12-18T21:20:00.000' AS DateTime), N'9319737142619', 457, 2, 48, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (74, CAST(N'2019-12-18T01:08:00.000' AS DateTime), N'SEAD1994CPS2
', 1994, 17, 4, N'garry@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (75, CAST(N'2019-12-18T01:09:00.000' AS DateTime), N'SEAD1994CPS2
', 1994, 17, 5, N'garry@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (76, CAST(N'2019-12-18T01:10:00.000' AS DateTime), N'SEAD1994CPS2
', 1994, 2, 5, N'garry@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (77, CAST(N'2019-12-18T01:13:00.000' AS DateTime), N'SEAD1996CPS4', 1996, 2, 56, N'chrisR@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (78, CAST(N'2019-12-18T01:20:00.000' AS DateTime), N'SEAD1996CPS4', 1996, 5, 56, N'garry@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (79, CAST(N'2019-12-18T01:21:00.000' AS DateTime), N'SEAD1996CPS4', 1996, 3, 56, N'garry@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (80, CAST(N'2019-12-18T01:21:00.000' AS DateTime), N'SEAD1996CPS4', 1996, 3, 55, N'garry@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (81, CAST(N'2019-12-18T03:21:00.000' AS DateTime), N'SEAD1994CPS2
', 1994, 2, 11, N'chrisR@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (82, CAST(N'2019-12-18T03:49:00.000' AS DateTime), N'4049991084015', 592, 2, 79, N'chrisR@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (83, CAST(N'2019-12-18T03:50:00.000' AS DateTime), N'4049991059143', 579, 1, 79, N'chrisR@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (84, CAST(N'2019-12-18T03:52:00.000' AS DateTime), N'4049991084046', 577, 1, 79, N'chrisR@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (85, CAST(N'2019-12-18T04:15:00.000' AS DateTime), N'9319737090224', 2010, 0.02, 11, N'chrisR@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (86, CAST(N'2019-12-18T04:17:00.000' AS DateTime), N'9319737090224', 2010, 0.02, 8, N'chrisR@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (87, CAST(N'2019-12-19T16:02:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 3, 79, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (88, CAST(N'2019-12-19T16:34:00.000' AS DateTime), N'4049991084015', 592, 1, 80, N'garry@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (89, CAST(N'2019-12-19T20:56:00.000' AS DateTime), N'9319737090507', 2035, 1, 50, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (90, CAST(N'2019-12-19T20:57:00.000' AS DateTime), N'9319737090507', 2035, 1, 50, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (91, CAST(N'2019-12-19T20:57:00.000' AS DateTime), N'9319737090507', 2035, 1, 50, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (92, CAST(N'2019-12-19T20:59:00.000' AS DateTime), N'9319737090507', 2035, 1, 50, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (93, CAST(N'2019-12-19T21:04:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 2, 50, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (94, CAST(N'2019-12-20T16:12:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 1, 69, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (95, CAST(N'2019-12-20T17:43:00.000' AS DateTime), N'9319737142619', 457, 4, 31, N'VicP@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (96, CAST(N'2019-12-20T17:46:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 4, 49, N'VicP@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (97, CAST(N'2019-12-20T17:54:00.000' AS DateTime), N'9319737123403', 460, 1, 59, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (98, CAST(N'2019-12-20T17:55:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 1, 59, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (99, CAST(N'2019-12-20T20:33:00.000' AS DateTime), N'9319737090507', 2035, 3, 52, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (100, CAST(N'2019-12-20T20:33:00.000' AS DateTime), N'9319737142619', 457, 1, 52, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (101, CAST(N'2019-12-20T20:34:00.000' AS DateTime), N'9319737090507', 2035, 1, 69, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (102, CAST(N'2019-12-20T20:34:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 4, 52, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (103, CAST(N'2019-12-20T01:51:00.000' AS DateTime), N'SEAD2037CPA', 2039, 2, 14, N'geneW@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (104, CAST(N'2019-12-20T01:52:00.000' AS DateTime), N'SEAD1993CPS', 1993, 1, 49, N'geneW@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (105, CAST(N'2019-12-21T18:54:00.000' AS DateTime), N'9319737090507', 2035, 14, 7, N'Cale@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (106, CAST(N'2019-12-21T00:01:00.000' AS DateTime), N'SEAD1994CPS2
', 1994, 25, 2, N'scottT@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (107, CAST(N'2019-12-22T16:50:00.000' AS DateTime), N'9319737090507', 2035, 2, 71, N'garry@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (108, CAST(N'2019-12-22T17:04:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 2, 71, N'garry@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (109, CAST(N'2019-12-22T18:29:00.000' AS DateTime), N'9319737090507', 2035, 2, 28, N'Cale@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (110, CAST(N'2019-12-22T19:54:00.000' AS DateTime), N'SEAQ0506CHPlus', 2032, 4, 79, N'robG@Xxxxxxxxx.com')
INSERT [dbo].[StockDelivered] ([DeliveredId], [DeliveryDate], [Barcode], [ShtStockId], [Quantity], [WaterBodyID], [RegistrantEmail]) VALUES (111, CAST(N'2019-12-22T20:17:00.000' AS DateTime), N'SEAD1994CPS2
', 1994, 1, 28, N'robG@Xxxxxxxxx.com')
SET IDENTITY_INSERT [dbo].[StockDelivered] OFF
SET IDENTITY_INSERT [dbo].[StockLink] ON 

INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (1, N'1993                                    ', 1, N'1986                                    ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (2, N'1994                                    ', 25, N'511                                     ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (3, N'1995                                    ', 25, N'462                                     ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (4, N'1996                                    ', 25, N'466                                     ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (6, N'507                                     ', 1, N'507                                     ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (7, N'485                                     ', 1, N'485                                     ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (8, N'2031                                    ', 1, N'449                                     ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (9, N'2032                                    ', 1, N'449                                     ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (10, N'500                                     ', 1, N'500                                     ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (11, N'487                                     ', 1, N'487                                     ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (12, N'481                                     ', 1, N'481                                     ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (13, N'2038                                    ', 20, N'500                                     ')
INSERT [dbo].[StockLink] ([StockLinkID], [StockID], [Divisor], [AssociatedID]) VALUES (14, N'2039                                    ', 20, N'487                                     ')
SET IDENTITY_INSERT [dbo].[StockLink] OFF
ALTER TABLE [dbo].[Site]  WITH CHECK ADD  CONSTRAINT [FK_Site_Clients_Clientid] FOREIGN KEY([Clientid])
REFERENCES [dbo].[Clients] ([Clientid])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Site] CHECK CONSTRAINT [FK_Site_Clients_Clientid]
GO
ALTER TABLE [dbo].[Site]  WITH CHECK ADD  CONSTRAINT [FK_Site_SiteType_SiteTypeID] FOREIGN KEY([SiteTypeID])
REFERENCES [dbo].[SiteType] ([SiteTypeID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Site] CHECK CONSTRAINT [FK_Site_SiteType_SiteTypeID]
GO
ALTER TABLE [dbo].[StockDelivered]  WITH CHECK ADD  CONSTRAINT [FK_StockDelivered_ShtStock] FOREIGN KEY([ShtStockId])
REFERENCES [dbo].[ShtStock] ([ShtStockId])
GO
ALTER TABLE [dbo].[StockDelivered] CHECK CONSTRAINT [FK_StockDelivered_ShtStock]
GO

GO
/****** Object:  Table [dbo].[Staffs]    Script Date: 2/02/2020 12:22:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Staffs](
	[StaffID] [int] IDENTITY(1,1) NOT NULL,
	[DivisionID] [int] NOT NULL,
	[HireDate] [datetime2](7) NOT NULL,
	[StaffName] [nvarchar](320) NOT NULL,
	[TerminationDate] [datetime2](7) NULL,
	[StaffMobileNumber] [varchar](50) NULL,
	[RegistrantEmail] [nchar](190) NULL,
 CONSTRAINT [PK_Staffs] PRIMARY KEY CLUSTERED 
(
	[StaffID] 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].[Staffs] ON 
INSERT [dbo].[Staffs] ([StaffID], [DivisionID], [HireDate], [StaffName], [TerminationDate], [StaffMobileNumber], [RegistrantEmail]) VALUES (4, 1, CAST(N'2016-01-18T00:00:00.0000000' AS DateTime2), N'Scott ', NULL, N'00000', N'scottT@Xxxxxxxxxxxxxx.com                                                                                                                                                      ')
INSERT [dbo].[Staffs] ([StaffID], [DivisionID], [HireDate], [StaffName], [TerminationDate], [StaffMobileNumber], [RegistrantEmail]) VALUES (5, 1, CAST(N'2016-01-18T00:00:00.0000000' AS DateTime2), N'Garry R', NULL, N'00000', N'garry@Xxxxxxxxxxxxxx.com                                                                                                                                                       ')
INSERT [dbo].[Staffs] ([StaffID], [DivisionID], [HireDate], [StaffName], [TerminationDate], [StaffMobileNumber], [RegistrantEmail]) VALUES (8, 1, CAST(N'2016-01-18T00:00:00.0000000' AS DateTime2), N'Tony R', NULL, N'00000', N'TonyR@Xxxxxxxxxxxxxx.com                                                                                                                                                       ')
INSERT [dbo].[Staffs] ([StaffID], [DivisionID], [HireDate], [StaffName], [TerminationDate], [StaffMobileNumber], [RegistrantEmail]) VALUES (9, 1, CAST(N'2016-01-18T00:00:00.0000000' AS DateTime2), N'Chris R', NULL, N'00000', N'chrisR@Xxxxxxxxxxxxxx.com                                                                                                                                                      ')
INSERT [dbo].[Staffs] ([StaffID], [DivisionID], [HireDate], [StaffName], [TerminationDate], [StaffMobileNumber], [RegistrantEmail]) VALUES (11, 1, CAST(N'2016-01-18T00:00:00.0000000' AS DateTime2), N'Victor P', NULL, N'00000', N'VicP@Xxxxxxxxxxxxxx.com                                                                                                                                                        ')
INSERT [dbo].[Staffs] ([StaffID], [DivisionID], [HireDate], [StaffName], [TerminationDate], [StaffMobileNumber], [RegistrantEmail]) VALUES (21, 1, CAST(N'2016-04-18T00:00:00.0000000' AS DateTime2), N'Gene W', NULL, N'00000', N'geneW@Xxxxxxxxxxxxxx.com                                                                                                                                                       ')
INSERT [dbo].[Staffs] ([StaffID], [DivisionID], [HireDate], [StaffName], [TerminationDate], [StaffMobileNumber], [RegistrantEmail]) VALUES (22, 1, CAST(N'2011-01-01T00:00:00.0000000' AS DateTime2), N'Robert G', NULL, N'00000', N'robG@Xxxxxxxxxxxxxx.com                                                                                                                                                        ')
INSERT [dbo].[Staffs] ([StaffID], [DivisionID], [HireDate], [StaffName], [TerminationDate], [StaffMobileNumber], [RegistrantEmail]) VALUES (23, 1, CAST(N'2019-10-10T00:00:00.0000000' AS DateTime2), N'Cate L', NULL, N'00000', N'Cale@Xxxxxxxxxxxxxx.com                                                                                                                                                        ')
SET IDENTITY_INSERT [dbo].[Staffs] OFF
ALTER TABLE [dbo].[Staffs]  WITH CHECK ADD  CONSTRAINT [FK_Staffs_Divisions_DivisionID] FOREIGN KEY([DivisionID])
REFERENCES [dbo].[Divisions] ([DivisionID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Staffs] CHECK CONSTRAINT [FK_Staffs_Divisions_DivisionID]
GO

New_Creative · February 2, 2020, 4:32am

Hope I got that right

New_Creative · February 2, 2020, 8:11pm

And one table I forgot...

CREATE TABLE [dbo].[MonthChange](
	[MonthChangeID] [int] IDENTITY(1,1) NOT NULL,
	[MonthName] [nchar](70) NOT NULL,
	[ChangeDate] [date] NOT NULL,
 CONSTRAINT [PK_MonthChangeNew] PRIMARY KEY CLUSTERED 
(
	[MonthChangeID] 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].[MonthChange] ON 

INSERT [dbo].[MonthChange] ([MonthChangeID], [MonthName], [ChangeDate]) VALUES (1, N'January 2020                                                          ', CAST(N'2019-12-17' AS Date))
INSERT [dbo].[MonthChange] ([MonthChangeID], [MonthName], [ChangeDate]) VALUES (2, N'February 2020                                                         ', CAST(N'2020-01-21' AS Date))
SET IDENTITY_INSERT [dbo].[MonthChange] OFF

yosiasz · February 3, 2020, 1:25am

please fully test before posting?

Foreign key 'FK_Site_Clients_Clientid' references invalid table 'dbo.Clients'.

Foreign key 'FK_Site_SiteType_SiteTypeID' references invalid table 'dbo.SiteType'.

and many other errors.

New_Creative · February 3, 2020, 2:10am

Thats the trouble when learning by doing still not sure how to test or to ensure the 'errors are cleaned up.

yosiasz · February 3, 2020, 2:14am

By trying it yourself on a test database🤟

New_Creative · February 3, 2020, 2:16am

I have a feeling this is so entwined I would end up with the entire database as an outcome. Its very related.

New_Creative · February 4, 2020, 10:16pm

Well that took forever...Who knew Hospitals are linked to Water Bodys and cant be removed until the First Aid kit content is removed as well. Nonetheless I have disentwined as best I can.
One Drive Link To Script

yosiasz · February 4, 2020, 11:05pm

So what is this link for? And no I personally will not be clicking on it

New_Creative · February 5, 2020, 2:34am

Well thats frustrating...

I cant attach a file. The script is several thousands of lines now with several hundreds of records, and even if I cut the records there is still something around 30 tables meaning I cant copy and paste the script into one response.

I thought upload to a shared folder would be a reasonable solution.

What other way can be suggested?