Modify the query - SQL Server Forums

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

Transact-SQL (2008)

Modify the query

Author

Topic

JungleeGeorge
Starting Member

1 Post

Posted - 2013-09-03 : 06:58:03

Please help me to modify the query such that LEFT JOIN on activities, typeofactivities can be removed, so that query performance is good? This is my query:
SELECT S.studyCode,
SOP.operatorName AS StudyDirector,
A.activityCode,
CASE ISNULL(CATCA.activityCategoryName, '') WHEN '' THEN TOA.typeOfActivityName
ELSE TOA.typeOfActivityName + ' ; ' + CATCA.activityCategoryName END AS PhaseType,
AOP.operatorName AS ActivityOwner,
SDAAFV.txtValue AS TQSDComment,
PIAAFV.txtValue AS TQPIComment

FROM Activities A WITH(NOLOCK)
INNER JOIN TypesOfActivities TOA ON A.typeOfActivityIncId = TOA.typeOfActivityIncId AND A.typeOfActivitySqlId = TOA.typeOfActivitySqlId AND TOA.isDeleted=0x0
INNER JOIN ActivitiesCategories AC ON A.activityCategoryIncId = AC.activityCategoryIncId AND A.activityCategorySqlId = AC.activityCategorySqlId AND AC.isDeleted=0x0
INNER JOIN Studies S ON A.studyIncId = S.studyIncId AND A.studySqlId = S.studySqlId AND S.isDeleted=0x0
INNER JOIN TypesOfStudies TOS ON S.typeOfStudyIncId = TOS.typeOfStudyIncId AND S.typeOfStudySqlId = TOS.typeOfStudySqlId AND TOS.isDeleted=0x0

--Filter on AF with extractName 'TrialEvaluation' AND the AFValue 'major issue : trial not valid/cancelled ' (But use the AFV code instead of the name)
LEFT JOIN ActivitiesAdditionalFieldsValues AS AAFV WITH(NOLOCK) ON A.activityIncId=AAFV.activityIncId AND A.activitySqlId=AAFV.activitySqlId AND AAFV.isDeleted=0x0
INNER JOIN ActivitiesAdditionalFields AS AAF WITH(NOLOCK) ON AAFV.activityAdditionalFieldIncId=AAF.activityAdditionalFieldIncId AND AAFV.activityAdditionalFieldValueSqlId=AAF.activityAdditionalFieldSqlId AND AAF.isDeleted=0x0
----AND AAF.extractName = 'TrialEvaluation'
INNER JOIN CboValues CBOV ON AAFV.cboRecordIncId = CBOV.cboValueIncId AND AAFV.cboRecordSqlId = CBOV.cboValueSqlId AND CBOV.isDeleted=0x0
----AND CBOV.cboValueCode = 'EAS-3003' -->'major issue : trial not valid/cancelled '

LEFT JOIN Operators AS AOP ON A.todoBySqlId = AOP.operatorSqlId AND A.todoByIncId = AOP.operatorIncId
LEFT JOIN Operators AS SOP ON S.directedBySqlId = SOP.operatorSqlId AND S.directedByIncId = SOP.operatorCategoryIncId

--To create PhaseType: Get the child activity with 'Running' in the typeOfActivityName
LEFT JOIN Activities AS CA
LEFT JOIN ActivitiesCategories AS CATCA ON CATCA.activityCategorySqlId=CA.activityCategorySqlId AND CATCA.activityCategoryIncId=CA.activityCategoryIncId AND CATCA.isDeleted=0x0
LEFT JOIN TypesOfActivities AS TOCA ON CA.typeOfActivitySqlId=TOCA.typeOfActivitySqlId AND CA.typeOfActivityIncId=TOCA.typeOfActivityIncId AND TOCA.isDeleted=0x0
ON A.activitySqlId=CA.fatherActivitySqlId AND A.activityIncId=CA.fatherActivityIncId AND CA.isDeleted=0x0
---AND TOCA.typeOfActivityName Like '%Running%'

