Consecutive Year Test - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Consecutive Year Test

Author

Topic

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-10 : 14:39:04

Please Help! I have to select all companies in our DB where the FL flag is set to 'N' for 3 consecutive years. The problem I am having is that I am not sure how to know if the years are consecutive. Thanks for any assistance...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 14:42:43

do you've an year field in table as such? or is it a date field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-10 : 14:49:14

The year field is in each record of the table.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2012-05-10 : 15:11:10

One way that doesn't seem extremely elegant but it gets the job done:


select y1.company, y1.year as FirstYear, y3.year as LastYear
from yourtable y1
inner join  
  yourtable y2 on y1.company = y2.company and y2.year= y1.year+1
inner join
  yourtable y3 on y2.company = y3.company and y3.year = y1.year+2
where
 y1.FL ='N' and y2.FL='N' and y3.FL = 'N'

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 15:27:41

quote:
Originally posted by pgmr1998

The year field is in each record of the table.


SELECT Company
FROM
(
SELECT (DENSE_RANK() OVER (PARTITION BY Company ORDER BY Year)-1)/3 AS Rnk,*
FROM table
)t
GROUP BY Company,Rnk
HAVING COUNT(DISTINCT Year) = 3)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2012-05-10 : 16:00:56

visakh16 -- That doesn't ensure that the 3 years are consecutive. See below; company #2 should not be returned but it is:


declare @t table
(
	Company int,
	Year int
)

insert into @t
select 1,2010 union all
select 1,2011 union all
select 1,2012  union all
select 2,2009  union all
select 2,2010  union all
select 2,2012

SELECT Company

FROM
(
SELECT (DENSE_RANK() OVER (PARTITION BY Company ORDER BY Year)-1)/3 AS Rnk,*
FROM @T
)t
GROUP BY Company,Rnk
HAVING COUNT(DISTINCT Year) = 3

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2012-05-10 : 16:08:55

Here's perhaps a more elegant and more flexible way to do it; you can vary how many consecutive years you require by changing the variable/parameter:



declare @t table
(
	Company int,
	Year int
)

insert into @t
select 1,2010 union all
select 1,2011 union all
select 1,2012  union all
select 2,2009  union all
select 2,2010  union all
select 2,2012

 
declare @NumOfConsecutiveYears int = 3

select y1.company, y1.Year as Firstyear, MAX(y2.Year) as LastYear
from @t y1
inner join @t y2 on y1.Company = y2.Company and y2.Year between y1.Year and y1.Year + @NumOfConsecutiveYears - 1
group by y1.Company, y1.Year 
having COUNT(distinct y2.year) = @NumOfConsecutiveYears

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-10 : 16:17:37

Or this?

declare @t table
(
	Company int,
	Year int
)

insert into @t
select 1,2010 union all
select 1,2011 union all
select 1,2012  union all
select 1,2001 union all
select 1,2002 union all
select 1,2003  union all
select 1,2004  union all
select 2,2009  union all
select 2,2010  union all
select 2,2012  union all
select 2,2014  union all
select 2,2015  union all
select 2,2016

-- SwePeso
;WITH cteSource(Company, [Year], SeqID)
AS (
	SELECT	Company,
		[Year],
		[Year] - ROW_NUMBER() OVER (PARTITION BY Company ORDER BY [Year]) AS SeqID
	FROM	@T
	--WHERE	Flag = 'N'
)
SELECT		Company,
		MIN([Year]) AS FromYear,
		MAX([Year]) AS ToYear
FROM		cteSource
GROUP BY	Company,
		SeqID
HAVING		SUM([Year]) - MIN([Year]) * COUNT(*) >= 3
ORDER BY	Company,
		SeqID

N 56°04'39.26"
E 12°55'05.63"

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-10 : 16:36:16

How could I put in an OR condition to the above? So, if the above returns nothing, the OR condition may pick up documents. This condition is not to use the JOINS, but simply the table itself to selcet documents based on a single condition - the value of a single field.

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-10 : 17:17:03

When I append an OR condition, nothing is returned because the JOINS return nothing. How could an OR condition be added such that it gets evaluated independently of the JOINS.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-11 : 00:25:50

