Hi,
a slightly shorter question/problem with some Examples this time ... I'm trying to link the following tables and create a computed column on the third table to limit the entries of future Records ... But first the Tables & some Data:
First Table is the Breakdown-Table - it contains about 40 different Breakdowns that contain the Information for the Amount of Aca/Confined/Dives, in which our Courses are broken down to be able to split Wages for Instructors involved in teaching these Courses - this table is fix and might require an update for a single record maybe once every two years ...:
use TooEarly
IF OBJECT_ID('TooEarly..Breakdown','U') IS NOT NULL
DROP TABLE Breakdown
CREATE TABLE [dbo].[Breakdown](
[Break_ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [nvarchar](20) NOT NULL,
[Commissions] [int] NOT NULL,
[Amount_Aca] [int] NOT NULL,
[Comms_Aca_Unit] [int] NOT NULL,
[Amount_Confined] [int] NULL,
[Comms_Conf] [int] NOT NULL,
[Amount_Dives] [int] NULL,
[Comms_Dives_Unit] [numeric](6, 2) NOT NULL,
[Amount_Other] [int] NULL,
[Comms_Other_Unit] [numeric](6, 2) NOT NULL
)
SET IDENTITY_INSERT Breakdown ON
INSERT INTO Breakdown
(Description, Break_ID, Commissions, Amount_Aca, Comms_Aca_Unit, Amount_Confined, Comms_Conf,
Amount_Dives, Comms_Dives_Unit, Amount_Other, Comms_Other_Unit)
SELECT 'OW Standard', 3, 1700, 5, 85, 1, 425, 4, 212.5, 0, 0 UNION ALL
SELECT 'Open Water Deluxe', 3, 1700, 5, 85, 1, 425, 4, 212.5, 0, 0 UNION ALL
SELECT 'AOW Std.', 4, 1450, 0, 0, 0, 0, 5, 290, 0, 0 UNION ALL
SELECT 'EFR,O2-Provider', 5, 500, 1, 500, 0, 0, 0, 0, 0, 0 UNION ALL
SELECT 'Divemaster', 6, 5500, 2, 2000, 1, 1000, 1, 500, 0, 0
SET IDENTITY_INSERT Breakdown ON
Second Table is our CourseBookings-Table, which grows by about 15.000 Records a year. In this one we collect Records of the Courses our Customers book with us. A single Customer can have multiple Courses booked with us (as in the Test-Data I entered), so the CBook_ID is as well used as a reference for the Wages payed to our Staff (FK on the WagesEntryNew). I shortened this one by all the references to other parts of the DB, that are irrelevant:
USE TooEarly
GO
IF OBJECT_ID('TooEarly..CourseBookings','U') IS NOT NULL
DROP TABLE CourseBookings
CREATE TABLE [dbo].[CourseBookings](
[CBook_ID] [int] IDENTITY(300000,1) NOT NULL,
[CourseDate] [datetime] NOT NULL,
[CU_ID] [int] NOT NULL,
[Break_ID] [int] NULL
)
SET IDENTITY_INSERT CourseBookings ON
INSERT INTO CourseBookings
(CBook_ID, CourseDate, CU_ID, Break_ID)
SELECT 1000, '2013-04-22', 15250, 3 UNION ALL
SELECT 1001, '2013-04-22', 15251, 4 UNION ALL
SELECT 1002, '2013-04-23', 15252, 3 UNION ALL
SELECT 1003, '2013-04-23', 15253, 5 UNION ALL
SELECT 1004, '2013-04-24', 15254, 6 UNION ALL
SELECT 1005, '2013-04-24', 15251, 5 UNION ALL
SELECT 1006, '2013-04-26', 15250, 4 UNION ALL
SELECT 1007, '2013-04-28', 15250, 3
SET IDENTITY_INSERT CourseBookings ON
Here now the 3rd table WagesEntryNew, this one is used by our staff to enter the information about how many Parts of a Course (Aca/Confined/Dive) an Individual has been teaching. A Course is sometimes taught by one Person entirely, sometimes it's split between multiple Instructors. Every Instructor is teaching multiple Courses in a certain Pay-Period. The Amount of entries on this table is about 20.000 per year:
USE TooEarly
GO
IF OBJECT_ID('TooEarly..WagesEntryNew','U') IS NOT NULL
DROP TABLE WagesEntryNew
CREATE TABLE [dbo].[WagesEntryNew](
[Wage_ID] [int] IDENTITY(65000,1) NOT NULL,
[Cert_Date] [datetime] NOT NULL,
[Ins_ID] [int] NOT NULL,
[CBook_ID] [int] NOT NULL,
[Break_ID] [int] NULL,
[Amount_Aca] [int] NOT NULL,
[Amount_Confined] [int] NOT NULL,
[Amount_Dives] [int] NOT NULL
)
SET IDENTITY_INSERT WagesEntryNew ON
INSERT INTO WagesEntryNew
(Wage_ID, Cert_Date, Ins_ID, CBook_ID, Break_ID, Amount_Aca, Amount_Confined, Amount_Dives)
SELECT 1, '2013-04-25', 2000, 1000, 3, 5, 1, 4 UNION ALL
SELECT 2, '2013-04-25', 2010, 1001, 4, 0, 0, 3 UNION ALL
SELECT 3, '2013-04-25', 2020, 1001, 4, 0, 0, 2 UNION ALL
SELECT 4, '2013-04-26', 2030, 1002, 3, 3, 1, 0 UNION ALL
SELECT 5, '2013-04-26', 2040, 1002, 3, 2, 1, 4 UNION ALL
SELECT 6, '2013-04-23', 2030, 1003, 5, 1, 0, 0 UNION ALL
SELECT 7, '2013-04-26', 2010, 1004, 6, 1, 1, 1 UNION ALL
SELECT 8, '2013-04-26', 2000, 1004, 6, 1, 0, 0 UNION ALL
SELECT 9, '2013-04-26', 2030, 1005, 5, 1, 0, 0 UNION ALL
SELECT 10, '2013-04-26', 2020, 1006, 4, 0, 0, 5 UNION ALL
SELECT 11, '2013-04-30', 2020, 1007, 3, 5, 1, 0 UNION ALL
SELECT 12, '2013-04-30', 2010, 1007, 3, 0, 0, 4
SET IDENTITY_INSERT WagesEntryNew ON
What we are trying to achieve now is to create an option to only allow Entries for Aca/Confined/Dives on this WagesEntryNew Table, that won't exceed the Amount_Aca for a single Course_ID as defined in the Breakdown.
So as an example out of this data:
Instructor 2010 & 2020 have been teaching a Course together with the Break_ID 4 which refers to a Course that contains 0 Aca, 0 Confined, 5 Dives. They entered their information correctly and split 3/2
Instructor 2030 & 2040 taught a Course with the Break_ID 3, which contains 5 Aca, 1 Confined, 4 Dives, but they entered the data wrong, as their Entries for Confined exceed the Maximum of 1.
Another Application is that depleting Values avoid multiple entries for the same Booking-ID, which is a common problem in our running scenario.
So basically I am looking for some sort of Computed Column that on insert checks against the Break_ID and returns something like Breakdown.Amount_Aca - WagesEntryNew.Amount_Aca AS Remain_Aca for a given CBook_ID. ...
It works fine on a view as a view allows me to cross reference multiple tables, but I would like the Result stored in a new Column on the table ...
I appreciate any input and thanks for your help already