database development project with sql server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

database development project with sql server 2005

Author

Topic

dalichey
Starting Member

3 Posts

Posted - 2012-06-23 : 16:35:21

I have a problem for creating a computed (computation of two columns from different tables), for instance:
Create table employees
(EmpID ...
BillableHours (computed column))
I don't know how to implement that since everything I tried failed.
Also, when it comes to inserting data into tables, I get this error msg:' msg 8152,string or binary data would be truncated' I don't understand what is wrong!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-23 : 18:55:44

what's calculation you want to apply for the column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dalichey
Starting Member

3 Posts

Posted - 2012-06-24 : 03:29:48

the product of two or more columns: humanresources.billimgrate * projectdetails.hoursworked to be saved in projectdetails.billablehours.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-24 : 13:10:44

ok..that cant be done by means of simple expressions as component columns are in different table. How are tables related?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dalichey
Starting Member

3 Posts

Posted - 2012-06-25 : 13:27:49

create table HumanResources.Employees
(EmployeeID smallint identity(00001,1)
constraint pkem_ei primary key(employeeid)
,FirstName varchar(30) not null
,LastName varchar(30)
,Title varchar(30) not null
constraint cti_di check(title in ('Trainee','Team Member','Team Leader','Project Manager','Senior Project Manager'))
,Phone char(15) not null
constraint phc_di check(phone like ('[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'))
,[b]BillingRate[\b] money
constraint cbi_di check(billingrate > 0))

I.1 the phc_ci Phone check constraint doesn't work, the numbers don't fit in the pattern

create table ProjectDetails.TimeCards
(TimeCardID smallint identity(00001,1)
constraint pk_tcid_ei primary key(TimeCardID)
,EmployeeID smallint
constraint fk_empidtc_ei foreign key
references Humanresources.employees(EmployeeID)
,DateIssued datetime
,DaysWorked datetime
constraint ck_dyswrkd_di check(DaysWorked > 0)
,ProjectID smallint
constraint fkpj_ei foreign key
references projectdetails.projects(projectid)
,[b]BillableHours[\b] smallint
constraint ck_billhrs_di check(BillableHours > 0)
,[b]TotalCost[\b] int
,WorkCodeID smallint
constraint fk_wrkcde_ri foreign key
references projectdetails.workcodes(WorkCodeID) )
GO

I.2 TotalCost should be automatically calculated by using the following formula: TotalCost = BillableHours * BillingRate

II.1 If a credit card is not used for payments, CreditCardNumber, CardHoldersName and CreditCardExpDate should be NULL.

II.2 If a credit card is used, the CreditCardExpDate value should be greater than the paymentDate.

create type descrype
from varchar(100) not null

create table Payment.PaymentMethod
(PaymentMethodID smallint identity(00001,1)
constraint pkpmi_ei primary key(paymentmethodid)
,Description descrype)

create table Payment.Payments
(PaymentID smallint identity(00001,1)
constraint pkpi_ei primary key(paymentid)
,ProjectID smallint
constraint fkpj_ei foreign key
references projectdetails.projects(projectid)
,PaymentMethodID smallint
constraint fk_paymethd_ri foreign key
references payment.paymentmethod(paymentmethodid)
,PaymentAmount money
constraint cpa_di check(paymentamount > 0)
,PaymentDue money null
,PaymentDate datetime
,CreditCardNumber char(20)
,CardHoldersName varchar(30)
,CreditCardExpDate datetime))

go

I have fired this trigger for these business rules:

alter trigger aftrig_ppcc on payment.payments
after update
as
declare @paymethd smallint
select @paymethd = paymentmethodid from payment.payments
if @paymethd = 2
begin
update payment.payments
set creditcardnumber = null, cardholdersname = null, creditcardexpdate = null
where paymentmethodid = 2
end
else if @paymethd = 1
begin
declare @cced datetime, @paydate datetime
select @cced = creditcardexpdate from payment.payments
select @paydate = paymentdate from payment.payments
if @cced < @paydate
begin
print 'The Credit Card expiry date should be greater than the payment date'
rollback transaction
end
end
go

It works for II.2 but not for II.1 . Thank you for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources