Help with Date Calc - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Help with Date Calc

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-06-24 : 16:00:48

SORRY! This is a crosspost from the administration forum, but this is where I should have posted...sorry that it is in both places, please reply here...

The Select Statement above would return a record like so
ProjectID :: EventID :: EstimatedCompletionDate :: LeadTime
1 :: 1 :: :: 10
1 :: 2 :: :: 20
1 :: 3 :: :: 5

So, what I need to do here is to take @CustomerShipDate, which is passed into the stored proc, the count the days backward. So, if @CustomerShipDate were 07/25/2005, and I were calculating for EventID 3. I could say DateAdd(day, LeadTime(which is 5), @CustomerShipDate).

But, now, how would I get the rest of the values?? Because now, for EventID 2, I need to say
DateAdd(day, LeadTime(EventID 3 LeadTime which is 5 MINUS EventID 2 LeadTime which is 20 ), @CustomerShipDate)

Does this makes sense? Thanks for looking

INSERT INTO ProjectEvent(ProjectID, EventID, EstimatedCompletionDate, LeadTime)
SELECT @ProjectID, CompanySeasonProductTemplate.EventID, (Calculate Estimated CompletionDate Here), Event.LeadTime
FROM CompanySeasonProductTemplate INNER JOIN
Event ON CompanySeasonProductTemplate.EventID = Event.EventID
WHERE (CompanySeasonProductTemplate.CompanyID = @CompanyID)
AND SeasonID Is Null
AND ProductCategoryID Is Null
ORDER BY EventOrder

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-24 : 18:41:00

I've had to guess a little as to your table structures, but this is a potential way to do it:

INSERT INTO ProjectEvent(ProjectID, EventID, EstimatedCompletionDate, LeadTime)
SELECT @ProjectID, CompanySeasonProductTemplate.EventID, DATEADD(d, Event.Leadtime, Z.CSD), Event.LeadTime
FROM CompanySeasonProductTemplate INNER JOIN
Event ON CompanySeasonProductTemplate.EventID = Event.EventID
LEFT JOIN
(SELECT @CustomerShipDate as CSD, CompanyID, MAX(EventID) AS MaxEventID FROM CompanySeasonProductTemplate GROUP BY CompanyID) AS Z
ON CompanySeasonProductTemplate.CompanyID = Z.CompanyID AND CompanySeasonProductTemplate.EventID = Z.MaxEventID
WHERE (CompanySeasonProductTemplate.CompanyID = @CompanyID)
AND SeasonID Is Null
AND ProductCategoryID Is Null
ORDER BY CompanySeasonProductTemplate.EventID DESC

DECLARE @CompletionDate datetime
SELECT @CompletionDate = EstimatedCompletionDate FROM ProjectEvent WHERE EstimatedCompletionDate IS NOT NULL

UPDATE ProjectEvent
SET @CompletionDate = EstimatedCompletionDate = DATEADD(d, leadtime, @CompletionDate)
WHERE EstimatedCompletionDate IS NULL

The INSERT just inserts the the EstimatedCompletionDate, based on the biggest EventID. The UPDATE updates the rest of the dates in descending order. The columns might be a little off, but I think you'll get the general idea.

Harry C
Posting Yak Master

148 Posts

Posted - 2005-06-25 : 11:25:15

This actually ended up working. I did not figure this out myself, someone on another board did. I very much appreciate the help here. Thanks

INSERT INTO ProjectEvent
( ProjectID, EventID, LeadTime, EstimatedCompletionDate )
SELECT @ProjectID, CSPD.EventID, E1.LeadTime,
( SELECT DATEADD( d, -SUM( E2.LeadTime ), @CustomerShipDate )
FROM Event AS E2
WHERE E2.EventID <= E1.EventID ) AS EstimatedCompletionDate
FROM CompanySeasonProductTemplate AS CSPD, Event AS E1
WHERE CSPD.EventID = E1.EventID
AND CSPD.CompanyID = @CompanyID
AND SeasonID Is Null
AND ProductCategoryID Is Null
ORDER BY EventOrder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources