sql server programming: Trigger - Calculate date based on two other dates -- sql server programming

RE: Trigger - Calculate date based on two other dates

Bob
9/16/2007 7:46:01 AM

I've got an example, maybe you could hack the bits you need out for your
trigger?

-- Create percentages table; just for the demo
DECLARE @action_percentages TABLE( action_number INT PRIMARY KEY,
action_percentage DECIMAL( 3, 2 ) NOT NULL CHECK ( action_percentage < 1 ) )

SET NOCOUNT ON

INSERT INTO @action_percentages VALUES( 1, .55 )
INSERT INTO @action_percentages VALUES( 2, .30 )
INSERT INTO @action_percentages VALUES( 3, .05 )
INSERT INTO @action_percentages VALUES( 4, .05 )
INSERT INTO @action_percentages VALUES( 5, .05 )

SET NOCOUNT OFF

-- Work out the difference between the two dates, then multiply that by the
percentages
DECLARE @start_date DATETIME, @end_date DATETIME
DECLARE @diff INT

-- Initialise the dates
SELECT @start_date = '20070101', @end_date = '20071231'

-- Work out the number of days between the two dates
SET @diff = DATEDIFF( d, @start_date, @end_date )

-- Apply the percentages to the dates
SELECT DATEADD( day, @diff * x.running_total, @start_date )
FROM
(
-- Create running total of percentages
SELECT a.action_number, SUM( b.action_percentage ) AS running_total
FROM @action_percentages a CROSS JOIN @action_percentages b
WHERE a.action_number >= b.action_number
GROUP BY a.action_number
) x
GO

Hope that makes sense. Let me know how you get on.

wBob
Rate the post if it's right!

[quoted text, click to view]

Re: Trigger - Calculate date based on two other dates

Erland Sommarskog
9/16/2007 2:43:23 PM

FinnGirl (FinnGirl@discussions.microsoft.com) writes:
[quoted text, click to view]

Since I don't know your tables, I will have to guess a bit. But this should
give you the gist of it:

CREATE TRIGGER milstone_tri ON milestones FOR INSERT, UPDATE AS

INSERT actions (milestoneid, day1, day2, day3, day4, day5)
SELECT a.milestoneid,
dateadd(DAY, 0.55*datediff(DAY, a.date, b.date), a.date),
dateadd(DAY, 0.85*datediff(DAY, a.date, b.date), a.date),
dateadd(DAY, 0.90*datediff(DAY, a.date, b.date), a.date),
dateadd(DAY, 0.95*datediff(DAY, a.date, b.date), a.date),
b.date
FROM milestones a
JOIN milestones b ON a.milestoneid = b.milestoneid
WHERE a.date IS NOT NULL
AND b.date IS NOT NULL
AND EXISTS (SELECT *
FROM inserted i
WHERE a.miltestoneid = i.milestoneid)

You probably have to consider how rounding should strike here.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Re: Trigger - Calculate date based on two other dates

FinnGirl
9/16/2007 5:08:00 PM

Correct. The records are related through the "GeneralMasterID" column.
Thanks.

Table - GeneralActions
Column Data Type
GeneralActionID int (PK)
GeneralMasterID int (FK)
Action varchar(30)
DueDate datetime

Table - GeneralMilestones
Column Data Type
GeneralMilestoneID int (PK)
GeneralMasterID int (FK)
Milestone varchar(20)
MilestoneDate datetime

Table - GeneralMasters
Column DataType
GeneralMasterID int (PK)
....
--
Finn Girl

[quoted text, click to view]

Re: Trigger - Calculate date based on two other dates

Tom Moreau
9/17/2007 5:53:37 PM

So it would appear that the following might be a solution:

create trigger tri_GeneralMilestones on GeneralMilestones after insert
as

if @@ROWCOUNT = 0
return

insert GeneralActions
select
i.GeneralMilestoneID
, i.GeneralMasterID
, 'Action ' + str (x.Id)
, dateadd (dd, pct * datediff (dd, m.MilestoneDate, i.MilestoneDate) /
100.0, m.MilestoneDate)
from
inserted i
join
GeneralMilestones m on m.GeneralMasterID = i.GeneralMasterID
cross join
(
select 1, 55 union all
select 2, 30 union all
select 3, 5 union all
select 4, 5 union all
select 5, 5
) as x (Id, pct)
where
i.Milestone = 'Milestone B'
and
m.Milestone = 'Milestone A'
go

That said, I think the percentages of time need to be rethought out. I
think you gave us only the deltas. Therefore, I believe the following is
the correct approach:

create trigger tri_GeneralMilestones on GeneralMilestones after insert
as

if @@ROWCOUNT = 0
return

insert GeneralActions
select
i.GeneralMilestoneID
, i.GeneralMasterID
, 'Action ' + str (x.Id)
, dateadd (dd, pct * datediff (dd, m.MilestoneDate, i.MilestoneDate) /
100.0, m.MilestoneDate)
from
inserted i
join
GeneralMilestones m on m.GeneralMasterID = i.GeneralMasterID
cross join
(
select 1, 55 union all
select 2, 85 union all
select 3, 90 union all
select 4, 95 union all
select 5, 100
) as x (Id, pct)
where
i.Milestone = 'Milestone B'
and
m.Milestone = 'Milestone A'
go

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

[quoted text, click to view]
Correct. The records are related through the "GeneralMasterID" column.
Thanks.

Table - GeneralActions
Column Data Type
GeneralActionID int (PK)
GeneralMasterID int (FK)
Action varchar(30)
DueDate datetime

Table - GeneralMilestones
Column Data Type
GeneralMilestoneID int (PK)
GeneralMasterID int (FK)
Milestone varchar(20)
MilestoneDate datetime

Table - GeneralMasters
Column DataType
GeneralMasterID int (PK)
....
--
Finn Girl

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.