conbine outout

• coool_sweet

  SSCommitted

  Points: 1892

  More actions

  July 26, 2017 at 3:29 pm

  #322904

  hi

  this is my output :

  ID ,             DATE ,                      reason ,      final,        final_date

  1     01/13/2013 09:05:32         NULL         approve           01/02/2015 12:40:00

  1      01/13/2013 09:05:33         hold         not available           01/02/2015 12:40:01

  1      01/13/2013 09:05:34         hold1         not available           01/02/2015 12:40:02

  2       01/13/2013 09:05:35         NULL        locked                   01/02/2015 12:40:03

  what i need is just one line out of it.

  so i need to check first if final -''not available' and reason is not null then get min(date) and that reason should be there.

  so in this case

  ID ,             DATE ,                      reason

  1      01/13/2013 09:05:33         hold  

  not for next field i need to see all values from final column (except not available) and then see final_date, whiever is min that corresponding column should be in final field output.

  final              final_date

  approve           01/02/2015 12:40:00

  so my final output of this table 

  ID ,             DATE ,                      reason      final                   final_date

  1      01/13/2013 09:05:33         hold           approve                01/02/2015 12:40:00

  same for id 2,3 ,4....................

  plz help me

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  July 26, 2017 at 4:41 pm

  #1952924

  So do a row number with a partition by ID and a sort by date with a criteria of "not available" in a CTE and then select from the CTE where the row number = 1.

  If you want a coded example, you've been around long enough to know that we prefer coded data.  See the first link in my signature line below under "Helpful Links", which you've been told about before.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)