Help required in SQL Statement

• Ibrahim Zuberi

  SSC Enthusiast

  Points: 137

  More actions

  May 23, 2007 at 1:27 am

  #196038

  I need a SQL Statement to return data in required format ...

  Sample Data

  emp_no	emp_name	emp_status	emp_time
  0001	John	Present	09:00 AM
  0002	Smith	Leave	-
  0003	Clark	Late	11:15 AM
  0001	John	Present	09:00 AM
  0002	Smith	Late	11:55 AM
  0003	Clark	Leave	-

  Required Results

  emp_no	emp_name	emp_status	emp_time	emp_status	emp_time
  0001	John	Present	09:00 AM	Present	09:00 AM
  0002	Smith	Leave	-	Late	11:55 AM
  0003	Clark	Late	11:15 AM	Leave	-

  Thanks in advance and it will provide me great help.

• Koji Matsumura

  SSCrazy

  Points: 2241

  More actions

  May 23, 2007 at 1:33 am

  #708257

  How to decide which record becomes the first set of (emp_status, emp_time) and which bocomes the 2nd?

  Which field(s) makes the unique key of sample data?

• Ibrahim Zuberi

  SSC Enthusiast

  Points: 137

  More actions

  May 23, 2007 at 1:43 am

  #708258

  Sorry there was one additional field that i missed ... i.e. emp_date

  here we go again

  Sample Data

  emp_no	emp_name	emp_status	emp_time	emp_date
  0001	John	Present	09:00 AM	21-05-07
  0002	Smith	Leave	-	21-05-07
  0003	Clark	Late	11:15 AM	21-05-07
  0001	John	Present	09:00 AM	22-05-07
  0002	Smith	Late	11:55 AM	22-05-07
  0003	Clark	Leave	-	22-05-07

   

  Required Results

  emp_no	emp_name	emp_status	emp_time	emp_date	emp_status	emp_time	emp_date
  0001	John	Present	09:00 AM	21-05-07	Present	09:00 AM	22-05-07
  0002	Smith	Leave	-	21-05-07	Late	11:55 AM	22-05-07
  0003	Clark	Late	11:15 AM	21-05-07	Leave	-	22-05-07

• Koji Matsumura

  SSCrazy

  Points: 2241

  More actions

  May 23, 2007 at 1:52 am

  #708259

  Can it be assumed that [emp_no + emp_date] will make all records unique?

• Ibrahim Zuberi

  SSC Enthusiast

  Points: 137

  More actions

  May 23, 2007 at 1:59 am

  #708260

  Basiclly as shown in sample data that there are fields like emp_no, emp_name  I actually wants emp_no, emp_name, emp_date fields grouped togather and emp_status, emp_time should repeate until emp_date is not over.

  It is not necessary that emp_date column should be displayed.

  Please see the blow grid it may clear some more concept.

  		21-05-07		22-05-07
  emp_no	emp_name	emp_status	emp_time	emp_status	emp_time
  0001	John	Present	09:00 AM	Present	09:00 AM
  0002	Smith	Leave	-	Late	11:55 AM
  0003	Clark	Late	11:15 AM	Leave	-

   

  I know that results can't display as above but it is just to give you some more understanding.

  Thank in adv.