replace a column with a joined table

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

Transact-SQL (2005)

replace a column with a joined table

Author

Topic

mroll
Starting Member

4 Posts

Posted - 2010-01-14 : 10:23:05

I have the following stored procedure that returns my results. I need to replace the IsIndexed column with a case statement that checks the column in a new joined table (it may be null). I am not sure how to accomplish this and it is kicking my butt. Any help would be appreciated.
WORKING SQL excerpted from Stored Procedure:
select
PLS.Credentials,
PLS.OriginalCitation,
PLS.CriticalSummaryConclusion,
PLS.ClinicalQuestion,
PLS.ReviewConclusionDate,
PLS.ReviewMethods,
PLS.MainResults,
PLS.Conclusions,
PLS.SourceOfFunding,
PLS.ImportanceAndContext,
PLS.StrengthsAndLimitationsOfSR,
PLS.StrengthsAndLimitationsOfEvidence,
PLS.ImplicationsForDentalPractice,
PLS.GeneralComments,
PLS.JADAURL,
PLS.AddToJADA,
PLS.Status as PlainLanguageSummaryStatus,

PLS.SystematicReviewId,
PLS.Id as PLSId,

-- REPLACE THE CASE BELOW ...
case PLS.Indexed
when 1 then 'Yes'
else 'No'
end as IsIndexed,
-- ... WITH THIS
-- CASE WHEN C.ClinicalTopicId is null THEN 'No' ELSE 'Yes' END as IsIndexed,

case PLS.SetPriority
when 1 then 'Yes'
else 'No'
end as IsSetPriority,

SR.*,
LastName+', '+FirstName as UserName,
dateadd(d,@DueDays,CS.CreatedDate) as DueDate

from ADCollaborationTeam CT,
ADCollaborationTeamUsers TU,
ADUsers U,
ADSystematicReview SR,
ADPlainLanguageSummary PLS,
ADCriticalSummary CS

left join ( select (sum(cast(RateSummary as float))/Count(SystematicReviewId)) RateSummary,SystematicReviewId
from ADPublicRatingCriticalSummary
Group by SystematicReviewId) PR
on PR.SystematicReviewId = CS.SystematicReviewId

-- WHERE DO I ADD THE JOIN FOR CategoryClinicalTopic ???
-- left join
-- (select distinct ClinicalTopicId from ADCategoryClinicalTopic) C
-- on SR.SRId=C.ClinicalTopicId

where PLS.CriticalSummaryId=CS.Id and
CS.SystematicReviewId=SR.Id and
SR.Id=CT.ClinicalTopicId and
CT.Id=TU.CollaborationTeamId and
TU.UserId=U.Id and
TU.RoleId=100 and
TU.IsPrimaryAER = 1

I need to know if the PLS.Id exists in the CategoryClinicalTopicId table so I would add something like this (as commnted above):
left join
(select distinct ClinicalTopicId from ADCategoryClinicalTopic) C
on SR.SRId=C.ClinicalTopicId
Any help would be GREATLY appreciated.

Mike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 10:28:27

[code]
select
PLS.Credentials,
PLS.OriginalCitation,
PLS.CriticalSummaryConclusion,
PLS.ClinicalQuestion,
PLS.ReviewConclusionDate,
PLS.ReviewMethods,
PLS.MainResults,
PLS.Conclusions,
PLS.SourceOfFunding,
PLS.ImportanceAndContext,
PLS.StrengthsAndLimitationsOfSR,
PLS.StrengthsAndLimitationsOfEvidence,
PLS.ImplicationsForDentalPractice,
PLS.GeneralComments,
PLS.JADAURL,
PLS.AddToJADA,
PLS.Status as PlainLanguageSummaryStatus,

PLS.SystematicReviewId,
PLS.Id as PLSId,
CASE WHEN C.ClinicalTopicId is null THEN 'No' ELSE 'Yes' END as IsIndexed,

case PLS.SetPriority
when 1 then 'Yes'
else 'No'
end as IsSetPriority,

SR.*,
LastName+', '+FirstName as UserName,
dateadd(d,@DueDays,CS.CreatedDate) as DueDate

from ADCollaborationTeam CT
join ADCollaborationTeamUsers TU
on CT.Id=TU.CollaborationTeamId
join ADUsers U
on TU.UserId=U.Id
join ADSystematicReview SR
on SR.Id=CT.ClinicalTopicId
join ADCriticalSummary CS
on CS.SystematicReviewId=SR.Id
join ADPlainLanguageSummary PLS
on PLS.CriticalSummaryId=CS.Id
left join ( select (sum(cast(RateSummary as float))/Count(SystematicReviewId)) RateSummary,SystematicReviewId
from ADPublicRatingCriticalSummary
Group by SystematicReviewId) PR
on PR.SystematicReviewId = CS.SystematicReviewId
left join
(select distinct ClinicalTopicId from ADCategoryClinicalTopic) C
on SR.SRId=C.ClinicalTopicId
where TU.RoleId=100 and
TU.IsPrimaryAER = 1
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources