Count from joins

• lanky_doodle

  SSCrazy

  Points: 2378

  More actions

  February 12, 2018 at 7:30 am

  #343599

  Hi,
  Is there a better way of doing this? I'm joining back to the same table 4 times and counting from each table alias:
  
 select  DEP_Code, DEP_Name, cast(0 as bit) Favourite,
     case
      when DEP_Code = 'ACHR' then 7
      when DEP_Code = 'IT' then 8
      when DEP_Code = 'REC' then 9
     end [Rank],
     count(at.EMP_ClockNumber) Total,
     count(ai.EMP_ClockNumber) [In],
     count(ao.EMP_ClockNumber) [Out],
     count(aa.EMP_ClockNumber) [Absent]
 from  [(local)\ISYS].Intelligent.dbo.Employees e left join
     [(local)\ISYS].Intelligent.dbo.Departments d on d.DEP_Code = e.EMP_DEP_Code left join
     [(local)\ISYS].Intelligent.dbo.vwStaffDayActivity at on at.EMP_ClockNumber = e.EMP_ClockNumber left join
     [(local)\ISYS].Intelligent.dbo.vwStaffDayActivity ai on ai.EMP_ClockNumber = e.EMP_ClockNumber and
      ai.ACT_Status = 'In' left join
     [(local)\ISYS].Intelligent.dbo.vwStaffDayActivity ao on ao.EMP_ClockNumber = e.EMP_ClockNumber and
      ao.ACT_Status = 'Out' left join
     [(local)\ISYS].Intelligent.dbo.vwStaffDayActivity aa on aa.EMP_ClockNumber = e.EMP_ClockNumber and
      aa.ACT_Status = 'Absent'
 where  not exists
    (
     select  1
     from  [(local)\ISYS].Intelligent.dbo.[Sub Departments]
     where  SDP_DEP_Code = DEP_Code
    ) and
    (getdate() >= e.EMP_Join_Date) and
    (
     (e.EMP_Left_Date is null) or
     (
      getdate() <=
      case when e.EMP_Proposal_Date <= e.EMP_Left_Date
       then e.EMP_Proposal_Date
       else e.EMP_Left_Date
      end
     )
    )
 group by d.DEP_Code, d.DEP_Name


  Thanks.

• lanky_doodle

  SSCrazy

  Points: 2378

  More actions

  February 12, 2018 at 7:36 am

  #1979419

  PS: I'm doing 2 union all and the FROM statements are almost the same in each, as is some of the WHERE statement.

• drew.allen

  SSC Guru

  Points: 76998

  More actions

  February 12, 2018 at 7:43 am

  #1979422

  Use a CASE expression.

  select DEP_Code, DEP_Name, cast(0 as bit) Favourite,
  case
  when DEP_Code = 'ACHR' then 7
  when DEP_Code = 'IT' then 8
  when DEP_Code = 'REC' then 9
  end [Rank],
  count(at.EMP_ClockNumber) Total,
  count(CASE WHEN at.ActStatus = 'In' THEN at.EMP_ClockNumber END) [In],
  count(CASE WHEN at.ActStatus = 'Out' THEN at.EMP_ClockNumber END) [Out],
  count(CASE WHEN at.ActStatus = 'Absent' THEN at.EMP_ClockNumber END) [Absent]
from [(local)\ISYS].Intelligent.dbo.Employees e left join
  [(local)\ISYS].Intelligent.dbo.Departments d on d.DEP_Code = e.EMP_DEP_Code left join
  [(local)\ISYS].Intelligent.dbo.vwStaffDayActivity at on at.EMP_ClockNumber = e.EMP_ClockNumber
where not exists
  (
  select 1
  from [(local)\ISYS].Intelligent.dbo.[Sub Departments]
  where SDP_DEP_Code = DEP_Code
  ) and
  (getdate() >= e.EMP_Join_Date) and
  (
  (e.EMP_Left_Date is null) or
  (
  getdate() <=
  case when e.EMP_Proposal_Date <= e.EMP_Left_Date
   then e.EMP_Proposal_Date
   else e.EMP_Left_Date
  end
  )
  )
group by d.DEP_Code, d.DEP_Name

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 12, 2018 at 8:16 am

  #1979428

  This should be an improvement.
  
select DEP_Code, DEP_Name, cast(0 as bit) Favourite,
  case
  when DEP_Code = 'ACHR' then 7
  when DEP_Code = 'IT' then 8
  when DEP_Code = 'REC' then 9
  end [Rank],
  count(at.EMP_ClockNumber) Total,
  count(CASE WHEN ai.ACT_Status = 'In'  THEN at.EMP_ClockNumber END) [In],
  count(CASE WHEN ai.ACT_Status = 'Out'  THEN at.EMP_ClockNumber END) [Out],
  count(CASE WHEN ai.ACT_Status = 'Absent' THEN at.EMP_ClockNumber END) [Absent]
