Find next consecutive month - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Find next consecutive month

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2014-04-08 : 04:03:59

Hi,
I have 2 tables. I need to find per idno in table tmpProcess the consecutive months following the mxdate in table #cte but less than the mnDate. If the mxdate is 2012-07 in table CTE but the month following that is 2012-09 then it's not relevant. Only if the months following that start from 2012-08.

Any thoughts on how to do that?

Thanks

CREATE TABLE #CTE (IDNO INT,MNDATE DATETIME,MXDATE DATETIME)		
INSERT INTO #CTE VALUES ( 307,'2012-06-01 00:00:00.000','2012-07-01 00:00:00.000'	)
INSERT INTO #CTE VALUES ( 307,'2012-11-01 00:00:00.000','2013-03-01 00:00:00.000'	)
INSERT INTO #CTE VALUES (656,'2013-07-01 00:00:00.000',	'2013-08-01 00:00:00.000')

CREATE TABLE #tmpProcess (IDNO INT,ACTUALDATE DATETIME)
INSERT INTO #tmpProcess VALUES (307,'2012-08-01 00:00:00.000')
INSERT INTO #tmpProcess VALUES (307,'2012-09-01 00:00:00.000')
INSERT INTO #tmpProcess VALUES (307,'2013-04-01 00:00:00.000')
INSERT INTO #tmpProcess VALUES (307,'2013-06-01 00:00:00.000')
INSERT INTO #tmpProcess VALUES (656,'2013-10-01 00:00:00.000')
INSERT INTO #tmpProcess VALUES (656,'2013-11-01 00:00:00.000')
INSERT INTO #tmpProcess VALUES (656,'2013-12-01 00:00:00.000')

outputSELECT 307,'2012-08-01 00:00:00.000', '2012-06-01 00:00:00.000','2012-07-01 00:00:00.000'UNION
SELECT 307,'2012-09-01 00:00:00.000', '2012-06-01 00:00:00.000','2012-07-01 00:00:00.000'UNION
SELECT 307,'2013-04-01 00:00:00.000', '2012-11-01 00:00:00.000','2013-03-01 00:00:00.000'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-08 : 04:18:56

[code]; with rcte as
(
select c.IDNO, p.ACTUALDATE, c.MNDATE, c.MXDATE
from #CTE c
inner join #tmpProcess p on c.IDNO = p.IDNO
and c.MXDATE = dateadd(month, -1, p.ACTUALDATE)

union all

select c.IDNO, p.ACTUALDATE, c.MNDATE, c.MXDATE
from rcte c
inner join #tmpProcess p on c.IDNO = p.IDNO
and c.ACTUALDATE = dateadd(month, -1, p.ACTUALDATE)

)
select *
from rcte
order by IDNO, ACTUALDATE[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources