Timecard report that shows hours worked every day, even when its zero

############################################

select  
    d.shop, 
    d.shop_person as "Employee #",
    wkad.proposal as "WO#",  
    wkad.act_hrs,
    wkad.sort_code as "Phase#",
    wkad.trans_no,
    date_format(wkae.sched_date, '%Y-%m-%d') as work_date
    
from ae_l_shp_d d

left join ae_p_wka_e wkae #time card
on d.shop_person = wkae.shop_person

left join ae_p_wka_d wkad #time card line item
on wkae.trans_no = wkad.trans_no

join aim_dim_date dd
on 1=1

where d.shop like 'FAC-TRD-ELECTRIC%'
and ((d.date_to >= '2020-07-01' and d.date_to <= '2020-07-31') or d.date_to is null)
and (dd.date_value >= '2020-07-01' and dd.date_value <= '2020-07-31')     
and (date_format(wkae.sched_date, '%Y-%m-%d') >= '2020-07-01' and  date_format(wkae.sched_date, '%Y-%m-%d') <= '2020-07-31') 

order by d.shop_person, dd.date_value, date_format(wkae.sched_date, '%Y-%m-%d') 

The above query produces this: query results

What I want is for the work_date column to show every date in the month. If someone doesn’t work then WO#, act_hours, phase#, and trans_no would all be null but the work_date field, shop, and employee # would still be filled in.

I have a view (aim_dim_date) that has every day in the calendar in it. I’m just trying to figure out how to utilize it to show when people work and when they haven’t worked.

Please help!

Thanks!

Answer

You should remove the where condition for wkae table, since it filters out the null records, and place the condition on the date in the left join clause instead.

You can try something like this. First cross join ae_l_shp_d with your calendar view and then left join wkae on date column also.

select  
    d.shop, 
    d.shop_person as "Employee #",
    wkad.proposal as "WO#",  
    wkad.act_hrs,
    wkad.sort_code as "Phase#",
    wkad.trans_no,
    dd.date_value as work_date
    
from ae_l_shp_d d

cross join aim_dim_date dd

left join ae_p_wka_e wkae #time card
on d.shop_person = wkae.shop_person and wkae.sched_date = dd.date_value

left join ae_p_wka_d wkad #time card line item
on wkae.trans_no = wkad.trans_no

where d.shop like 'FAC-TRD-ELECTRIC%'
and ((d.date_to >= '2020-07-01' and d.date_to <= '2020-07-31') or d.date_to is null)
and (dd.date_value >= '2020-07-01' and dd.date_value <= '2020-07-31')     

order by d.shop_person, dd.date_value