1 Replies - 170 Views - Last Post: 17 July 2017 - 08:11 PM

#1 fearless2411  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 29-February 16

select statement based on multiple criteria

Posted 17 July 2017 - 07:58 PM

Hi,

I have 2 tables with the following attributes:

TableA:
- employeeid
- employeename

TableB:
- employeeid
- survey_datetime

My objective is to come up with an sql statement to retrieve all employees from TableA and then check if the employee has done at least 1 survey between a period. If at least 1 survey is done, then the column will show "Done" else will show "Not Done".
Note: An employee can do survey at both "Done" period and "Not Done" period.

The following is the sql I have tried but I could not get the result, can someone kindly help? Thanks.

select ta.employeeid, ta.employeename,
case when tb.survey_datetime between '2016-01-01' and '2016-12-31' then 'Done' else 'Not Done' end as 'status' 
from tableA ta
left join tableB tb
    on ta.employeeid=tb.employeeid
group by ta.employeeid,
case when tb.survey_datetime between '2016-01-01' and '2016-12-31' then 'Done' else 'Not Done' end



Is This A Good Question/Topic? 0
  • +

Replies To: select statement based on multiple criteria

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13395
  • View blog
  • Posts: 53,465
  • Joined: 12-June 08

Re: select statement based on multiple criteria

Posted 17 July 2017 - 08:11 PM

You could go about this a few ways... the more explicit would be to join against a subquery that's doing a count.

off the top of my head:
SELECT *
, -- isnull here or a case statement evalutating b.NUmSurveysDone
from mytable a
left join ( select id, count(*) as NUmSurveysDone
       from my table 
       group by ID
) b  
on a.id = b.id

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1