self Join issue

• naik.jo

  SSC-Addicted

  Points: 408

  More actions

  November 6, 2008 at 7:41 am

  #96765

  Hi,

  I have following result set,

  empid activitytype noOfHrs

  162599 Billable 6

  162599 NonBillable 1

  184854 Billable 41.5

  184854 NonBillable 37.5

  188557 Billable 1

  I want to generate report in this format-:

  empid billable nonbillable total

  162599 6 1 7

  184854 41.5 37.5 79

  188557 1 0 1

  For this I am writing following query-:

  select a.empid,a.[No of hrs] 'billable',b.[No of hrs] 'nonbillable' from

  testTiemsheet a, testTiemsheet b

  where a.empid=b.empid and a.activitytype='Billable' and b.activitytype='NonBillable'

  But result I am getting is-:

  empid billable nonbillable total

  162599 6 1 7

  184854 41.5 37.5 79

  It is missing 188557 as it is not having activitytype as ‘nonbillable’ [ref. first table, only ‘billable’ is there].

  Could you please help me out in this?

• Ken McKelvey

  SSCoach

  Points: 18844

  More actions

  November 6, 2008 at 8:15 am

  #894917

  SELECT empid

  &nbsp&nbsp&nbsp&nbsp,ISNULL(SUM(CASE WHEN activitytype = 'Billable' THEN noOfHrs END), 0) AS Billable

  &nbsp&nbsp&nbsp&nbsp,ISNULL(SUM(CASE WHEN activitytype = 'NonBillable' THEN noOfHrs END), 0) AS NonBillable

  &nbsp&nbsp&nbsp&nbsp,SUM(noOfHrs) AS Total

  FROM testTiemsheet

  GROUP BY empid

• naik.jo

  SSC-Addicted

  Points: 408

  More actions

  November 6, 2008 at 10:17 pm

  #895311

  Thanks a lot. That helped. 🙂