June 24, 2005 at 11:01 am
Say I select values within a stored proc like so
SELECT EventID from Event where Column1 = 1
And it returns
1
2
3
How can I then say
INSERT INTO ProjectEvent (ProjectID, EventID) Values (1,1)
INSERT INTO ProjectEvent (ProjectID, EventID) Values (1,2)
INSERT INTO ProjectEvent (ProjectID, EventID) Values (1,3)
I won't know how many EventIDs I will be getting back in the first SELECT. I could do a for each in the C#, but that seems silly. There has to be a way to do within the SP. Thoughts? Thanks for looking!
Harry C
June 24, 2005 at 1:11 pm
Now to Calculate the EstimatedShipDate...any help is greatly appreciated. Thanks
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
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
June 24, 2005 at 1:40 pm
I don't fully understand your logic for all cases but this may put you in the right Track:
select
Project,
EventID,
DateAdd( day,
(select sum(Leadtime)
from yourtable t2
where t1.Project =t2.Project
and
t1.EventID <= t2.EventID) --TotalLeadTime
, @CustomerShipDate ) ShipDate
from Yourtable t1
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply