Click here to Skip to main content
13,045,285 members (57,641 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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
(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

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
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
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

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
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
(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

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
Posted 23-Apr-13 9:01am
cnh 2262
Updated 24-Apr-13 9:50am
dbrenth 23-Apr-13 16:28pm
I don't understand your comment about views. The purpose of a view is to display data with computed or renamed columns. They do allow multiple tables and functions, but you can't update or insert through a view with multiple tables and functions. You have to insert or update the base tables, and then the view with its calculated columns will update automatically.
cnh 2 23-Apr-13 16:31pm
Sorry, my English is not the best as well ;) ... I mean to link these tables in a View is simple and to create the Compute VColumn on it as well, I just can't get it done on Table Level ...
dbrenth 23-Apr-13 16:37pm
I wouldn't try to do it on a table level. You can probably mess with triggers, but triggers can be fragile. They have to be in every table that touches your data in insert update and delete functions. And if you have a bug in your calculation, good luck - it could be in any one or more of the triggers.

Using a view allows you to maintain the calculated information in one place as if it was a table. And the calculations are in one place.
cnh 2 23-Apr-13 16:49pm
I've just updated my question - the View works fine, but it doesn't allow me to use the value for our Employees when they actually enter data onto the specific table ... a cross reference would be lovely, with a Check Constraint that refers to the Calculated Value of the Field in the View ... I just have no idea, what' possible and what's not - must be coz it's 4 am here ... thanks for the help so far anyways :)
@AmitGajjar 24-Apr-13 1:24am
nobody have time to read such a long question.
CHill60 25-Apr-13 4:49am
How does the data get entered onto your database - do you have an application? Put the validation there. Or use a stored procedure to do the inserts and put validation code in there.

"multiple entries for the same Booking-ID" - you've defined it with IDENTITY but then use SET IDENTITY_INSERT - so don't. Allow the table to generate the booking id for you e.g. INSERT INTO CourseBookings ( CourseDate, CU_ID, Break_ID) SELECT '2013-04-22', 15250, 3 and/or use unique indexes[^]
cnh 2 25-Apr-13 15:11pm
multiple Entries for the same Booking_ID was referred to my WagesEntry Table ... the only reason I set the IDENTITY INSERT was to create some sample data ... anyways ... I think I get a way around it with Filtered Indexes and Constraints ... I hope :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 24 Apr 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100