Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 ...:

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

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

SQL
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
Posted
Updated 24-Apr-13 9:50am
v5
Comments
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900