1 Reply Latest reply: Dec 31, 2013 5:08 AM by Marwim RSS

return rows for given employees even if data does not exists in sql developer

1a6536b7-9ea3-4467-8a07-17ccb9507253 Newbie
Currently Being Moderated

My sql query returns proper result for given employees for a particular week

for eg. my employee list is (emp1 , emp2 , emp3 , emp4)

IF emp4 has not done any work for that week , row for that employee is not returned at all .

My query is as follows :

select a.creator_login , sum (case when a.activitytype = 'NCE- Installation' then a.duration else 0 end) as NCE_Installation , sum (case when a.activitytype = 'NCE- Migration' then a.duration else 0 end) as NCE_Migration , sum (case when a.activitytype = 'NCE-Circuit Testing' then a.duration else 0 end) as NCE_Circuit_Testing , sum (case when a.activitytype = 'NCE-Communication - External' then a.duration else 0 end) as NCE_Communication_External, sum (case when a.activitytype = 'NCE-Communication - Internal' then a.duration else 0 end) as NCE_Communication_Internal, sum (case when a.activitytype = 'Exception' then a.duration else 0 end) as Exception, sum (case when a.activitytype = 'NCE-Configuration' then a.duration else 0 end) as NCE_Configuration, sum (case when a.activitytype = 'NCE-Design Reqt Gathering' then a.duration else 0 end) as NCE_Design_Reqt_Gathering, sum (case when a.activitytype = 'NCE-Documentation' then a.duration else 0 end) as NCE_Documentation, sum (case when a.activitytype = 'Notes' then a.duration else 0 end) as Notes, sum (case when b.openingcode = 'GOC Acceptance' then a.duration else 0 end) as  GOC_Acceptance, sum (case when a.activitytype = 'To Do' then a.duration else 0 end) as To_Do from vware.snap_ticketactivities a , vware.snap_troubletickets b where a.ticketrowid  = b.ticketrowid and a.creator_login in ('AMITTAL','HSHARMA','NKHAN','PKSINGH','PPATNANA','PTHAKUR','SDAS','SPATEL','VDASS','VVIGNESHWARAN','AOAK') and a.created between  trunc(sysdate-12)  and trunc(sysdate-6 ) group by a.creator_login


There is no record for user AOAK , so no row is returned for AOAK , but i want to dispaly him with 0 values in all feilds

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points