-- PCT Details SELECT P.CASEREFERENCE AS PCTCase, C.CASEREFERENCE AS NationalCase, PCT.EVENTNUMBER AS "PCT filing number", PCT.EVENTDATE AS "PCT filing date", FLG.EVENTNUMBER AS "Filing Number", FLG.EVENTDATE AS "Filing Date", DEPL.EVENTNUMBER AS "Local filing Number", DEPL.EVENTDATE AS "Local filing Date", DES.EVENTNUMBER AS "Designated Number", DES.EVENTDATE AS "Designated Date" FROM CASES P LEFT JOIN CEIRELATION CR ON CR.PARENTKEY = P.CASEKEY AND CR.RELATIONTYPE = 105 LEFT JOIN CASEEVENT PCT ON P.CASEKEY = PCT.CASEKEY AND PCT.EVENTKEY = 10 LEFT JOIN CASES C ON CR.CASEKEY = C.CASEKEY LEFT JOIN CASEEVENT FLG ON FLG.CASEKEY = C.CASEKEY AND FLG.EVENTKEY = 10 LEFT JOIN CASEEVENT DEPL ON DEPL.CASEKEY = C.CASEKEY AND DEPL.EVENTKEY = 110 LEFT JOIN CASEEVENT DES ON DES.CASEKEY = C.CASEKEY AND DES.EVENTKEY = 103 WHERE P.CASEREFERENCE LIKE '%WO%' ORDER BY 1,2
SELECT TC.TABCONTROLNO, C.CRITERIANO, C.CASEOFFICEID, O.DESCRIPTION AS OFFICE, CT.CASETYPE, CT.CASETYPEDESC, PT.PROPERTYTYPE, PT.PROPERTYNAME, CTY1.COUNTRYCODE, CTY1.COUNTRY, CTY2.COUNTRY AS VALIDCOUNTRY, CC.CASECATEGORY, CC.CASECATEGORYDESC, ISNULL(VC1.CASECATEGORYDESC, VC2.CASECATEGORYDESC) AS VALIDCASECATEGORYDESC, C.PROGRAMID, C.PROPERTYTYPE, C.PROPERTYUNKNOWN, C.COUNTRYCODE, C.COUNTRYUNKNOWN, C.CASECATEGORY, C.CATEGORYUNKNOWN, WC.WINDOWCONTROLNO, WC.WINDOWNAME, WC.ISEXTERNAL, TC.TABCONTROLNO, TC.TABNAME, TC.DISPLAYSEQUENCE, TC.TABTITLE FROM CRITERIA C JOIN WINDOWCONTROL WC ON C.CRITERIANO = WC.CRITERIANO LEFT JOIN TABCONTROL TC ON WC.WINDOWCONTROLNO = TC.WINDOWCONTROLNO LEFT JOIN OFFICE O ON C.CASEOFFICEID = O.OFFICEID LEFT JOIN CASETYPE CT ON C.CASETYPE = CT.CASETYPE LEFT JOIN PROPERTYTYPE PT ON C.PROPERTYTYPE = PT.PROPERTYTYPE LEFT JOIN CASECATEGORY CC ON CC.CASETYPE = C.CASETYPE AND CC.CASECATEGORY = C.CASECATEGORY LEFT JOIN COUNTRY CTY1 ON C.COUNTRYCODE = CTY1.COUNTRYCODE LEFT JOIN VALIDCATEGORY VC1 ON VC1.CASETYPE = C.CASETYPE AND VC1.PROPERTYTYPE = C.PROPERTYTYPE AND VC1.CASECATEGORY = C.CASECATEGORY AND VC1.COUNTRYCODE = C.COUNTRYCODE LEFT JOIN VALIDCATEGORY VC2 ON VC2.CASETYPE = C.CASETYPE AND VC2.PROPERTYTYPE = C.PROPERTYTYPE AND VC2.CASECATEGORY = C.CASECATEGORY AND VC2.COUNTRYCODE = 'ZZZ' LEFT JOIN COUNTRY CTY2 ON CTY2.COUNTRYCODE = ISNULL(VC1.COUNTRYCODE, VC2.COUNTRYCODE) WHERE TABTITLE LIKE '%Case Billing info%' ORDER BY C.CRITERIANO, TC.DISPLAYSEQUENCE