/* ucb_assignment_case.sas */
/* Construct an assignment-level table which incorporates:
 * assignment dates, assignment county and office number.
 * Restrict to assignments of primary responsibility to cases.
 * Programmer: M. Armijo
 * Revision History:
 * 2008.02.12 Rev 1.00
 */  
proc sql BUFFERSIZE=256000 ;
create table DWH.UCB_ASSIGNMENT_CASE as
select
   A.IDENTIFIER
  ,A.CNTY_SPFCD
  ,A.START_DT
  ,A.END_DT
  ,A.ESTBLSH_ID as FKCASE_T
/*,A.FKCASE_LDT */
/*,B.FKASG_UNIT */
/*,B.START_DT as CL_START_DT */
/*,B.END_DT as CL_END_DT */
/*,C.FKCWS_OFFT */
/*,C.START_DT as AU_START_DT */
/*,C.END_DT as AU_END_DT */
  ,D.CWS_OFF_NO

from CWS.ASGNM_T A, CWS.CASE_LDT B, CWS.ASG_UNIT C, CWS.CWS_OFFT D
where
A.FKCASE_LDT=B.IDENTIFIER
and
B.FKASG_UNIT=C.IDENTIFIER
and
C.FKCWS_OFFT=D.IDENTIFIER
and
A.ESTBLSH_CD='C'
and
A.ASGNMNT_CD='P'
;