ToolBox - Create Views for SIG and EMP

USE ToolBox

CREATE VIEW SB_SIGEMP_ASIA
AS
SELECT O.DESCRIPTION AS OFFICE,
N1.NAMENO AS SIG_NAMENO, N1.NAMECODE AS SIG_NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME AS SIG_NAME,
N2.NAMENO AS EMP_NAMENO, N2.NAMECODE AS EMP_NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME AS EMP_NAME, COUNT(C.CASEID) AS CASECOUNT
FROM INPROASIA..OFFICE O
LEFT JOIN INPROASIA..CASES C ON C.OFFICEID = O.OFFICEID
LEFT JOIN INPROASIA..CASENAME CN1 ON C.CASEID = CN1.CASEID AND CN1.NAMETYPE = 'SIG'
LEFT JOIN INPROASIA..NAME N1 ON N1.NAMENO = CN1.NAMENO
LEFT JOIN INPROASIA..CASENAME CN2 ON C.CASEID = CN2.CASEID AND CN2.NAMETYPE = 'EMP'
LEFT JOIN INPROASIA..NAME N2 ON N2.NAMENO = CN2.NAMENO
GROUP BY O.DESCRIPTION, N1.NAMENO, N1.NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME, N2.NAMENO, N2.NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME

CREATE VIEW SB_SIGEMP_EU
AS
SELECT O.DESCRIPTION AS OFFICE,
N1.NAMENO AS SIG_NAMENO, N1.NAMECODE AS SIG_NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME AS SIG_NAME,
N2.NAMENO AS EMP_NAMENO, N2.NAMECODE AS EMP_NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME AS EMP_NAME, COUNT(C.CASEID) AS CASECOUNT
FROM INPROEU..OFFICE O
LEFT JOIN INPROEU..CASES C ON C.OFFICEID = O.OFFICEID
LEFT JOIN INPROEU..CASENAME CN1 ON C.CASEID = CN1.CASEID AND CN1.NAMETYPE = 'SIG'
LEFT JOIN INPROEU..NAME N1 ON N1.NAMENO = CN1.NAMENO
LEFT JOIN INPROEU..CASENAME CN2 ON C.CASEID = CN2.CASEID AND CN2.NAMETYPE = 'EMP'
LEFT JOIN INPROEU..NAME N2 ON N2.NAMENO = CN2.NAMENO
GROUP BY O.DESCRIPTION, N1.NAMENO, N1.NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME, N2.NAMENO, N2.NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME

CREATE VIEW SB_SIGEMP_US
AS
SELECT O.DESCRIPTION AS OFFICE,
N1.NAMENO AS SIG_NAMENO, N1.NAMECODE AS SIG_NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME AS SIG_NAME,
N2.NAMENO AS EMP_NAMENO, N2.NAMECODE AS EMP_NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME AS EMP_NAME, COUNT(C.CASEID) AS CASECOUNT
FROM INPROUS..OFFICE O
LEFT JOIN INPROUS..CASES C ON C.OFFICEID = O.OFFICEID
LEFT JOIN INPROUS..CASENAME CN1 ON C.CASEID = CN1.CASEID AND CN1.NAMETYPE = 'SIG'
LEFT JOIN INPROUS..NAME N1 ON N1.NAMENO = CN1.NAMENO
LEFT JOIN INPROUS..CASENAME CN2 ON C.CASEID = CN2.CASEID AND CN2.NAMETYPE = 'EMP'
LEFT JOIN INPROUS..NAME N2 ON N2.NAMENO = CN2.NAMENO
GROUP BY O.DESCRIPTION, N1.NAMENO, N1.NAMECODE, ISNULL(N1.FIRSTNAME+' ','')+ N1.NAME, N2.NAMENO, N2.NAMECODE, ISNULL(N2.FIRSTNAME+' ','')+ N2.NAME