Help with sql query...please

[berny978]

Hi to all good people out there!

I need a little help with one sql query. Suppose we have one table with data like this:
Date ID
-----------------
4-1-2013 10
4-2-2013 10
4-3-2013 10
4-4-2013 10
4-5-2013 20
4-6-2013 20
4-7-2013 10
4-8-2013 10
4-9-2013 10
4-10-2013 30
-----------------

Now, I would like to get min and max dates for each ID, but like this:
ID min max
------------------------------
10 4-1-2013 4-4-2013
20 4-5-2013 4-6-2013
10 4-7-2013 4-9-2013
30 4-10-2013 4-10-2013
-------------------------------

Is it possible somehow? Thanks in advance!
B.

 

[IanWaterman]

You have grouped by ID = 10 twice. How are they differentiated.

Ian

 

[DaveInIowa]

How about?

SET NOCOUNT ON

DECLARE @Id INT, @DDate DATE
DECLARE @MinDate DATE, @MaxDate DATE, @RowsInRange INT
DECLARE @IsNewId BIT = 1, @IdPrevious INT
DECLARE @CustomGroupedRows TABLE ( Id INT, MinDate DATE, MaxDate DATE, RowsInRange INT )

DECLARE csr CURSOR FAST_FORWARD FOR 
SELECT Id, DDate
  FROM YourTable
 ORDER BY DDate

OPEN csr 
FETCH NEXT FROM csr INTO @Id, @DDate
WHILE @@FETCH_STATUS = 0 BEGIN

	IF (@IsNewId = 1) BEGIN
		SET @RowsInRange = 0
		SET @MinDate = @DDate
		SET @IdPrevious = @Id
	END
	
	SET @RowsInRange = @RowsInRange + 1
	SET @MaxDate = @DDate
	
	FETCH NEXT FROM csr INTO @Id, @DDate

	SET @IsNewId = 0
	IF (@@FETCH_STATUS <> 0 OR @Id <> @IdPrevious) BEGIN
		SET @IsNewId = 1
		INSERT INTO @CustomGroupedRows (Id, MinDate, MaxDate, RowsInRange) VALUES(@IdPrevious, @MinDate, @MaxDate, @RowsInRange)
	END

END

CLOSE csr 
DEALLOCATE csr

SELECT *
  FROM @CustomGroupedRows

 

[Alan0568]

Look like Dave beat me to it!

create table #temp_results
(id int,
[min] date,
[max] date)
GO

truncate table #temp_results
GO

declare
@id int,
@date smalldatetime,
@curr_id int,
@min_date smalldatetime,
@max_date smalldatetime,
@row_count int

declare a_cursor cursor for select
[date],[id] from yourtable
order by [date]

open a_cursor
fetch next from a_cursor into

@date,
@id

set @curr_id = @id
set @min_date = @date
set @max_date = @date
set @row_count = 0

while @@fetch_status=0

begin
set @row_count = @row_count +1

if  @curr_id <> @id
begin
	insert #temp_results
	select @curr_id,@min_date,@max_date
	set @min_date = @date
end

set @max_date = @date
set @curr_id = @id

if @row_count = @@CURSOR_ROWS
begin
	insert #temp_results
	select @curr_id,@min_date,@max_date
end
fetch next from a_cursor into

@date,
@id

end
close a_cursor
deallocate a_cursor
GO

select * from #temp_results

drop table #temp_results

 

[berny978]

thanks a lot!
chears!