Sql server joins a single table

Question

These are my lines in the table:

  Id |  FromDate |  Todate |  prod_Id
 ------ | --------------------------- | --------------- ----------- | -------
 1 |  2012-08-13 07: 00: 00.000 |  2012-08-14 18: 59: 00.000 |  ten
 1 |  2012-08-13 07: 00: 00.000 |  2012-08-15 18: 59: 00.000 |  ten
 1 |  2012-08-13 07: 00: 00.000 |  2012-08-16 18: 59: 00.000 |  ten 

I need the result as below:

  Id |  FromDate |  Todate |  prod_Id
 ---- | ----------------------------- | --------------- ------------ | ---------
 1 |  2012-08-13 07: 00: 00.000 |  2012-08-16 18: 59: 00.000 |  ten 

How to do it?

Answer 1

SELECT Id, MIN(FromDate), MAX(ToDate), prod_Id FROM TbName GROUP BY Id, prod_Id 

Answer 2

Please try this sql. I think this is what you want.

 SELECT Id, FromDate, MAX(ToDate), prod_Id FROM TbName GROUP BY Id, FromDate, prod_Id 

Answer 3

You can go as follows

 SELECT p.dept,p.id, u.FirstName +'+ ' '+ '+ u.LastName as CreatedBy, v.FirstName +'+ ' '+ '+ v.LastName as ModifiedBy from tblUserNew p inner join tblUserNew u on p.CreatedBy=u.Id inner join tblUserNew as v on p.ModifiedBy=v.Id