Defaulting to certain values
-
As you can see, I have this select statement that generates some values based on a passed in @ProjectID(NOT NULL), @CustomerShipDate(NOT NULL), @CompanyID(NOT NULL), @SeasonID(NULL), @ProductCategoryID(NULL).
Now, sometimes a user may pass in a SeasonID or a ProductCategoryID, but a CustomerSeasonProductTemplate may not exist for that combination. In that case, I would want it to default to a Just the Company Template(Trust that constaints ARE enforced in the DB, so no dups are allowed.)
Any thoughts on how to do this?
Again, one last example. A user puts in "Dell", "Spring", "PC", but no template exists for that, but one DOES exists for just "Dell", "Spring", so that template would get selected. Thoughts?
SELECT @ProjectID, CSPD.EventID, E1.LeadTime,
( SELECT DATEADD( d, -SUM( E2.LeadTime ), @CustomerShipDate )
FROM CompanySeasonProductTemplate AS CSPD2, Event AS E2
WHERE CSPD2.EventID = E2.EventID
AND CSPD2.CompanyID = @CompanyID
AND SeasonID = @SeasonID
AND ProductCategoryID = @ProductCategoryID
AND E2.EventID >= E1.EventID ) AS EstimatedCompletionDate
FROM CompanySeasonProductTemplate AS CSPD, Event AS E1
WHERE CSPD.EventID = E1.EventID
AND CSPD.CompanyID = @CompanyID
AND SeasonID = @SeasonID
AND ProductCategoryID = @ProductCategoryID
ORDER BY EventOrder
-
just a first idea, early in the morning :
SELECT @ProjectID, CSPD.EventID, E1.LeadTime
, isnull(( SELECT DATEADD( d, -SUM( E2.LeadTime ), @CustomerShipDate )
FROM CompanySeasonProductTemplate AS CSPD2, Event AS E2
WHERE CSPD2.EventID = E2.EventID
AND CSPD2.CompanyID = @CompanyID
AND SeasonID = @SeasonID
AND ProductCategoryID = @ProductCategoryID
AND E2.EventID >= E1.EventID )
, your other solution stuff
) AS EstimatedCompletionDate
FROM CompanySeasonProductTemplate AS CSPD, Event AS E1
WHERE CSPD.EventID = E1.EventID
AND CSPD.CompanyID = @CompanyID
AND SeasonID = @SeasonID
AND ProductCategoryID = @ProductCategoryID
ORDER BY EventOrder
There is always more than one way to skin a kat
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
btw, suppose your in-line-select returns more than one result ?
Wich one to pick ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for the reply..I actually just ended up using a variable to see whether a result is being found. Then in each IF statement, I check for @Found. If it is 0, then I try to find another value. If it is 1, then I am done and return the results. Thanks!
SET @Found = 0
IF (SELECT Count(*) FROM CompanySeasonProductTemplate
WHERE CompanyID = @CompanyID AND SeasonID=@SeasonID AND ProductCategoryID=@ProductCategoryID)>0
BEGIN
INSERT INTO ProjectEvent
( ProjectID, EventID, LeadTime, EstimatedCompletionDate )
SELECT @ProjectID, CSPD.EventID, E1.LeadTime,
( SELECT DATEADD( d, -SUM( E2.LeadTime ), @CustomerShipDate )
FROM CompanySeasonProductTemplate AS CSPD2, Event AS E2
WHERE CSPD2.EventID = E2.EventID
AND CSPD2.CompanyID = @CompanyID
AND SeasonID=@SeasonID
AND ProductCategoryID = @ProductCategoryID
AND E2.EventID >= E1.EventID ) AS EstimatedCompletionDate
FROM CompanySeasonProductTemplate AS CSPD, Event AS E1
WHERE CSPD.EventID = E1.EventID
AND CSPD.CompanyID = @CompanyID
AND CSPD.SeasonID = @SeasonID
AND CSPD.ProductCategoryID = @ProductCategoryID
ORDER BY EventOrder
SET @Found=1
END
-
ooh it must have been realy early in the morning ....
this inline select performs only a sum, so no duplicates
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply