Grouping Consecutive Values by Min and Max

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
52
I have the following table for which I would like to create groups of consecutive weeks by min and max values.

AREA CodeWeek
3301
3302
3303
3304
33033
33034
33035
33036
33037
33038
33039
33040
33041
33042
33043
33044
33045
33047
33048
33049
33050

<caption> Automatics </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

The end result I would like is:

Area CodeWeek StartWeek End
33014
3303345
3304750

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I'm trying to write this in SQL but many of the solutions I've found online reference using the ROW_NUMBER() function which Access does not recognize.

Can someone please help me out?
Thanks
 

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
52
Note: This is relative to the area. So the groupings for area 330 will be independent of another area, but the logic will remain the same.
 

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
52
Got the answer at Stack Overflow.
Thanks anyway!

<code>select area, min(week), max(week)
from (select t.*,
(select count(*)
from t as t2
where t2.area = t.area and t2.week <= t.week
) as seqnum
from t
) as t
group by area, (week - seqnum);</code>
 

Forum statistics

Threads
1,077,907
Messages
5,337,096
Members
399,125
Latest member
manibiotech

Some videos you may like

This Week's Hot Topics

Top