CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Uday
Uday

Reputation: 161

sql server join same table

This is my Rows in a table :

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

I would like a result like below:

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

How to do it?

Upvotes: 4

Views: 2535

Answers (3)

AKZap
AKZap

Reputation: 1211

Please, try this sql. i think that is what you want.

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

Upvotes: 2

Ram Singh
Ram Singh

Reputation: 6918

You can go in the following way

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

Upvotes: 1

Patrick Guimalan
Patrick Guimalan

Reputation: 1010

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

Upvotes: 5

Related Questions