--Get TQSD comment
LEFT JOIN ActivitiesAdditionalFieldsValues AS SDAAFV
LEFT JOIN ActivitiesAdditionalFields AS SDAAF WITH(NOLOCK) ON SDAAFV.activityAdditionalFieldIncId=SDAAF.activityAdditionalFieldIncId AND SDAAFV.activityAdditionalFieldValueSqlId=SDAAF.activityAdditionalFieldSqlId AND SDAAF.isDeleted=0x0
ON A.activityIncId=SDAAFV.activityIncId AND A.activitySqlId=SDAAFV.activitySqlId AND SDAAFV.isDeleted=0x0
AND SDAAF.extractName = 'TQSDComment'

--Get TQPI comment
LEFT JOIN ActivitiesAdditionalFieldsValues AS PIAAFV
LEFT JOIN ActivitiesAdditionalFields AS PIAAF WITH(NOLOCK) ON PIAAFV.activityAdditionalFieldIncId=PIAAF.activityAdditionalFieldIncId AND PIAAFV.activityAdditionalFieldValueSqlId=PIAAF.activityAdditionalFieldSqlId AND PIAAF.isDeleted=0x0
ON A.activityIncId=PIAAFV.activityIncId AND A.activitySqlId=PIAAFV.activitySqlId AND PIAAFV.isDeleted=0x0
AND PIAAF.extractName = 'TQPIComment'

WHERE A.isDeleted=0x0
AND TOS.typeOfStudyCode = 'EAS-01'
AND TOA.typeOfActivityCode = 'EAS-1'
AND AC.activityCategoryCode = 'EAS-1F'
AND AAF.extractName = 'TrialEvaluation'
AND CBOV.cboValueCode = 'EAS-3003' -->'major issue : trial not valid/cancelled '
AND (A.activityStartDate < DateAdd(DAY,1,@ToDate) AND A.activityEndDate >= @FromDate)

ORDER BY S.studyCode, A.activityCode

I want a query like this. Please help..Urgent...

CREATE Table #Tab (studyCode, StudyDirector, activityCode, PhaseType, ActivityOwner,TQSDComment,TQPIComment)

INSERT #Tab (studyCode, StudyDirector, activityCode, PhaseType, ActivityOwner,TQSDComment,TQPIComment)

SELECT S.studyCode,
SOP.operatorName AS StudyDirector,
A.activityCode,0,0,0,0
FROM Activities A WITH(NOLOCK)
INNER JOIN TypesOfActivities TOA ON A.typeOfActivityIncId = TOA.typeOfActivityIncId AND A.typeOfActivitySqlId = TOA.typeOfActivitySqlId AND TOA.isDeleted=0x0
INNER JOIN ActivitiesCategories AC ON A.activityCategoryIncId = AC.activityCategoryIncId AND A.activityCategorySqlId = AC.activityCategorySqlId AND AC.isDeleted=0x0
INNER JOIN Studies S ON A.studyIncId = S.studyIncId AND A.studySqlId = S.studySqlId AND S.isDeleted=0x0
INNER JOIN TypesOfStudies TOS ON S.typeOfStudyIncId = TOS.typeOfStudyIncId AND S.typeOfStudySqlId = TOS.typeOfStudySqlId AND TOS.isDeleted=0x0

UPDATE #Tab SET TQSDComment = SDAAFV.txtValue AS TQSDComment FROM ActivitiesAdditionalFieldsValues AS SDAAFV
LEFT JOIN ActivitiesAdditionalFields AS SDAAF WITH(NOLOCK) ON SDAAFV.activityAdditionalFieldIncId=SDAAF.activityAdditionalFieldIncId AND SDAAFV.activityAdditionalFieldValueSqlId=SDAAF.activityAdditionalFieldSqlId AND SDAAF.isDeleted=0x0
ON A.activityIncId=SDAAFV.activityIncId AND A.activitySqlId=SDAAFV.activitySqlId AND SDAAFV.isDeleted=0x0
AND SDAAF.extractName = 'TQSDComment'

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-03 : 08:59:35

ON CA.typeOfActivitySqlId=TOCA.typeOfActivitySqlId
AND CA.typeOfActivityIncId=TOCA.typeOfActivityIncId
AND TOCA.isDeleted=0x0
ON A.activitySqlId=CA.fatherActivitySqlId
AND A.activityIncId=CA.fatherActivityIncId AND CA.isDeleted=0x0

How To Run The Query Without Any Join (Observe above Query)

veeranjaneyulu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-03 : 09:35:30

first provide some sample data and explain what you want as output. just removing LEFT JOIN may not give you your desired output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources