Dynamic Joins

[ldoodle]

Hiya,

I have this query;

SELECT		COUNT(DISTINCT CA.CASEID) CASECOUNT, N1.FIRSTNAME + ' ' + N1.NAME STAFFNAME, N2.FIRSTNAME + ' ' + N2.NAME SIGNAME
FROM		CASES CA
				JOIN CASETYPE CT ON CA.CASETYPE = CT.CASETYPE
				JOIN CASEEVENT CE ON CA.CASEID = CE.CASEID
				JOIN CASENAME CN1 ON CA.CASEID = CN1.CASEID AND CN1.NAMETYPE = 'EMP'
				JOIN NAME N1 ON CN1.NAMENO = N1.NAMENO
				JOIN CASENAME CN2 ON CA.CASEID = CN2.CASEID AND CN2.NAMETYPE = 'SIG'
				JOIN NAME N2 ON CN2.NAMENO = N2.NAMENO
WHERE		CA.CASECATEGORY NOT IN ('X') AND CE.EVENTNO = -16
				AND CE.EVENTDATE BETWEEN { ts '2011-04-01 00:00:00.000' } AND { ts '2012-03-31 23:59:59.997' }
				AND N1.NAMENO IN (-93, -9963)
GROUP BY	N1.FIRSTNAME, N1.NAME, N2.FIRSTNAME, N2.NAME
ORDER BY	N1.NAME, N1.FIRSTNAME

which results in thsi output;

518	Records Department	Jon Doe
1	Records Department	Records Department
6	Records Department	Joe Bloggs
1	Records Department	Jill Bloggs
57	Joe Bloggs		Joe Bloggs

however the output should be;

518	Records Department	Jon Doe
1	Records Department	Records Department
1	Records Department	Jill Bloggs
63	Joe Bloggs		Joe Bloggs

So in essence, where the staffname (2nd column) is 'Records Department', the join should be made against the signatoryname (3rd column).

I've tried in the join and where statements but it doesn't work;

CASE WHEN N1.NAMENO = -93 THEN 'SIG' ELSE 'EMP' END

Can provide table definitions if needed.

[perfectionsu]

I have added another table, CASES.


SELECT COUNT(CA.CASEDESC), N.FIRSTNAME + ' ' N.NAME STAFFNAME
FROM CASES CA JOIN NAMETYPE NT ON CA.NAMENO = NT.NAMENO JOIN
NAME N ON NT.NAMENO = N.NAMENO

Would show this;

2 Joe Bloggs
1 John Smith
2 Jon Doe