Problem for recursion? or another solution? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Problem for recursion? or another solution?

Author

Topic

Vogel515
Starting Member

5 Posts

Posted - 2012-08-03 : 13:30:54

I have a table with thousands of records which have a uniqID, a start date, and an end date. I would like to get a count of all records which overlap a given fiscal period.

For instance, if I am looking at a 12 month period there are 12 fiscal periods:

2001 1
2001 2
2001 3
2001 4
2001 5
2001 6
2001 7
2001 8
2001 9
2001 10
2001 11
2001 12

There are three records which overlap any of these periods
UniqID Start End
1 2001 5 2001 6
2 2000 1 2003 12
3 2001 11 2004 1

I'd like the query to return:
FY FP UniqID
2001 1 2
2001 2 2
2001 3 2
2001 4 2
2001 5 1
2001 5 2
2001 6 1
2001 6 2
2001 7 2
2001 8 2
2001 9 2
2001 10 2
2001 11 2
2001 11 3
2001 12 2
2001 12 3

Any ideas? Thanks in advance...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-03 : 14:09:49

You can join the two tables like this:

SELECT
	FiscalYear,
	FiscalMonth,
	p.UniqueID
FROM
	FiscalPeriods f
	LEFT JOIN Periods p ON
		DATEFROMPARTS(p.StartYear,p.StartMonth,1) <= DATEFROMPARTS(f.FiscalYear,f.FiscalMonth,1)
		AND DATEFROMPARTS(p.EndYear,p.EndMonth,1) >= DATEFROMPARTS(f.FiscalYear,f.FiscalMonth,1)
ORDER BY 
	1

DATEFROMPARTS is a SQL2012 function. If you are on a lower version (which you most likely are) replace that with

CAST(CAST(StartYear AS CHAR(4))+ RIGHT('0'+CAST(Startmonth AS VARCHAR(2)),2) + '01' AS DATETIME)

Vogel515
Starting Member

5 Posts

Posted - 2012-08-03 : 14:57:03

Thank you, that was perfect.

I modified it slightly so that it would check to see if the start period started before the end of the month, and the end period after the start of the month.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-03 : 15:43:45

You are welcome, glad to be of help .)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources