April 23, 2013 at 1:10 pm
Hi,
I've got the following Problem, which I'd like to solve in SQL Server 2008:
I got a Table 'Products', each product with a range of up to 3 different areas, which contains Information about the Total Amount of items for this specific product.
So the first product contains of a Total of 10 different Parts, whereas the 2nd product contains out of 5 and so on …
PROD_ID: ACA: CW: DIVE: BR_ID
1: 5: 1: 4: 10
2: 0: 0: 5: 11
3: 1: 0: 2: 12
4: 2: 1: 2: 10
I got a second table named 'Breakdown' which defines the commissions paid for each different Product-Part ...
BR_ID: ACA: CW: DIVE
10: 85: 425: 213
11: 0: 0: 290
12: 850: 0: 325
This table is fix and used to calculate the Comms for each Individual – it’s NOT editable by the USER.
My third table 'Orders' contains all the ORDERS placed by the Customers ...
ORD_ID: PROD_ID: Date
100: 1: 01-Apr
101: 1: 01-Apr
102: 2: 02-Apr
103: 3: 02-Apr
104: 1: 03-Apr
In each of the 3 tables the first column is IDENTITY and as such definitely not entered twice.
Here my problem:
In my fourth table 'Comms' I need to get all the Commissions correctly entered and it's done by the Receivers themselves AFTER the Product has been sold and PAID- obviously it's unlikely that Comms are not entered at all seeing that people want to collect those - but it's more likely that people enter those multiple times/multiple people enter the Comms twice ...
What I try to achieve is a Calculated Field to validate the entries in that table on SAVE Action, with multiple criterias.
I want to only allow Users to enter the maximum allowed Amount of Prod.-Parts from the first table (ACA/CW/DIVE) for a single ORDER_ID from Table 3, so for example if Order_ID 100 refers to PROD_ID 1 (which contains 5 Parts ACA), then a Maximum of total Entries of ACA could not exceed 5 in ALL rows entered for this ORDER_ID or an error message is thrown …
My Table 4 'Comms' looks like this so far:
CREATE TABLE [dbo].[Comms](
[Comm_ID] [int] IDENTITY(65000,1) NOT NULL,
[Cert_Date] [datetime] NOT NULL,
[Ins_ID] [int] NOT NULL,
[ORD_ID] [int] NOT NULL,
[Amount_Aca] [int] NOT NULL,
[Amount_Confined] [int] NOT NULL,
[Amount_Dives] [int] NOT NULL,
What I would like now is a Computed Column of the style like "Compare the Records for this Order_ID, find the Product and Breakdown-Values for it from Table 1 & 2 and then search for existing Records with that Order_ID in the 'Comms' Table itself. If no entry has been made, return the Maximum Parts as defined in the 'Products' Table. If someone entered for example already 2 ACA for PROD_ID 1 under ORD_ID 101, then return the remaining Value as 5 (MAX ITEMS ACA) - 2 = 3 ...
A simple View can do this, but unfortunately I couldn't find a way to Create it with Computed Columns (as they can't refer to other Tables) or Functions/Triggers, as well as it has to be done more or less on Record Creation.
The ID-Fields on all Tables are set IDENTITY, and FK are set in between them.
On a positive side: This application is not for millions of Transactions at the same time, so it's a chance of about one million to one, that two users will enter their Comms at the same time, so double entries/engine performance shouldn't be a problem. ...
Any help with this would be really appreciated, I'm new to this stuff, so any extra information that could help I'd love to provide. Thanks a lot already! 🙂
April 23, 2013 at 1:18 pm
Hi and welcome to the forums. It is very difficult to determine what you are trying to do here. The "columns" are all very short abbreviations that make sense to somebody at your company but to an outside they mean nothing. It is difficult to tell from your description what you are trying to do. I think maybe you want a constraint on the orders table to limit the max amount of a given sku can be placed on the order? Maybe using a function here would work?
If you could post ddl and sample data along with a clear picture of what you are trying to do there will be lots of people around here jumping in to help. Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 2:20 pm
Hey Sean,
thanks and sorry - I am new to posting on Forums, usually I find what I need in other Posts 🙂 ... and new to the whole problem ...
I merged the first two tables as one, seeing they are both more or less static and don't hold many records ever it might as well both happen in the same table ...
Here some more info on my tables (I took out what's not important):
Table 1 (now merged with Table 2):
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,
...
The CourseBookings-Table is ever growing and looks like this:
CREATE TABLE [dbo].[CourseBookings](
[CBook_ID] [int] IDENTITY(300000,1) NOT NULL,
[CourseDate] [datetime] NOT NULL,
[CU_ID] [int] NOT NULL,
[AGroup_ID] [int] NULL,
[Agent_ID] [int] NULL,
[CCateg_ID] [int] NOT NULL,
[Course_ID] [int] NOT NULL,
[Lang_ID] [int] NULL,
[Course_Price] [int] NOT NULL,
[DiscBaht] [int] NOT NULL,
[DiscPC] [int] NULL,
[Total_Course] AS (([Course_Price]-[DiscBaht])-([Course_Price]*[DiscPC])/(100)),
[Paid_Full] [bit] NULL,
[Break_ID] [int] NULL,
The whole purpose of my question now is the Commissions paid for each individual who did parts of that Course - we do this via a separate table used for data entry by the individual people teaching that Course, this table is called WagesEntry.
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,
[FDBook_ID] [int] NULL,
[Break_ID] [int] NULL,
[Amount_Aca] [int] NOT NULL,
[Amount_Confined] [int] NOT NULL,
[Amount_Dives] [int] NOT NULL,
Our Employees enter the Information about the Amount of Aca, Confined (*referred to as CW in other Tables*) or Dives themselves via a Web-Portal on .asp. I would like the following:
1.) Once an WagesEntryNew Record is saved by an Employee on the last table, I would like to have the entered value deducted from the Maximum Value defined in the fields on [dbo].[Breakdown] for Aca, Confined and Dives. So if 1 day later another employee would try to enter more than the remaining Amount of e.g. Aca an error would be thrown.
2.) This could potentially throw a lot of errors once implemeynted, so I would as well like to do it with the Remaining_Aca, Remaining_Confined, Remaining_Dives physically stored on that WagesEntryNew-Table, so I can retrieve this Calculated in our .asp-Application (Employees will see the remaining Parts next to their data entry field) ...
I will recreate the whole thing just as a Test based on the "Best Practices" in a wee bit, but maybe this gives you a better idea of what we want to achieve already.
Thanks again and sorry for the Newbie-Stuff 😉
April 23, 2013 at 2:56 pm
No worries about being new around here...we all were once. 😛
So what you want to do is to have the Breakdown.Amount_ACA be a remaining based on WagesEntryNew?
In other words a row would be entered in Breakdown with some quantity, say 100. Then a new row is added to WagesEntryNew with Amount_ACA = 10. You want to update Breakdown.Amount_ACA to now have a value of 90?
I am pretty sure that is what you are looking for. I have to say, this is a horrible idea. It would be FAR better to have a table that holds your original data for a course including Amount_ACA (I have no idea what that is but it doesn't really matter). Then you could create an indexed view with a column that calculates what is left based on the original value - SUM(WagesEntryNew). If you try to keep your column up to date like I described above you are in for a long, painful and most likely unsuccessful road. You will be forced to have multiple triggers on your table because you will have to deal with inserts, updates and deletes
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 3:39 pm
Hey Sean,
about to fall to sleep - 4:15 AM here, but the Football kept me up ...
Just to clarify: In my Scenario it's the other way around. The Breakdown.Amount_Aca is the Constant, let's say 5 for a Certain Course. In the Breakdown-Table there is a clear definition that for each Break_ID is a certain Amount of Aca ranging from 0-5 and a certain Amount of Wages paid for each Part. So this is my constant that I don't wanna get changed (except new Courses/Products come up) ...
My two tables that are updated frequently is the CourseBookings-Table and the WagesEntryNew-Table.
CourseBookings are updated every time a Customer comes to us and books a Course with us which ranges from 1 Day to 6 Months Duration ... anyways, for the booked Course he get's a reference (IDENTITY) CBook_ID.
Multiple Employees might be working on his Course and finish certain parts, so for example Employee 1 finishes 2 Parts of Amount_Aca and Employee 2 finishes 3 Parts of Aca out of a set of Amount_Aca.
In my WagesEntryNew Table I would like to have a field that calculates the Remaining Parts of Aca based on the FIX Amount of Aca that is set in the Breakdown Table.
Let's say, Employee 1 enters a value of 3 for Amount_Aca in the WagesEntryNew-Table for a Course that contains the value '5' for Amount_Aca in the CourseBreakdown-Table, I would like this information to be processed, so that the Amount_Aca in WagesEntryNew is deducted from Amount_Aca in CourseBreakdown and inserted into a new field on the WagesEntryNew-Table.
I get the values using a View like this:
SELECT dbo.WagesEntryNew.CBook_ID, dbo.CourseBreakdown.Description, dbo.WagesEntryNew.Break_ID, dbo.CourseBreakdown.Amount_Aca, dbo.WagesEntryNew.Amount_Aca AS AcaWages,
dbo.CourseBreakdown.Amount_Aca - dbo.WagesEntryNew.Amount_Aca AS LeftAca
FROM dbo.CourseBreakdown RIGHT OUTER JOIN
dbo.WagesEntryNew ON dbo.CourseBreakdown.Break_ID = dbo.WagesEntryNew.Break_ID
and LeftAca will show me exactely what I want - the remainder of Aca for that particular Course that can be entered by another User in our Web-Application ... this example shows the outcome of the query on that view:
Break_ID / Amount_Aca (that's the Total possible) / Amount_Aca (entered already) / LeftAca
4000
3505
3514
3523
3532
I just can't get it to work on table level, I tried a few UDF, but they all seem to end up in weird numbers rather than retrieving the value from the view ...
Thanks for all the help, really appreciated - if you need some diving courses, ask me 🙂
April 23, 2013 at 3:46 pm
Get some sleep!!!
Once you are rested what would be a big help is to post some sample data along with what you expect to happen. You will be able to explain it more clearly after you get some rest. 😀
Here is the ddl that you posted in a format that will actually work. This is what I am using. Please give me sample data that will fit in these tables. Then give me some detailed instructions about what you want to happen.
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
)
--The CourseBookings-Table is ever growing and looks like this:
CREATE TABLE [dbo].[CourseBookings](
[CBook_ID] [int] IDENTITY(300000,1) NOT NULL,
[CourseDate] [datetime] NOT NULL,
[CU_ID] [int] NOT NULL,
[AGroup_ID] [int] NULL,
[Agent_ID] [int] NULL,
[CCateg_ID] [int] NOT NULL,
[Course_ID] [int] NOT NULL,
[Lang_ID] [int] NULL,
[Course_Price] [int] NOT NULL,
[DiscBaht] [int] NOT NULL,
[DiscPC] [int] NULL,
[Total_Course] AS (([Course_Price]-[DiscBaht])-([Course_Price]*[DiscPC])/(100)),
[Paid_Full] [bit] NULL,
[Break_ID] [int] NULL
)
--The whole purpose of my question now is the Commissions paid for each individual who did parts of that Course - we do this via a separate table used for data entry by the individual people teaching that Course, this table is called WagesEntry.
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,
[FDBook_ID] [int] NULL,
[Break_ID] [int] NULL,
[Amount_Aca] [int] NOT NULL,
[Amount_Confined] [int] NOT NULL,
[Amount_Dives] [int] NOT NULL
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2013 at 4:29 am
Hi Sean,
after a looong sleep I'm a bit more rested and cleared up a bit ... First my 3 slightly shorter 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 Commissions for individual people 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. ...
As I said, 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
April 24, 2013 at 8:34 am
What you need here is a computed persisted column on your breakdown table, possibly three (1 for ACA, Confined and Dives). Then you would need to create a constraint on WagesEntryNew for Amount_ACA, Amount_Confined and Amount_Dives. Each of these constraints would use a function to check that the new value is < the computed column in Breakdown.
Start by working with one element and create the computed column. It would be something like Breakdown.Amount_ACA - SUM(WagesEntryNew) for Break_ID.
Does this help get you pointed in the right direction? Some of the stuff you are doing is still a little fuzzy for me, but I can be rather dense. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2013 at 10:37 am
Cheers for the reply ...
There's two problems here with this solution ... 1.) The Breakdown-Table contains the constants, I'd need these Computed Columns on my Wages Entry table as e.g. Left_Aca, Left_Confined and Left_Dives Column seeing that they are dependent on the Course-Booking-ID AND the Break_ID (There is multiple Course_Bookings with the same Break_ID, I need to cross-reference that within a single Course-Booking the Amount of let's say Aca is not exceeding more than allowed for that Break_ID) ... sorry, it is confusing 🙂
The main problem for me is the construct though, as I don't see a way to reference another Table within a Computed Column. I've seen some ideas about using UDF to retrieve Data from Tables/Views and insert them into a Table-Field, but none of those functions seemed to work for me.
If you'd have any idea, how I could get a function to retrieve e.g. field "Amount_Aca" from my Breakdown-Table into a field in my WagesEntryNew-Table as e.g. "MAX_Aca" WHERE Break_ID on both tables match, then I could work from there with Constraints/Computed Columns. You think you could provide me with some Code-Snippet as a start idea ???
Cheers (more football tonight ... pheeew)
April 24, 2013 at 11:07 am
carsten.crystaldive (4/24/2013)
Cheers for the reply ...There's two problems here with this solution ... 1.) The Breakdown-Table contains the constants, I'd need these Computed Columns on my Wages Entry table as e.g. Left_Aca, Left_Confined and Left_Dives Column seeing that they are dependent on the Course-Booking-ID AND the Break_ID (There is multiple Course_Bookings with the same Break_ID, I need to cross-reference that within a single Course-Booking the Amount of let's say Aca is not exceeding more than allowed for that Break_ID) ... sorry, it is confusing 🙂
The main problem for me is the construct though, as I don't see a way to reference another Table within a Computed Column. I've seen some ideas about using UDF to retrieve Data from Tables/Views and insert them into a Table-Field, but none of those functions seemed to work for me.
If you'd have any idea, how I could get a function to retrieve e.g. field "Amount_Aca" from my Breakdown-Table into a field in my WagesEntryNew-Table as e.g. "MAX_Aca" WHERE Break_ID on both tables match, then I could work from there with Constraints/Computed Columns. You think you could provide me with some Code-Snippet as a start idea ???
Cheers (more football tonight ... pheeew)
No you want to add a computed column to the breakdown table. It will have two columns. One for Amount_ACA and the new one is Amount_ACA_Available. Maybe you don't even need to persist the data, you can just create a UDF that calculates the amount available and use that as a constraint on the Wages table.
I will see if I can find some time later today to cobble something together. Meantime I have my own work to get done. 😀
Which football are you watching?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2013 at 2:43 pm
Sorry it took me a bit to get back to this. I am trying to figure out the relationship between these tables and am a bit lost.
You didn't identify how the relationships between these tables work so I am guessing.
It seems like WagesEntryNew would join to CourseBookings on Break_ID and CBook_ID. But the only thing in Breakdown is Break_ID and there are multiples in there.
I can't figure out how these relationships work. I am wondering if you dropped something critical when you removed a lot of the extra stuff when you posted your tables.
Let's say I want to find the rows among those tables for CBook_ID = 1000. Here is the query I came up with. The problem here is that it returns 2 rows from Breakdown.
select *
from WagesEntryNew w
join CourseBookings c on c.CBook_ID = w.CBook_ID and c.Break_ID = w.Break_ID
join Breakdown b on b.Break_ID = c.Break_ID
where w.CBook_ID = 1000
What am I missing here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2013 at 11:30 pm
Oh Bollocks, I entered the Break_ID 3 twice in my TestData - sorry ... after Removing the first one the statement returns 1 row only ... obviously with the real data Break_ID is Unique
It was the Champions League Semi-Final ... slowly recovering from it 🙂
These tables are a bit confusing I got to admit - let me try to explain ...
The Breakdown Table has Break_ID (IDENTITY) as PK and is linked to both the Wage Entry-Table and the Course Bookings-Table via FK. One of those Fields (the one in Wages) is actually unnecessary, but I can't get rid of it at the moment, as other tables/Views are linked to it as well.
Here's how it works:
Breakdown Table contains the Breakdown for our Courses. These are fix and won't get changed.
Customer comes in and books a Course, we create a Record in the Course-Bookings with unique CBook_ID (IDENTITY) as the reference. The Break_ID is automatically entered on Application side using the FK to the Breakdown-Table.
Each Course is taught by one or multiple Instructors, and after the Course the Instructors enter the information about the Course-Parts for each course they have done into the WagesEntryNew-Table. On here we have a reference to CBook_ID and Break_ID.
Our Instructors are good in teaching, but not really good in remembering what they've actually been doing, so what we see at the moment is that for a single Course we sometimes get double entries (2 Instructors claim the same CBook_ID) or Course Parts are missing/the wrong Course is chosen.
April 26, 2013 at 7:27 am
carsten.crystaldive (4/25/2013)
Oh Bollocks, I entered the Break_ID 3 twice in my TestData - sorry ... after Removing the first one the statement returns 1 row only ... obviously with the real data Break_ID is UniqueIt was the Champions League Semi-Final ... slowly recovering from it 🙂
These tables are a bit confusing I got to admit - let me try to explain ...
The Breakdown Table has Break_ID (IDENTITY) as PK and is linked to both the Wage Entry-Table and the Course Bookings-Table via FK. One of those Fields (the one in Wages) is actually unnecessary, but I can't get rid of it at the moment, as other tables/Views are linked to it as well.
Here's how it works:
Breakdown Table contains the Breakdown for our Courses. These are fix and won't get changed.
Customer comes in and books a Course, we create a Record in the Course-Bookings with unique CBook_ID (IDENTITY) as the reference. The Break_ID is automatically entered on Application side using the FK to the Breakdown-Table.
Each Course is taught by one or multiple Instructors, and after the Course the Instructors enter the information about the Course-Parts for each course they have done into the WagesEntryNew-Table. On here we have a reference to CBook_ID and Break_ID.
Our Instructors are good in teaching, but not really good in remembering what they've actually been doing, so what we see at the moment is that for a single Course we sometimes get double entries (2 Instructors claim the same CBook_ID) or Course Parts are missing/the wrong Course is chosen.
Champion's league has been fun this year. 😀
So to make sure we are on the same page. I ran this to remove the extra Break_ID from BreakDown.
delete Breakdown where Description = 'OW Standard'
So now I can run this:
select *
from WagesEntryNew w
join CourseBookings c on c.CBook_ID = w.CBook_ID and c.Break_ID = w.Break_ID
join Breakdown b on b.Break_ID = c.Break_ID
where w.CBook_ID = 1000
Does that accurately join these tables together?
Given you explanation I still think the way I would tackle this is have a constraint on WagesEntryNew to ensure that new rows do not exceed the Amount_ACA. From what you have described I don't think you need to persist that data. Let me know and I can help you get the first one situated. You should be able to handle the others once you have a working example.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 26, 2013 at 11:17 am
Champion's league has been fun this year. 😀
It still is, especially if you like Borussia Dortmund 🙂 ...
So to make sure we are on the same page. I ran this to remove the extra Break_ID from BreakDown.
delete Breakdown where Description = 'OW Standard'
So now I can run this:
select *
from WagesEntryNew w
join CourseBookings c on c.CBook_ID = w.CBook_ID and c.Break_ID = w.Break_ID
join Breakdown b on b.Break_ID = c.Break_ID
where w.CBook_ID = 1000
Does that accurately join these tables together?
Yeah, now at least the examples work 🙂 ...
select *
from WagesEntryNew w
join CourseBookings c on c.CBook_ID = w.CBook_ID and c.Break_ID = w.Break_ID
join Breakdown b on b.Break_ID = c.Break_ID
where w.CBook_ID = 1002
Changing the CBook_ID to 1002 though shows you the problem I am facing ... the Total of Amount_Aca for this CBook_ID should not exceed 5 as in defined in Calc_MAXAca, but the total of the two rows sums up to 7 seeing that the first entry already states an Amount_Aca of 5 and the 2nd entry an additional Amount_Aca of 2.
I am looking for a way to check BEFORE inserting a new row, what the remaining Aca for a certain CBook_ID is, so more or less a "depleting" Value for Amount_Aca_LEFT starting from the Max of that Break_ID.
Break_ID 3 for example has 5 as Value for Calc_MAXAca, whereas Break_ID 4 has a Value for 0, so I can't just generalize it by saying Amount_Aca < 5 ...
In the meantime I tried to install Triggers and they do some good but unfortunately not comparing the Amount_Aca already being used for a single CBook_ID, but referring back to the MAXAmount, so I could create a Total Amount_Aca of 15 for the above example with Break_ID 3 (which allows an amount of 5) if I wanted to as long as each Single Entry is not more than 5 ...
Here's my attempt:
First I put a new Column on WagesEntryNew called Calc_MAXAca … putting a Trigger on it that basically fills it with the Max Amount of Aca for each Break_ID … this one get’s the information on insert/update from the Breakdown …
ALTER TABLE WagesEntryNew
ADD Calc_MAXAca INT NULL
GO
CREATE TRIGGER [dbo].[MAXAca]
ON [dbo].[WagesEntryNew]
AFTER INSERT, UPDATE
AS
BEGIN
IF TRIGGER_NESTLEVEL() > 1
RETURN
UPDATE WagesEntryNew
SET [Calc_MAXAca] = cb.[Amount_Aca]
FROM WagesEntryNew w
INNER JOIN Breakdown cb
ON w.[Break_ID] = cb.[Break_ID]
END
Then created a Column Remain_Aca & Trigger which calculates the difference between MAX and Amount Aca on Row basis ...
ALTER TABLE dbo.WagesEntryNew
Add Remain_Aca Int Null
GO
CREATE TRIGGER RemainAca
ON [dbo].[WagesEntryNew]
AFTER INSERT, UPDATE
AS
BEGIN
IF TRIGGER_NESTLEVEL() > 1
RETURN
UPDATE WagesEntryNew
SET [Remain_Aca] = w.[CALC_MAXAca] - w.[Amount_Aca]
FROM WagesEntryNew w
INNER JOIN CourseBookings cob
ON w.[CBook_ID] = cob.[CBook_ID]
END
GO
If I now update a record in WagesEntryNew I get the Update in the INT Column Remain_Aca. Basically the same as a Computed Column with the exception that I can use this "normal" INT-Column to be processed further than a Computed Column.
several problems at this point still:
I need to have some sort of Function like LOOKUP and before inserting a new row sum up the Amount_Aca already entered for a CBook_ID to work out the Remaining_Aca, even if there has been multiple entries done before ...
so let's enter this in our sample:
delete WagesEntryNew where CBook_ID = 1007
insert into WagesEntryNew (Cert_Date, Ins_ID, CBook_ID, Break_ID, Amount_Aca, Amount_Confined, Amount_Dives, Calc_MaxAca)
SELECT '2013-04-30', 2020, 1007, 3, 1, 0, 0, 5 UNION ALL
SELECT '2013-04-30', 2010, 1007, 3, 1, 0, 0, 5 UNION ALL
SELECT '2013-04-30', 2000, 1007, 3, 1, 0, 0, 5 UNION ALL
SELECT '2013-04-30', 2030, 1007, 3, 1, 0, 0, 5 UNION ALL
SELECT '2013-04-30', 2040, 1007, 3, 1, 0, 0, 5 UNION ALL
SELECT '2013-04-30', 2050, 1007, 3, 5, 0, 0, 5
I get a Total of 10 Aca under the same CBook_ID by 6 different Ins_ID ... and there's my problem.
Given you explanation I still think the way I would tackle this is have a constraint on WagesEntryNew to ensure that new rows do not exceed the Amount_ACA. From what you have described I don't think you need to persist that data. Let me know and I can help you get the first one situated. You should be able to handle the others once you have a working example.
I tried to work with Constraints and I use them for example to make sure that only one of the Ins_ID can actually fill a Bit-Field "Cert" with 1, whereas all other entries have to equal 0 or will be rejected, but I can't find a way to use constraints to validate complexer things.
Tried Functions/Indexes as well, but they all limit me to the same problem, that I can't check the contents of existing Rows for input of data in the field Amount_Aca with a specific CBook_ID, sum those values, deduct them from the MAX-Value, so the MAX can be checked against the Value entered before insert of another row ...
Again - any help appreciated - btw: it's actually not a school project or so, it's kinda important and if I could, I would try to do most of it on Application Level - unfortunately I can't - the Application used for data entry is based on .asp though, so if you know any solutions, people, forums, ANYTHING in this area as well, I might be able to pass this on to the Developers of the Application - pheew, no football tonight, might even get some rest 🙂
April 26, 2013 at 2:10 pm
You should be able to add a constraint to WagesEntryNew so that any new data that will be inserted/updated will be subject to meeting the criteria of the constraint.
Something along these lines should be pretty close.
if OBJECT_ID('CheckACA_Remaining') is not null
drop function CheckACA_Remaining
go
create function CheckACA_Remaining
(
@CBook_ID int
) returns int as begin
declare @AmountACA_Remaining int
select @AmountACA_Remaining = b.Amount_ACA - SUM(w.Amount_ACA)
from WagesEntryNew w
join CourseBookings c on c.CBook_ID = w.CBook_ID and c.Break_ID = w.Break_ID
join Breakdown b on b.Break_ID = c.Break_ID
where w.CBook_ID = @CBook_ID
group by b.Amount_Aca, w.Break_ID, w.CBook_ID
return @AmountACA_Remaining
end
go
alter table WagesEntryNew WITH NOCHECK --the NOCHECK means that existing data will not be evaluated
ADD CONSTRAINT WagesEntryNew_CheckACA_Remaining CHECK (Amount_ACA <= dbo.CheckACA_Remaining(CBook_ID))
Now if you try to update any of your existing data and increase the Amount_ACA or insert a new Wages row it will throw an exception. Give this a spin and see if this is close to what you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply