Click here to Skip to main content
15,034,432 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
I have a event table in which the list of user participated will be stored.

Sample Data:

VB
╔════╦══════╦══════╗
║ id ║ name ║ year ║
╠════╬══════╬══════╣
║  1 ║ Arun ║ 2001 ║
║  2 ║ Arun ║ 2002 ║
║  3 ║ Arun ║ 2003 ║
║  4 ║ Arun ║ 2004 ║
║  5 ║ Arun ║ 2009 ║
║  6 ║ Arun ║ 2010 ║
║  7 ║ Arun ║ 2011 ║
║  8 ║ Bala ║ 2014 ║
║  9 ║ Bala ║ 2015 ║
║ 10 ║ Bala ║ 2017 ║
║ 11 ║ chan ║ 2014 ║
║ 12 ║ chan ║ 2015 ║
║ 13 ║ chan ║ 2018 ║
╚════╩══════╩══════╝



I need to find only the user who have attended continuously for three years with the years in new column. Even if the user attended for continuous 6 years then have to split into first three in an column and next three in another column.

Output:

VB
╔════╦══════╦═══════════╗
║ id ║ name ║   years   ║
╠════╬══════╬═══════════╣
║  1 ║ Arun ║ 2001-2003 ║
║  2 ║ Arun ║ 2009-2011 ║
╚════╩══════╩═══════════╝
Posted
Updated 10-Apr-21 4:57am
Comments
OriginalGriff 19-Oct-15 5:29am
   
And?
What have you tried?
Where are you stuck?
What help do you need?
Ragul M 19-Oct-15 5:33am
   
But it's not working as i need.
select name, min(year), max(year)
from (select e.*,
(year - row_number() over (partition by name order by year)
) as grp
from events e
) e
group by name, grp
having count(*) >= 3;
Tomas Takac 19-Oct-15 9:25am
   
Please update your question with the code. There you can properly format it.
Ragul M 19-Oct-15 6:19am
   
No one yet to help ??
Sreekanth Mothukuru 19-Oct-15 9:52am
   
check the solution...

This query should fix your problem:

SQL
select  ROW_NUMBER() over (ORDER BY Min(id)) AS Number, name, cast( min(year) as varchar(100)) + ' - ' + cast(max(year) as varchar(100)) as years
from (
		select e.*, (year - row_number() over (partition by name order by year)) as grp from tblStudent e
) e
group by name, grp
having count(*) >= 3;
   
v3
Comments
Maciej Los 19-Oct-15 10:21am
   
Looks promising...
Using your year - row_number() over, you could do an additional modulus calculation of 3 and then filter with 0.

Something like this:
SQL
with events as(
--setup dummy data
	select 
		1 id,
		'Arun' name,
		2001 year
	union all select 2, 'Arun', 2002
	union all select 3, 'Arun', 2003
	union all select 4, 'Arun', 2004
	union all select 5, 'Arun', 2009
	union all select 6, 'Arun', 2010
	union all select 7, 'Arun', 2011
	union all select 8, 'Bala', 2014
	union all select 9, 'Bala', 2015
	union all select 10, 'Bala', 2017
	union all select 11, 'chan', 2014
	union all select 12, 'chan', 2015
	union all select 13, 'chan', 2018
--extra data
	union all select 14, 'Arun', 2006
	union all select 15, 'Arun', 2005
	union all select 16, 'Bala', 2018
	union all select 17, 'Bala', 2020
	union all select 18, 'Bala', 2019
	union all select 22, 'Jim', 2009
	union all select 21, 'Jim', 2010
	union all select 20, 'Jim', 2011

), eventsGroup as (
	--part of your sql
	select
		e.*, 
		(year - row_number() over (partition by name order by year)) as grp 
	from events e
), eventsGroup3Year as (
	--do a modulus check
	select
		*, 
		(row_number() over (partition by name, grp order by year))%3 as rowid3
	from eventsGroup
)
select 
	row_number() over(order by  name, year) id,
	name,
	convert(char(4), year-2)+' - '+convert(char(4), year) years
from eventsGroup3Year
where rowid3 = 0
order by
	name,
	year
;

It will probably not work if you have duplicate values,
lets say an additional row of 'Arun', 2003 - you will need to test that out.

Hope that helps out.
-----
Additional sql [for some reason this question has gotten stuck in my head :)]:
To get the start and end year in the same row/record, you could use the LAG analytic function (ms sql 2012 upwards).

Something like this:
SQL
with (
--	...cte as above, up to eventsGroup3Year...
)
, LagYear as(
select 
	*,
	lag(year, 1, 0) over (order by name, year) StartYear
from eventsGroup3Year
where rowid3 = 0 or rowid3 = 1
)
select
	row_number() over(order by name, year) id,
	name,
	concat(StartYear, ' - ', year) Years -- concat via solution 3, thanks Maciej Los
