Hi All
i need to add a new columns in my select result based on specific values in a column.
CustomerID ParcelID CustStatus OrderID OrderParcelID ParcelStatus
S10000024 2010885 OPEN 31345 0 SUSPENDED
S10000024 2010885 OPEN 31346 0 SUSPENDED
S10000024 2010885 OPEN 31344 2010885 ACTIVE
S10000081 2010891 OPEN 31365 0 SUSPENDED
S10000081 2010891 OPEN 31366 0 SUSPENDED
S10000081 2010891 OPEN 31362 2010891 ACTIVE
S10000081 2010891 OPEN 31363 2010891 SUSPENDED
S10000081 2010891 OPEN 31364 2010891 ACTIVE
I have the able table from below SQL
select A.CUstomerID,P.ParcelID,C.[Status] as CustStatus,OT.OrderID,
OT.ParcelID as OrderParcelID, P.[Status] as ParcelStatus,
from customer C
join ordertable OT
on C.CustomerID = OT.CustomerID
join Parcel P
on P.ParcelID= OT.ParcelID
Now I want SQL to display the result set in below format
CustomerID ParcelID CustStatus OrderID OrderParcelID ParcelStatus ProcStatus
S10000024 2010885 OPEN 31345 0 SUSPENDED SUSPENDED
S10000024 2010885 OPEN 31346 0 SUSPENDED SUSPENDED
S10000024 2010885 OPEN 31344 2010885 ACTIVE ACTIVE
S10000081 2010891 OPEN 31365 0 SUSPENDED SUSPENDED
S10000081 2010891 OPEN 31366 0 SUSPENDED SUSPENDED
S10000081 2010891 OPEN 31362 2010891 ACTIVE ACTIVE
S10000081 2010891 OPEN 31363 2010891 SUSPENDED ACTIVE
S10000081 2010891 OPEN 31364 2010891 ACTIVE ACTIVE
i.e if we observe 3 columns from above table (CustomerID,OrderParcelID and PracelStatus) we need to added new column called ProcStatus, where this column has to get the status as Active even though it's status in ParcelStatus column is SUSPENDED.
and that is only when a customer has at least one Active status in ParcelStatus for any OrderID that has same/common in OrderParcelID.
Please help me here.
Thanks
chiru