quote:

Originally posted by SwePeso

-- SwePeso
;WITH cteSource(Company, [Year], SeqID)
AS (
	SELECT	Company,
		[Year],
		[Year] - ROW_NUMBER() OVER (PARTITION BY Company ORDER BY [Year]) AS SeqID
	FROM	@T
	--WHERE	Flag = 'N'
)
SELECT		Company,
		MIN([Year]) AS FromYear,
		MAX([Year]) AS ToYear
FROM		cteSource
GROUP BY	Company,
		SeqID
HAVING		COUNT(*) >= 3
ORDER BY	Company,
		SeqID

N 56°04'39.26"
E 12°55'05.63"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2012-05-11 : 08:50:27

quote:
Originally posted by pgmr1998

When I append an OR condition, nothing is returned because the JOINS return nothing. How could an OR condition be added such that it gets evaluated independently of the JOINS.

You did not tell us which of these solutons are you trying. How can we help you without knowing?

Also, it is hard to guess what you are saying about the OR, you have not really explained what you need to do. At the very least, show us some of the code you are trying; at best, look at the code we wrote that provides some sample data and some code to test with using that sample data; provide that and it's easier for everyone involved to help out. Even better, it's a great strategy for anyone who's trying to solve a SQL problem or learn a new SQL technique. Even us "experts" do that all the time.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2012-05-11 : 09:03:49

quote:

Originally posted by SwePeso

quote:

Originally posted by SwePeso

-- SwePeso
;WITH cteSource(Company, [Year], SeqID)
AS (
	SELECT	Company,
		[Year],
		[Year] - ROW_NUMBER() OVER (PARTITION BY Company ORDER BY [Year]) AS SeqID
	FROM	@T
	--WHERE	Flag = 'N'
)
SELECT		Company,
		MIN([Year]) AS FromYear,
		MAX([Year]) AS ToYear
FROM		cteSource
GROUP BY	Company,
		SeqID
HAVING		COUNT(*) >= 3
ORDER BY	Company,
		SeqID

N 56°04'39.26"
E 12°55'05.63"

It took me a few minutes to figure out what you are doing there, but that's a brilliant way to "group" the runs of consecutive years.

For the OP, the key to his technique is what the CTE is calculating:


	SELECT	Company,
		[Year],
		[Year] - ROW_NUMBER() OVER (PARTITION BY Company ORDER BY [Year]) AS SeqID
	FROM	@T

Very clever!

- Jeff
http://weblogs.sqlteam.com/JeffS

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-11 : 10:09:16

Please accept my apologies for lack of clarity. I decided to use the first method presented. Here is the code. Notice that I have a UNION ALL statement separating the two SELECTs. This I know combines them as an AND would do. But, I want to join them in an OR fashion - where if a document is selected in the forst SELECT, it will not be repeated by the second one.

select AgencyID, ReportReceivedDate, ReportDueDate from dbo.MYDB
where
(ReportReceivedDate > ReportDueDate) or
Grade = 'D' or
(Other = -1 and OtherReason = 'High Profile')
UNION ALL
select y1.AgencyID, y1.ReportReceivedDate as FirstYear, y3.ReportReceivedDate as LastYear
from MYDB y1
inner join
LLA_Audits y2 on y1.AgencyID = y2.AgencyID and y2.ReportReceivedDate = y1.ReportReceivedDate + 1
inner join
LLA_Audits y3 on y2.AgencyID = y3.AgencyID and y3.ReportReceivedDate = y1.ReportReceivedDate + 2
where
(y1.ReportReceivedDate > y1.ReportDueDate and y2.ReportReceivedDate > y2.ReportDueDate and y3.ReportReceivedDate > y3.ReportDueDate)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-11 : 10:26:40

Change "UNION ALL" to "UNION"?

N 56°04'39.26"
E 12°55'05.63"

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-11 : 11:14:34

How can I remove duplicate row output?

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-11 : 11:25:11

Yes, but for now I am testing the second query by itself, and it is producing duplicate company number (AgencyID). This is bacause there are companies that have multiple 3-year consecutive dates - so those companies pass the test more than once, and therefore appear more than once in the output.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources