Required Query

• msaleem

  SSC Enthusiast

  Points: 159

  More actions

  August 11, 2003 at 12:52 am

  #358105

  I have a table candidate with following field

  1. CandidID

  2. Interviewdate

  3. PostId

  4. status

  stats field contains ( "selected", "Rejected", "eligible")

  I want to write a query which output like following ;

  Interview Date, PostId, Selected, Rejected

  i.e how much selected, rejrected in a particular post id on particular interview date,

  keep in mind selected and rejected are not column in table this is data store in status field as mentioned above.

  thanks for prompt help.

• mccork

  SSC-Insane

  Points: 22288

  More actions

  August 11, 2003 at 1:09 am

  #469338

  Try something along the lines of:

   
  
  select InterviewDate, PostId, 
  
      Selected = ISNULL(SUM(CASE WHEN status = 'Selected' THEN 1 ELSE 0 END),0),
  
      Rejected = ISNULL(SUM(CASE WHEN status = 'Rejected' THEN 1 ELSE 0 END),0)
  
  from candidate 
  
  where InterviewDate = @InterviewDate and PostId = @PostId
  
  group by InterviewDate, PostId

  Cheers,

  - Mark

  
  Cheers,
  - Mark

• brokenrulz

  SSCrazy

  Points: 2261

  More actions

  August 11, 2003 at 1:12 am

  #469339

  Try this

  ======

  select interviewdate,

  POSTID,

  sum(case when status='REJECTED' then 1 else 0 end) as 'REJECTED',

  sum(case when status='SELECTED' then 1 else 0 end) as 'SELECTED'

  from orders1 where interviewdate between <DATE1> and <DATE2>

  group by interviewdate,postid

  Relationships are like Banks.You Invest the Principal and then get the Interest.

  
  He who knows others is learned but the wise one is one who knows himself.

• bsubrama

  SSC Rookie

  Points: 28

  More actions

  August 11, 2003 at 11:54 pm

  #469494

  The following query also works in case you do not want to use 'case'.

  select Interviewdate, PostId,

  AcceptedCount=sum(charindex("Accepted",status)),

  RejectedCount=sum(charindex("Rejected",status))

  from #Candidate

  group by Interviewdate, PostId