from [(local)\ISYS].Intelligent.dbo.Employees e
LEFT JOIN [(local)\ISYS].Intelligent.dbo.Departments d on d.DEP_Code = e.EMP_DEP_Code
LEFT JOIN [(local)\ISYS].Intelligent.dbo.vwStaffDayActivity at on at.EMP_ClockNumber = e.EMP_ClockNumber
where not exists
  (
  select 1
  from [(local)\ISYS].Intelligent.dbo.[Sub Departments]
  where SDP_DEP_Code = DEP_Code
  ) and
  (getdate() >= e.EMP_Join_Date) and
  (
  (e.EMP_Left_Date is null) or
  (
  getdate() <=
  case when e.EMP_Proposal_Date <= e.EMP_Left_Date
   then e.EMP_Proposal_Date
   else e.EMP_Left_Date
  end
  )
  )
group by d.DEP_Code, d.DEP_Name

  I don't like the idea of using the linked servers like that. I'd prefer to send the whole query to the linked server and get it resolved there.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• lanky_doodle

  SSCrazy

  Points: 2378

  More actions

  February 13, 2018 at 4:45 am

  #1979569

  drew.allen - Monday, February 12, 2018 7:43 AM

  Use a CASE expression.

  select DEP_Code, DEP_Name, cast(0 as bit) Favourite,
  case
  when DEP_Code = 'ACHR' then 7
  when DEP_Code = 'IT' then 8
  when DEP_Code = 'REC' then 9
  end [Rank],
  count(at.EMP_ClockNumber) Total,
  count(CASE WHEN at.ActStatus = 'In' THEN at.EMP_ClockNumber END) [In],
  count(CASE WHEN at.ActStatus = 'Out' THEN at.EMP_ClockNumber END) [Out],
  count(CASE WHEN at.ActStatus = 'Absent' THEN at.EMP_ClockNumber END) [Absent]
from [(local)\ISYS].Intelligent.dbo.Employees e left join
  [(local)\ISYS].Intelligent.dbo.Departments d on d.DEP_Code = e.EMP_DEP_Code left join
  [(local)\ISYS].Intelligent.dbo.vwStaffDayActivity at on at.EMP_ClockNumber = e.EMP_ClockNumber
where not exists
  (
  select 1
  from [(local)\ISYS].Intelligent.dbo.[Sub Departments]
  where SDP_DEP_Code = DEP_Code
  ) and
  (getdate() >= e.EMP_Join_Date) and
  (
  (e.EMP_Left_Date is null) or
  (
  getdate() <=
  case when e.EMP_Proposal_Date <= e.EMP_Left_Date
   then e.EMP_Proposal_Date
   else e.EMP_Left_Date
  end
  )
  )
group by d.DEP_Code, d.DEP_Name

  Drew

  Of course!!! Thanks

• lanky_doodle

  SSCrazy

  Points: 2378

  More actions

  February 13, 2018 at 4:47 am

  #1979570

  Luis Cazares - Monday, February 12, 2018 8:16 AM

  This should be an improvement.
  
select DEP_Code, DEP_Name, cast(0 as bit) Favourite,
  case
  when DEP_Code = 'ACHR' then 7
  when DEP_Code = 'IT' then 8
  when DEP_Code = 'REC' then 9
  end [Rank],
  count(at.EMP_ClockNumber) Total,
  count(CASE WHEN ai.ACT_Status = 'In'  THEN at.EMP_ClockNumber END) [In],
  count(CASE WHEN ai.ACT_Status = 'Out'  THEN at.EMP_ClockNumber END) [Out],
  count(CASE WHEN ai.ACT_Status = 'Absent' THEN at.EMP_ClockNumber END) [Absent]
from [(local)\ISYS].Intelligent.dbo.Employees e
LEFT JOIN [(local)\ISYS].Intelligent.dbo.Departments d on d.DEP_Code = e.EMP_DEP_Code
LEFT JOIN [(local)\ISYS].Intelligent.dbo.vwStaffDayActivity at on at.EMP_ClockNumber = e.EMP_ClockNumber
where not exists
  (
  select 1
  from [(local)\ISYS].Intelligent.dbo.[Sub Departments]
  where SDP_DEP_Code = DEP_Code
  ) and
  (getdate() >= e.EMP_Join_Date) and
  (
  (e.EMP_Left_Date is null) or
  (
  getdate() <=
  case when e.EMP_Proposal_Date <= e.EMP_Left_Date
   then e.EMP_Proposal_Date
   else e.EMP_Left_Date
  end
  )
  )
group by d.DEP_Code, d.DEP_Name

  I don't like the idea of using the linked servers like that. I'd prefer to send the whole query to the linked server and get it resolved there.

  One of the unions needs the local as well as the linked server so the sproc exists there. At some point in the future I want all this data synchronised between the databases so linked servers won't be required.