CREATE Procedure Report_getCasesByJudgeFullBench
@idCOURT integer = Null,
@idJUDGE integer = Null,
@idComposition integer,
@citation_date1 datetime = 'January 1, 1753',
@citation_date2 datetime = 'December 31, 9999'
AS
SELECT
DISTINCT
*,
st.description AS legal_status,
o.description AS outcome_description,
cmp.description As court_composition

FROM         CASE_ENTITY ce 
INNER JOIN   COURT co ON co.idCOURT = ce.idCOURT 
INNER JOIN   FOR_VOTE ON ce.idCASE_ENTITY = FOR_VOTE.idCASE_ENTITY 
INNER JOIN   JUDGE jdg ON FOR_VOTE.idJudge = jdg.idJUDGE  
INNER JOIN   COMPOSITION cmp ON ce.idCOMPOSITION = cmp.idCOMPOSITION
INNER JOIN   LEGAL_STATUS st ON ce.idLEGAL_STATUS = st.idLEGAL_STATUS
WHERE    
(ce.idCOURT = @idCOURT)
AND(jdg.idJUDGE =@idJUDGE )
AND citation_date BETWEEN @citation_date1 AND @citation_date2 Order By citation_date desc

UNION ALL
SELECT
DISTINCT
*,
st.description AS legal_status,
o.description AS outcome_description,
cmp.description As court_composition
FROM         CASE_ENTITY ce 
INNER JOIN   COURT co ON co.idCOURT = ce.idCOURT 
INNER JOIN   AGAINST_VOTE ON ce.idCASE_ENTITY = AGAINST_VOTE.idCASE_ENTITY 
INNER JOIN   JUDGE jdg ON AGAINST_VOTE.idJUDGE = jdg.idJUDGE 
INNER JOIN   COMPOSITION cmp ON ce.idCOMPOSITION = cmp.idCOMPOSITION
INNER JOIN   LEGAL_STATUS st ON ce.idLEGAL_STATUS = st.idLEGAL_STATUS
WHERE    
(ce.idCOURT = @idCOURT)
AND(jdg.idJUDGE =@idJUDGE )
AND citation_date BETWEEN @citation_date1 AND @citation_date2 Order By citation_date desc

CREATE Procedure Report_getCasesByJudgeFullBench
(
	@idCOURT integer = Null,
	@idJUDGE integer = Null,
	@idComposition integer,
	@citation_date1 datetime = 'January 1, 1753',
	@citation_date2 datetime = 'December 31, 9999'
)
AS

SET NOCOUNT ON

SELECT		<all columns explicit specified here>,
		st.description AS legal_status,
		o.description AS outcome_description,
		cmp.description As court_composition,
		0 AS ForAgainst
FROM		CASE_ENTITY ce 
INNER JOIN	COURT co ON co.idCOURT = ce.idCOURT 
INNER JOIN	FOR_VOTE ON ce.idCASE_ENTITY = FOR_VOTE.idCASE_ENTITY 
INNER JOIN	JUDGE jdg ON FOR_VOTE.idJudge = jdg.idJUDGE AND jdg.idJUDGE = @idJUDGE
INNER JOIN	COMPOSITION cmp ON ce.idCOMPOSITION = cmp.idCOMPOSITION
INNER JOIN	LEGAL_STATUS st ON ce.idLEGAL_STATUS = st.idLEGAL_STATUS
WHERE		ce.idCOURT = @idCOURT
		AND ce.citation_date BETWEEN @citation_date1 AND @citation_date2
UNION
SELECT		<all columns explicit specified here>,
		st.description AS legal_status,
		o.description AS outcome_description,
		cmp.description As court_composition,
		1 AS ForAgainst
FROM		CASE_ENTITY ce 
INNER JOIN	COURT co ON co.idCOURT = ce.idCOURT 
INNER JOIN	AGAINST_VOTE ON ce.idCASE_ENTITY = AGAINST_VOTE.idCASE_ENTITY 
INNER JOIN	JUDGE jdg ON AGAINST_VOTE.idJUDGE = jdg.idJUDGE AND jdg.idJUDGE = @idJUDGE
INNER JOIN	COMPOSITION cmp ON ce.idCOMPOSITION = cmp.idCOMPOSITION
INNER JOIN	LEGAL_STATUS st ON ce.idLEGAL_STATUS = st.idLEGAL_STATUS
WHERE		ce.idCOURT = @idCOURT
		AND citation_date BETWEEN @citation_date1 AND @citation_date2
ORDER BY	ForAgainst,
		ce.citation_date desc