How to set dates in a single row?

[mikeyeli]

Hey guys!, i have to do the following:

Suppose i have the following data:

ID ACTIVITY         DATE
1  gardening        01/01/2011
2  cooking          02/01/2011
1  gardening        03/01/2011

i need to make a stored procedure that gives the the result of something like this.

ID ACTIVITY     INITIAL_DATE  END_DATE
1  gardening    01/01/2011    03/01/2011
2  cooking      02/01/2011    NULL

Any ideas of how i can do this?

Thanks for your help!

[gpl]

You could use correlated sub queries, or derived tables, see below (I dont have sql server so have not been able to check the results)

Select 
  ID,
  ACTIVITY,
  INITIAL_DATE,
  NULLIF(End_Date, Initial_Date) As END_DATE
From
  (
    Select ID, ACTIVITY, MIN(Date) as INITIAL_DATE
    From A_TABLE
    Group By ID, ACTIVITY
  ) MinTab
Inner Join
  (
    Select ID, ACTIVITY, MAX(Date) as END_DATE
    From A_TABLE
    Group By ID, ACTIVITY
  ) MaxTab
On MinTab.ID = MaxTab.ID

[mikeyeli]

Thanks for the reply, but i think i didnt explain myself too well last time, what i need is to convert consecutive dates into a range of dates.

ex:

ID ACTIVITY         DATE
1  gardening        01/01/2011
1  gardening        02/01/2011
2  cooking          03/01/2011
2  cooking          04/01/2011
2  cooking          05/01/2011
1  gardening        06/01/2011
1  gardening        07/01/2011

Convert to:

ID ACTIVITY     INITIAL_DATE  END_DATE
1  gardening    01/01/2011    02/01/2011
1  gardening    02/01/2011    06/01/2011
1  gardening    06/01/2011    07/01/2011
2  cooking      03/01/2011    04/01/2011
2  cooking      04/01/2011    05/01/2011

[nbiswas]

Hope this helps

Declare @t table(ID int,Activity Varchar(50),[Date] DATETIME)
Insert into @t Select 1,'gardening','01/01/2011' union all Select 1,'gardening','02/01/2011' 
union all Select 2,'cooking','03/01/2011' union all Select 2,'cooking','04/01/2011'
union all Select 2,'cooking','05/01/2011' union all Select 1,'gardening','06/01/2011'
union all Select 1,'gardening','07/01/2011' 

--select * from @t 

;with cte as  (   
select *,          
row_number() over(partition by ID order by [Date]) as rn   from @t          
) 
select 
C1.ID,        
C1.Activity,      
C1.[Date] as INITIAL_DATE,        
C2.[Date] as END_DATE from cte as C1   
join cte as C2     
on C1.ID = C2.ID and        
C1.rn + 1 = C2.rn 
order by C1.ID, C1.[Date]

Output

ID	Activity	INITIAL_DATE	END_DATE
1	gardening	2011-01-01 00:00:00.000	2011-02-01 00:00:00.000
1	gardening	2011-02-01 00:00:00.000	2011-06-01 00:00:00.000
1	gardening	2011-06-01 00:00:00.000	2011-07-01 00:00:00.000
2	cooking	2011-03-01 00:00:00.000	2011-04-01 00:00:00.000
2	cooking	2011-04-01 00:00:00.000	2011-05-01 00:00:00.000