D.CAMP 리뉴얼

h1. View 테이블

<pre>

CREATE VIEW project_summary_view AS

select t1.*, t2.worker_cnt, t2.insp_cnt, t2.uploader_cnt from (

    select p.project_id,

       p.project_nm,

       p.data_type,

       count(*) as tot_cnt,

       SUM(case when data_status_ccd = 'ING' and data_dtl_status_ccd = 'ASSIGN' then 1 else 0 end)  as assign_done_cnt,

       SUM(case when data_status_ccd = 'WAIT' and data_dtl_status_ccd = 'WAIT' then 1 else 0 end)  as assign_wait_cnt ,

       SUM(case when data_status_ccd = 'ISSUE' then 1 else 0 end)  as issue_cnt ,

       SUM(case when data_status_ccd = 'DONE' and data_dtl_status_ccd = 'DONE' then 1 else 0 end)  as done_cnt

from project p

inner join project_data pd on p.project_id = pd.project_id

group by p.project_id

order by p.project_id

              ) t1

left join (

    select project_id,

           sum(case when user_role = 'WORKER' then 1 else 0 end) as worker_cnt,

           sum(case when user_role = 'INSPECTOR' then 1 else 0 end) as insp_cnt,

           sum(case when user_role = 'UPLOADER' then 1 else 0 end) as uploader_cnt

    from project_user_assigned pua

    join users u on u.user_seq = pua.user_seq

    group by project_id

    order by project_id

) t2 on t1.project_id = t2.project_id ;

</pre>