July 8, 2013 at 10:02 am
Hello,
I have a column called OrderStatus (int) which belongs to a table called ProspectOrder
If this OrderStatus is 1 then i need to return CreatedDate, CreatedBy
If this OrderStatus is 2 then I need to Return CancelledBy, CancelledDate
If this OrderStatus is 3 then i need to return GPAddedBy, GPAddedDate,
Im struggling on how i can accomplish this, below is my main select statement which is working correctly,
Select po.OrderID, u.Firstname+ ' ' +u.Surname as CreatedBy, CONVERT(VARCHAR(10), po.CreatedDate, 105) as CreatedDate, po.OrderGuid,
pls.ProspectLeadStatusDescription as CurrentStatus
from ProspectOrder po
join UserAccount u on po.Createdby = u.shortabbr
join ProspectLeadStatus pls on @CurrentStatus = pls.ProspectLeadStatusID
where po.ProspectID = 42163
Below is what SQL example of what im trying to achieve depending on the OrderStatus
Declare @CurrentStatus int = (Select OrderStatus from ProspectOrder where ProspectID = @ProspectID)
if(@CurrentStatus = 1) -- Confirmed Order
begin
Select p.CreatedDate as LastStatusUpdatedDate, u.Firstname + ' ' + u.Surname as LastUpdatedBy
from ProspectOrder p
join UserAccount u on p.Createdby = u.ShortAbbr
where p.ProspectID = @ProspectID
end
else if(@CurrentStatus = 2) -- Cancelled Order
begin
Select p.CancelledDate as LastStatusUpdatedDate, u.Firstname + ' ' + u.Surname as LastUpdatedBy
from ProspectOrder p
join UserAccount u on p.CancelledBy = u.ShortAbbr
where p.ProspectID = @ProspectID
end
else -- Sale Order
begin
Select p.GpAddedDate as LastStatusUpdatedDate, u.Firstname + ' ' + u.Surname as LastUpdatedBy
from ProspectOrder p
join UserAccount u on p.Createdby = u.ShortAbbr
where p.ProspectID = @ProspectID
end
So depending on what the OrderStatus is will determin what two extra columns i include in my main select if that makes sense?
Im sure this is quite straight forward for an expert but im struggling with it 🙁 any help would be highly appreciated.
July 8, 2013 at 10:16 am
Not sure if this is more efficient and you'd have to test, but you could join with an OR and then use a CASE statement.
Select
case
when @CurrentStatus = 1
then p.CreatedDate
when @CurrentStatus = 2
the p.CancelledDate
when @CurrentStatus = 3
then p.CreatedDate
end as LastStatusUpdatedDate
, u.Firstname + ' ' + u.Surname as LastUpdatedBy
from ProspectOrder p
inner join UserAccount u
on p.Createdby = u.ShortAbbr
or p.CancelledBy = u.ShortAbbr
where p.ProspectID = @ProspectID
Note that I actually prefer the way you are doing it, though I might make each of the selects another stored proc. That way that logic could be reused and this proc just does the switching if you don't know which item the user selects.
July 8, 2013 at 10:24 am
You should read this article from Gail. It is discussing this type of query and the performance issues that arise from it.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply