Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
Posted 23-Apr-13 10:01am
cnh 2262
Edited 24-Apr-13 10:50am
(no name)10.3K
v5
Comments
dbrenth at 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 at 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 at 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 at 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 at 24-Apr-13 1:24am
   
nobody have time to read such a long question.
CHill60 at 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 http://msdn.microsoft.com/en-us/library/ms175132(v=sql.105).aspx[^]
cnh 2 at 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)

  Print Answers RSS
0 OriginalGriff 410
1 Jochen Arndt 200
2 Richard MacCutchan 135
3 DamithSL 105
4 PIEBALDconsult 90
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,032
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,220


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 24 Apr 2013
Copyright © CodeProject, 1999-2014
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