from LagYear where rowid3 = 0
;

LAG (Transact-SQL): https://msdn.microsoft.com/en-us/library/hh231256.aspx[^]
   
v4
Comments
Ragul M 20-Oct-15 6:38am
   
Best solution.
I think you enjoyed my question as i did :) , even more than me i think :p
jaket-cp 20-Oct-15 7:04am
   
yes - you may be right :)
but your
(year - row_number() over (partition by name order by year)) as grp
was the main part of the solution
just added some extra bits to it, that is all
happy coding
Maciej Los 20-Oct-15 6:49am
   
5ed!
jaket-cp 20-Oct-15 7:02am
   
thanks
Ragul M 17-Nov-15 6:57am
   
I have another question for you which will be more fun than this question.

http://www.codeproject.com/Questions/1056693/How-to-extract-names-from-varchar-column-in-SQL-Se
Another solution:
SQL
DECLARE @tmp TABLE(ID INT, [Name] VARCHAR(30), [Year] INT)

INSERT INTO @tmp (ID, [Name], [Year])
VALUES(1, 'Arun', 2001),
(2, 'Arun', 2002),
(3, 'Arun', 2003),
(4, 'Arun', 2004),
(5, 'Arun', 2009),
(6, 'Arun', 2010),
(7, 'Arun', 2011),
(8, 'Bala', 2014),
(9, 'Bala', 2015),
(10, 'Bala', 2017),
(11, 'chan', 2014),
(12, 'chan', 2015),
(13, 'chan', 2018),
(14, 'zen', 2014),
(15, 'zen', 2015)

--4
SELECT [Name], CONCAT([1], ' - ', [3]) AS Years
FROM (
	--3
	SELECT [Name], [Grp], [1], [2], [3]
	FROM (
		--2
		SELECT [Name], [Year], Grp, ROW_NUMBER() OVER(PARTITION BY [Name], [Grp] ORDER BY [Year]) AS RowNo
		FROM (
			--1
			SELECT [Name], [Year], [Year] - ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY [Year]) AS Grp
			FROM @tmp
		) AS T
	) AS SRC
	PIVOT(MAX([Year]) FOR [RowNo] IN ([1], [2], [3])) AS PVT
	WHERE COALESCE([3],0) - COALESCE([1],0) = 2
) AS DST


What the followig queries do?
1. query creates "group" for consecutive years
2. query add row number for each group
3. query creates pivot table and returns only those data which the result of substraction is equal 2
4. query - final query - concatenates years
   
Comments
jaket-cp 20-Oct-15 4:12am
   
nice - but does not cater for OP continuous 6 years scenario.
When (16, 'Arun', 2005), (17, 'Arun', 2006) is added to the test data, it only returns the first 3 years.
no disrespect :)
Maciej Los 20-Oct-15 6:50am
   
Thank you for your valuable comment.
jaket-cp 20-Oct-15 7:01am
   
:) you are the best...
Maciej Los 20-Oct-15 7:16am
   
;) Thank you. And you too!
I have encountered this as challenge lately and I solved it using lead windows function
just order the result by year and find the lead and the next lead then eliminate null values at last in the where conditions check for sequence rule.
I think this is simpler , easier , more human readable and more dynamic and reusable.
check this might help:


with seq as(
SELECT name, year st
,lead(year) over ( partition by name order by year) nd
,lead(year,2) over ( partition by name order by year) ed
FROM my_tasks.participants
order by name,year)
select name
from seq
where nd is not null
and ed is not null
and ed=nd+1
and nd=st+1
group by name ;
   
v2
Comments
Richard Deeming 12-Apr-21 6:52am
   
This question was solved back in 2015. An unexplained code-dump adds nothing to the discussion. At the very least, you need to explain why you think your solution is better than the ones already posted.
Member 15145141 12-Apr-21 7:20am
   
I just updated my comment however just for more clarifying when I made my comment I meant to help those who will face this as challenge.
CHill60 12-Apr-21 8:40am
   
Reasons for my downvote and report
1. When I run your code I get an exception "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified."
2. You are only reporting names but the OP requested the output to include the ID and the years active
3. You will note that the expected results are meant to show each period of 3 years, your solution does not
3. It is not "more human readable" - I had to inspect the code to try to work out the relevence of using ed=nd+1 and nd=st=1 as filters. And I'm still not sure why.
4. You have not explained why LEAD is "better" than LAG (as used in Solution 2 in 2015)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900