Date calc logic in same column

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 2005 Forums

Transact-SQL (2005)

Date calc logic in same column

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-12-30 : 01:18:44

Dear All Experts,

I have a table in which table is showing date in following manner.

ID	STARTDATE
1	01/01/2010
1	20/02/2010
1	15/09/2010
2	03/04/2010
2	09/11/2010
3	11/01/2010
3	18/03/2010

Now i want the output in following manner.

ID	STARTDATE
1	01/01/2010
1	20/02/2010
3	11/01/2010
3	18/03/2010

It will check if the month difference of everyID if more then 4 month, then it will not show that entry.

For ex for ID = 1, in result its shows two entry only last one is rejected because the date difference of

1 20/02/2010
1 15/09/2010

is more then 4 month.

Please help me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-12-30 : 02:13:11

4 month with reference to an earlier date ?


; with data as
(
	select	*, row_no = row_number() over (partition by ID order by STARTDATE)
	from	yourtable
)
select	d2.ID, d2.STARTDATE
from	data d2
	left join data d1	on	d2.ID		= d1.ID
				and	d2.row_no	= d1.row_no + 1
where	datediff(month, d1.STARTDATE, d2.STARTDATE) <= 4
or	d1.ID	is null

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

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-12-30 : 02:23:47

Thanks KHTAN,

As per your query its showing ID=2 data also, which should not come in that.
because month diff is greater then 4 month.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources