5280_Lifestyle (4/3/2013)
As for the ANSI joins, when I tried switching to ANSI some of the rows were left out. With the old outer joins, the output was 243 rows. With the ANSI joins, the output was 239 rows. That's why I left those annoying outer joins in the query. I have roughly 8 months of sql experience, so I'm definitely still wearing my training wheels. I wasn't thinking this (GROUPED.TOTAL_DURATION = AL3.DURATION) all the way through, so bye-bye Duration.Jo, I like the way you have the query coded and I will try that out right away.
No worries about training wheels. My guess is that when you used correct joins you accidentally fat fingered something. Also as for the two "groups" of OR sections there is no point. They are all OR so there is no need to use the extra conditions.
WHERE
(
(AL21.SUBCASE_NUMBER LIKE '2049356%'
OR AL21.SUBCASE_NUMBER LIKE '2049462%'
OR AL21.SUBCASE_NUMBER LIKE '2057852%'
OR AL21.SUBCASE_NUMBER LIKE '2057877%'
OR AL21.SUBCASE_NUMBER LIKE '2057897%'
OR AL21.SUBCASE_NUMBER LIKE '2057930%'
OR AL21.SUBCASE_NUMBER LIKE '2057948%'
OR AL21.SUBCASE_NUMBER LIKE '2057963%'
OR AL21.SUBCASE_NUMBER LIKE '2057984%'
OR AL21.SUBCASE_NUMBER LIKE '2057996%'
OR AL21.SUBCASE_NUMBER LIKE '2058015%'
OR AL21.SUBCASE_NUMBER LIKE '2058032%'
OR AL21.SUBCASE_NUMBER LIKE '2058049%'
OR AL21.SUBCASE_NUMBER LIKE '2066771%'
)
OR
(
AL21.SUBCASE_TITLE LIKE '%CITS Audit%'
OR AL21.SUBCASE_TITLE LIKE '%CITS Audits%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDIT%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_Audit%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_AUDITS%'
OR AL21.SUBCASE_TITLE LIKE '%CITS_Audits%'
OR AL21.SUBCASE_TITLE LIKE '%CITS-Audit%'
OR AL21.SUBCASE_TITLE LIKE '%CITS-Audits%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAUDIT%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAudit%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAUDITS%'
OR AL21.SUBCASE_TITLE LIKE '%CITSAudits%'
)
)
The extra parenthesis make this more confusing trying to figure out the differences. I would much prefer the one I posted originally. It is the same thing but a ton easier on the eyes. 😛
_______________________________________________________________
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/