sql - Need to create a table with values filled in before dates -

using redshift. have table following fields:

column:         type: department    | varchar              employee_id   | varchar            event         | varchar      date          | date  

and date table has 1 field , lists dates. there few departments employees within each department. "event" field has 2 possible values: join or leave. join record date joined company/department , leave record date left company/department. looks this:

department  employee_id event   date marketing   001         join    6/17/2017 marketing   002         join    6/19/2017 marketing   002         leave   6/20/2017 marketing   001         leave   6/22/2017 

i want make table has every day listed (again have table dates well) , value 1 when employed , value of 0 when not employees. this:

date        department  employee_id employed 6/17/2017   marketing   001         1 6/18/2017   marketing   001         1 6/19/2017   marketing   001         1 6/19/2017   marketing   002         1 6/20/2017   marketing   001         1 6/20/2017   marketing   002         1 6/21/2017   marketing   001         1 6/22/2017   marketing   001         1 

thinking may need create new table has start date , leave date in same row? use case statement , partition first , last date not sure go there... maybe tally table? query might still wouldnt desired result:

select department, employee_id, case when row_number() on (partition employee_id order date asc) = 1 date else null end join_date, case when row_number() on (partition employee_id order date desc) = 1 date else null end leave_date table1 

you can use following sql expand dates using , dates

declare @dateranges table (employee_id varchar(4),                            department  varchar(20),                            join_date   date,                            leave_date  date)  insert @dateranges select employee_id,                           department,                           min(event_date) join_date,                           max(event_date) leave_date                      employment                     group employee_id,                              department;  cte (dt, dept, id, emd) (    select tbl.join_date dt,           tbl.department dept,           tbl.employee_id employee_id,           1 emd      @dateranges tbl     union    select dateadd(day, 1, cte.dt) dt,           tbl.department dept,           tbl.employee_id employee_id,           1 emp      cte     inner join @dateranges tbl        on cte.id = tbl.employee_id       , cte.dept = tbl.department     cte.dt < tbl.leave_date )  select dt date,        dept department,        id employee_id,        emd employed   cte  order dt, id 

result:

date    department  employee_id employed 2017-06-17  marketing   001 1 2017-06-18  marketing   001 1 2017-06-19  marketing   001 1 2017-06-19  marketing   002 1 2017-06-20  marketing   001 1 2017-06-20  marketing   002 1 2017-06-21  marketing   001 1 2017-06-22  marketing   001 1