Grouping by incremental dates 1

[mercwrought]

Hi all, Got a question. I have a table I am trying to group and the group needs to be groups of incremental dates. I know I have seen threads about this before but now I can not find them and I can not think of how to do this simply.
Let’s get some sample data:

date		amount
5/1/2005	10	
5/2/2005	15
5/3/2005	4
5/5/2005	9
5/6/2005	55
5/10/2005	7
5/12/2005	99

desired output:

group	from		to		amount
1	5/1/2005	5/3/2005	29
2	5/5/2005	5/6/2005	64
3	5/10/2005	5/10/2005	7
4	5/12/2005	5/12/2005	99

I know I can make a temp table and with that do some grouping but I was hoping for a better idea. Thx for any help … also I’m doing this because I have a really slow procedure that loops through it and I must speed it up. Once more thx for any help

 

[JayKusch]

I see no method to your groupings. Group1 spans 3 days, Group2 spans 2 days, Group3 spans 1 day then Group4 spans 1 day.

Each group does not have any identifiable field that I can see that would make them group one way as opposed to another.

What are/is your grouping methodology here?

Thanks

J. Kusch

 

[mercwrought]

Hi J, I’m sorry I should have explained it better
Each group is based on consecutive dates. With any break starting a new group.
Group 1 is 1-2-3
If there was a 5/4/2005 date, group 1 would have been 1-2-3-4-5-6

Here is how I have figured out to do it right now

create table #p( begindate datetime, enddate datetime)
insert into #p
select  date,Null 
from mytable s1 
where(select count(*) from mytable s2 where s2.date = s1.date-1)=0


update #p
set enddate = isnull((select date from mytable s1 where date= begindate+1),begindate)


while @@rowcount > 0 
BEGIN
	update #p
	set enddate = (select date from mytable s1 where date= enddate+1)
	where 	(select date from mytable s1 where date= enddate+1) is not null
END

Not very pretty nor as fast as I would like but it will be faster than that cursor that they use now. Please let me know what you think and if you know a better way of doing this

 

[vongrunt]

Congrats - date, group, from and to are all reserved words

I was thinking about something like:

-- get lower interval boundaries
select identity(int, 1, 1) as [group], A.[date]
into #tmp1
from blah A
left outer join blah B on A.[date]=B.[date]+1
where B.[date] is null
order by A.[date]

-- get upper interval boundaries
select identity(int, 1, 1) as [group], A.[date]
into #tmp2
from blah A
left outer join blah B on A.[date]=B.[date]-1
where B.[date] is null
order by A.[date]

-- join both, aggregate total
select A.[group], A.[date], B.[date], sum(amount) as amount
from #tmp1 A 
inner join #tmp2 B on A.[group]=B.[group]
inner join blah C on C.date between A.[date] and B.[date]
group by A.[group], A.[date], B.[date]
order by A.[group]

drop table #tmp1
drop table #tmp2

Personally I would be VERY careful about that implementation because of two things:

- it heavily relies on ordered singleton SELECTs
- it is sensitive to nonrepeatable reads/phantom rows (isolation level 2 or 3 may be required).

On the other hand, if number of gaps is small then temp tables will have very few rows... try it anyway.

All-in-one query, no temp tables and identities:

select A.[from], A.[to], sum(B.amount) as amount
from
(	select A.[date] as [from], min(C.[date]) as [to]
	from blah A
	left outer join blah B on A.[date]=B.[date]+1
	inner join 
	(	select A.[date]
		from blah A
		left outer join blah B on A.[date]=B.[date]-1
		where B.[date] is null
	) C on C.[date] >= A.[date]
	where B.[date] is null
	group by A.[date]
) A
inner join blah B on B.[date] between A.[from] and A.[to]
group by A.[from], A.[to]
order by A.[from]

Another way: calculate all possible continuous intervals then do nested MIN()/MAX():

select min([from]) as [from], [to], max(amount) as amount
from
(	select [from], max([to]) as [to], max(amount) as amount
	from
	(	select A.date as [from], B.date as [to], sum(C.amount) as amount
		from blah A
		inner join blah B on A.date<=B.date
		inner join blah C on C.date between A.date and B.date
		group by A.date, B.date
		having count(*)-1 = datediff(dd, A.date, B.date)
	) X
	group by X.[from]
)Y
group by Y.[to]
order by Y.[to]

It is surely too slow - innermost query generates (N+1)*N/2 rows, all of which must pass thru HAVING filter - and therefore good only as a brain exercise.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mercwrought]

vongrunt thx very much for your post it is exactly what I am looking for right now. It looks as if you have the makings of a nice FAQ there to.