Avatar of akiles99
akiles99
Flag for India asked on

Trigger for experts only

Hello guys,
I have two tables namely conthist and contact2....
I need a trigger when a insert or update in done on conthist it fires the trigger and the values of the column duration in conthist is stored in UYTD column of contact2....COnthist table also has a column named ondate where  sales date are going to be stored...But with more modifications thats why i'm posting this to experts like you...see more details below...

YTD Sales - Trigger name
Purpose: To populate the field with the amount of the year to date sales activity of the contact record.
How: This will come from the history table (ContHist) as the total amount of the last years sales
activity. This would be adjusted for each year going forward and will require the zeroing out of
the data at the turn of the year. This existing data, in UYTD, would be move to the ULYRSALES
on the first day of the new year.
Using a RecType Code of S and the DURATION field as the amount.
Update the UDF of Contact2 named UYTD
Note: There will be more than one sale item on any given date, as the items are entered into the database
as single line items; but all on one quote/order. You must be able to aggregate the total amount of
the sales and display that aggregated total in the UYTD field...

EX:
If a set of sales occured before 1/1/2008, then it would not be part of this year to
date sales....
According to the specs, the data would move to the ULYRSALES field
when the System date turned past 1/1/2009 .

ALso in same trigger i need a nested onewhich increment up by 1 day every day that lapse past the last
sale date....

Cheers....
akiles
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
akiles99

8/22/2022 - Mon
Daniel Wilson

Please provide the structure of the tables ... and a few rows of sample data.

Thanks.
akiles99

ASKER
Hi,

I attached the structure of the table and some sample values ...the contact2 table has values same as conthist for the first 2 fields...
for 3 &4 only we have to write a tigger....
ee.xls
ASKER CERTIFIED SOLUTION
Daniel Wilson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
akiles99

ASKER
Thanks for ur time mr.Danielwilson ...I test it and let u know...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
akiles99

ASKER
thanks