Help with a GROUP BY Query

[ShankarJ]

Hi!

I have data in the following format :

Table
-----
ID int
Rate decimal(9,2)
EffDate datetime

Data
----
1,200,2005-06-16
1,200,2005-06-17
1,300,2005-06-18
1,300,2005-06-20
1,200,2005-06-21
1,200,2005-06-22

I want to query which shows the Rate along with the validity dates i.e. MIN() & MAX() by Rate but taking into account Rate changes:

1, 200, 2005-06-16, 2005-06-17
1, 300, 2005-06-18, 2005-06-18
1, 300, 2005-06-20, 2005-06-20
1, 200, 2005-06-21, 2005-06-22

Please Note:
The Rate has changed on 2005-06-18 and again on 2005-06-21
There is no rate for 2005-06-19


My query of

SELECT ID, Rate, MIN(EffDate), MAX(EffDate), COUNT(*) AS Days GROUP BY ID, Rate

returns the following:

1, 200, 2005-06-16, 2005-06-22, 4
1, 300, 2005-06-18, 2005-06-20, 2

Any suggestion on how to do the query to make the GROUP BY break on a Rate Change or a missing EffDate. Or, is the only option a Stored procedure using a Temp Table? If so, how?

Thanks & Regards

 

[amrita418]

Shouldn't the output be

1, 200, 2005-06-16, 2005-06-17
1, 300, 2005-06-18, 2005-06-20
1, 200, 2005-06-21, 2005-06-22

Regards,
AA

 

[ShankarJ]

AA,

You are right. It's a TYPO.

 

[ShankarJ]

AA,

No, it is not a TYPO. You forgot the missing date of 2005-06-19.

 

[amrita418]

I do not see a way to do get the results you want with sql.
The code below creates a temp table which you can query to get the results you are looking for.

Run this block of code first:

declare

@v_id int,
@v_rate decimal(9, 2),
@v_effdate datetime,
@v_old_id int,
@v_old_rate decimal(9,2),
@v_count int,
@v_old_effdate datetime

set @v_id = NULL
set @v_rate = NULL
set @v_effdate = NULL
set @v_old_id = 0
set @v_old_rate = 0
set @v_count = 0
set @v_old_effdate = '1900-01-01'

declare c1 cursor for select * from #table1

open c1

fetch next from c1 into @v_id, @v_rate, @v_effdate

create table #temp (ID       int,
Rate     decimal(9,2),
EffDate  datetime, 
Counter int)

while @@fetch_status = 0
begin

if (@v_id = @v_old_id and @v_rate = @v_old_rate and datediff(day, @v_old_effdate, @v_effdate) <= 1)
begin
	insert into #temp values (@v_id, @v_rate, @v_effdate, @v_count)
end
else
begin
	set 	@v_count = @v_count + 1
	insert into #temp values (@v_id, @v_rate, @v_effdate, @v_count)
end

set 	@v_old_id = @v_id
set 	@v_old_rate = @v_rate
set 	@v_old_effdate = @v_effdate
set 	@v_id = NULL
set 	@v_rate = NULL

fetch next from c1 into @v_id, @v_rate, @v_effdate
end
close c1
deallocate c1

Then run this query:

select id, rate, min(effdate), max(effdate)
from #temp 
group by id, rate, counter
order by counter

Regards,
AA

 

[maswien]

Assume following is your table:

create table interest( iid int, rate int, effdate smalldatetime)

This table really need a unique key for you to work on it, so adding a key won't hurt anything, copy your table to another one with a unique key:

select identity(int,1,1) as rowid, interest.* into interest1 from interest order by effdate

Then you can run following query to get what you want:

select t0.iid, t0.rate, t0.effdate, t1.effdate
from interest1 t0 inner join interest1 t1 on t0.rowid + 1 = t1.rowid and t1.rowid % 2 = 0