Dynamic Joins

Question

Dynamic Joins

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

lanky_doodle (5/8/2012)
Any thoughts guys?

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Not everyone here have a desire to setup everything from scratch. If you could follow tips from the above link, you will get more relevant help in less time...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

lanky_doodle SSCrazy Points: 2378 More actions · Answer 2

Thanks for the input guys. I have managed to sort this out;

USEInpro

DECLARE@FROMDATEAS datetime

DECLARE@TODATEAS datetime

SET@FROMDATE= { TS '2012-01-01 00:00:00.000' }

SET@TODATE= { TS '2012-03-31 23:59:59.997' }

SELECTCOALESCE(C.CASECOUNT, 0) CASECOUNT, N1.FIRSTNAME + ' ' + N1.NAME STAFFNAME

FROMNAME N1

JOIN CASENAME CN1 ON N1.NAMENO = CN1.NAMENO

LEFT JOIN (

SELECTSUM(COALESCE(E.CASECOUNT, 0) + COALESCE(S.CASECOUNT, 0)) CASECOUNT, COALESCE(S.NAMENO, E.NAMENO) STAFFNAMENO

FROM(

SELECTCOUNT(DISTINCT CA2.CASEID) CASECOUNT, N2.NAMENO

FROMCASES CA2

JOIN CASETYPE CT2 ON CA2.CASETYPE = CT2.CASETYPE

JOIN CASEEVENT CE2 ON CA2.CASEID = CE2.CASEID

JOIN CASENAME CN2 ON CA2.CASEID = CN2.CASEID

JOIN NAME N2 ON CN2.NAMENO = N2.NAMENO

WHERECA2.CASECATEGORY <> 'X' AND CE2.EVENTNO = -16

AND CE2.EVENTDATE BETWEEN @FROMDATE AND @TODATE

AND CN2.NAMETYPE = 'EMP' AND N2.NAMENO <> -93

GROUP BYN2.NAMENO

) E LEFT JOIN (

SELECTCOUNT(DISTINCT CA3.CASEID) CASECOUNT, N4.NAMENO

FROMCASES CA3

JOIN CASETYPE CT ON CA3.CASETYPE = CT.CASETYPE

JOIN CASEEVENT CE3 ON CA3.CASEID = CE3.CASEID

JOIN CASENAME CN3 ON CA3.CASEID = CN3.CASEID

JOIN NAME N3 ON CN3.NAMENO = N3.NAMENO

JOIN CASENAME CN4 ON CA3.CASEID = CN4.CASEID

JOIN NAME N4 ON CN4.NAMENO = N4.NAMENO

WHERECA3.CASECATEGORY <> 'X' AND CE3.EVENTNO = -16

AND CE3.EVENTDATE BETWEEN @FROMDATE AND @TODATE AND CN3.NAMETYPE = 'EMP'

AND CN4.NAMETYPE = 'SIG' AND N4.NAMENO <> N3.NAMENO AND N3.NAMENO = -93

GROUP BYN4.NAMENO

) S ON E.NAMENO = S.NAMENO

GROUP BYS.NAMENO, E.NAMENO

) C ON C.STAFFNAMENO = N1.NAMENO

WHEREN1.NAMENO NOT IN (-93, -5167, -5450) AND CN1.NAMETYPE = 'EMP'

GROUP BYC.CASECOUNT, N1.FIRSTNAME, N1.NAME

ORDER BYN1.NAME

!!!!

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

lanky_doodle (5/14/2012)
Thanks for the input guys. I have managed to sort this out;

How's the performance on all of that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lanky_doodle SSCrazy Points: 2378 More actions · Answer 4

I haven't delved in too deeply, but it consistently completes in less than 1 second.

I know it's not the best method but it's working for now (although I am working on cleaning it up!).