August 2, 2013 at 4:28 am
Hi! I have a query where I want a group id every x rows.
Here is some sample data of what I'm working on:
create table #temp (
reference nvarchar(10),
collection_day nvarchar(50),
sort_date datetime,
)
insert into #temp (reference, collection_day, sort_date)
select '02015','Friday 7th December 2012','2012-12-07 09:00:00.000' union all
select '02015','Friday 7th December 2012','2012-12-07 11:00:00.000' union all
select '02015','Friday 7th December 2012','2012-12-07 13:00:00.000' union all
select '02015','Friday 7th December 2012','2012-12-07 15:00:00.000' union all
select '02015','Friday 7th December 2012','2012-12-07 17:00:00.000' union all
select '02015','Friday 7th December 2012','2012-12-07 19:00:00.000' union all
select '02015','Friday 7th December 2012','2012-12-07 21:00:00.000' union all
select '02015','Saturday 8th December 2012','2012-12-08 09:00:00.000' union all
select '02015','Saturday 8th December 2012','2012-12-08 11:00:00.000' union all
select '02015','Saturday 8th December 2012','2012-12-08 13:00:00.000' union all
select '02015','Saturday 8th December 2012','2012-12-08 15:00:00.000' union all
select '02015','Saturday 8th December 2012','2012-12-08 17:00:00.000' union all
select '02015','Saturday 8th December 2012','2012-12-08 19:00:00.000' union all
select '02015','Saturday 8th December 2012','2012-12-08 21:00:00.000' union all
select '02015','Sunday 5th May 2013','2013-05-05 09:00:00.000' union all
select '02015','Sunday 5th May 2013','2013-05-05 11:00:00.000' union all
select '02015','Sunday 5th May 2013','2013-05-05 11:00:00.000' union all
select '02015','Sunday 5th May 2013','2013-05-05 12:00:00.000' union all
select '02015','Sunday 5th May 2013','2013-05-05 13:00:00.000' union all
select '02015','Sunday 5th May 2013','2013-05-05 13:00:00.000' union all
select '02015','Sunday 5th May 2013','2013-05-05 13:00:00.000' union all
select '02015','Sunday 5th May 2013','2013-05-05 14:00:00.000' union all
select '02015','Sunday 5th May 2013','2013-05-05 14:00:00.000' union all
select '02016','Monday 6th May 2013','2013-05-06 09:00:00.000' union all
select '02016','Monday 6th May 2013','2013-05-06 11:00:00.000' union all
select '02016','Monday 6th May 2013','2013-05-06 13:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 08:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 08:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 10:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 10:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 12:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 12:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 14:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 16:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 16:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 18:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 18:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 20:00:00.000' union all
select '02016','Tuesday 7th May 2013','2013-05-07 20:00:00.000'
select
row_number() OVER (PARTITION BY reference, collection_day ORDER BY sort_date) AS rnum
,collection_day, reference, sort_date
from #temp
order by reference, sort_date
So the output here is:
rnumcollection_dayreferencesort_date
1Friday 7th December 2012020152012-12-07 09:00:00.000
2Friday 7th December 2012020152012-12-07 11:00:00.000
3Friday 7th December 2012020152012-12-07 13:00:00.000
4Friday 7th December 2012020152012-12-07 15:00:00.000
5Friday 7th December 2012020152012-12-07 17:00:00.000
6Friday 7th December 2012020152012-12-07 19:00:00.000
7Friday 7th December 2012020152012-12-07 21:00:00.000
1Saturday 8th December 2012020152012-12-08 09:00:00.000
2Saturday 8th December 2012020152012-12-08 11:00:00.000
3Saturday 8th December 2012020152012-12-08 13:00:00.000
4Saturday 8th December 2012020152012-12-08 15:00:00.000
5Saturday 8th December 2012020152012-12-08 17:00:00.000
6Saturday 8th December 2012020152012-12-08 19:00:00.000
7Saturday 8th December 2012020152012-12-08 21:00:00.000
1Sunday 5th May 2013020152013-05-05 09:00:00.000
2Sunday 5th May 2013020152013-05-05 11:00:00.000
3Sunday 5th May 2013020152013-05-05 11:00:00.000
4Sunday 5th May 2013020152013-05-05 12:00:00.000
5Sunday 5th May 2013020152013-05-05 13:00:00.000
6Sunday 5th May 2013020152013-05-05 13:00:00.000
7Sunday 5th May 2013020152013-05-05 13:00:00.000
8Sunday 5th May 2013020152013-05-05 14:00:00.000
9Sunday 5th May 2013020152013-05-05 14:00:00.000
1Monday 6th May 2013020162013-05-06 09:00:00.000
2Monday 6th May 2013020162013-05-06 11:00:00.000
3Monday 6th May 2013020162013-05-06 13:00:00.000
1Tuesday 7th May 2013020162013-05-07 08:00:00.000
2Tuesday 7th May 2013020162013-05-07 08:00:00.000
3Tuesday 7th May 2013020162013-05-07 10:00:00.000
4Tuesday 7th May 2013020162013-05-07 10:00:00.000
5Tuesday 7th May 2013020162013-05-07 12:00:00.000
6Tuesday 7th May 2013020162013-05-07 12:00:00.000
7Tuesday 7th May 2013020162013-05-07 14:00:00.000
8Tuesday 7th May 2013020162013-05-07 16:00:00.000
9Tuesday 7th May 2013020162013-05-07 16:00:00.000
10Tuesday 7th May 2013020162013-05-07 18:00:00.000
11Tuesday 7th May 2013020162013-05-07 18:00:00.000
12Tuesday 7th May 2013020162013-05-07 20:00:00.000
13Tuesday 7th May 2013020162013-05-07 20:00:00.000
I want a new group id (incremental for the whole set) every 6 rows with a collection day, or obviously where the collection day or the reference changes
i.e.
groupidrnumcollection_dayreferencesort_date
11Friday 7th December 2012020152012-12-07 09:00:00.000
12Friday 7th December 2012020152012-12-07 11:00:00.000
13Friday 7th December 2012020152012-12-07 13:00:00.000
14Friday 7th December 2012020152012-12-07 15:00:00.000
15Friday 7th December 2012020152012-12-07 17:00:00.000
16Friday 7th December 2012020152012-12-07 19:00:00.000
27Friday 7th December 2012020152012-12-07 21:00:00.000
31Saturday 8th December 2012020152012-12-08 09:00:00.000
32Saturday 8th December 2012020152012-12-08 11:00:00.000
33Saturday 8th December 2012020152012-12-08 13:00:00.000
34Saturday 8th December 2012020152012-12-08 15:00:00.000
35Saturday 8th December 2012020152012-12-08 17:00:00.000
36Saturday 8th December 2012020152012-12-08 19:00:00.000
47Saturday 8th December 2012020152012-12-08 21:00:00.000
51Sunday 5th May 2013020152013-05-05 09:00:00.000
52Sunday 5th May 2013020152013-05-05 11:00:00.000
53Sunday 5th May 2013020152013-05-05 11:00:00.000
54Sunday 5th May 2013020152013-05-05 12:00:00.000
55Sunday 5th May 2013020152013-05-05 13:00:00.000
56Sunday 5th May 2013020152013-05-05 13:00:00.000
67Sunday 5th May 2013020152013-05-05 13:00:00.000
68Sunday 5th May 2013020152013-05-05 14:00:00.000
69Sunday 5th May 2013020152013-05-05 14:00:00.000
71Monday 6th May 2013020162013-05-06 09:00:00.000
72Monday 6th May 2013020162013-05-06 11:00:00.000
73Monday 6th May 2013020162013-05-06 13:00:00.000
81Tuesday 7th May 2013020162013-05-07 08:00:00.000
82Tuesday 7th May 2013020162013-05-07 08:00:00.000
83Tuesday 7th May 2013020162013-05-07 10:00:00.000
84Tuesday 7th May 2013020162013-05-07 10:00:00.000
85Tuesday 7th May 2013020162013-05-07 12:00:00.000
86Tuesday 7th May 2013020162013-05-07 12:00:00.000
97Tuesday 7th May 2013020162013-05-07 14:00:00.000
98Tuesday 7th May 2013020162013-05-07 16:00:00.000
99Tuesday 7th May 2013020162013-05-07 16:00:00.000
910Tuesday 7th May 2013020162013-05-07 18:00:00.000
911Tuesday 7th May 2013020162013-05-07 18:00:00.000
912Tuesday 7th May 2013020162013-05-07 20:00:00.000
1013Tuesday 7th May 2013020162013-05-07 20:00:00.000
I can do it with a cursor but I'm sure there must be a good way to use a rank function to achieve this..
Any help would be much appreciated! 🙂
August 2, 2013 at 4:40 am
SELECT
DENSE_RANK() OVER(ORDER BY reference, collection_day, (rnum-1)/6 ),
*
FROM (
select
row_number() OVER (PARTITION BY reference, collection_day ORDER BY sort_date) AS rnum
,collection_day, reference, sort_date
from #temp
) d
order by reference, sort_date
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 2, 2013 at 4:48 am
Perfect, thank you! Thought it might be something like that but couldn't get it right.
Cheers!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply