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

Transact-SQL (2005)

Sql help

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2009-11-03 : 23:20:28

Here is data
SELECT CONVERT(datetime,'2009-11-04') dt INTO #x UNION
SELECT CONVERT(datetime,'2009-11-11') UNION
SELECT CONVERT(datetime,'2009-11-20') UNION
SELECT CONVERT(datetime,'2009-11-27') UNION
SELECT CONVERT(datetime,'2009-12-04') UNION
SELECT CONVERT(datetime,'2009-12-20')

how i can get
fromdt todt
2009-11-04 2009-11-11
2009-11-20 2009-20-04
2009-12-20 2009-12-20

basically if dates with in 7 day difference should be grouped

mk_garg

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-11-04 : 00:03:48

how do you determine which is fromdt and todt?

Hope can help...but advise to wait pros with confirmation...

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2009-11-04 : 00:12:23

first is 2009-11-04
all record after that with 7 day interval will be added to this row.

When this interval is broken, new row will be started.
that means new row start with 2009-11-20

mk_garg

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-04 : 01:47:27

[code]
; with
cte1
as
(
select *, row_no = row_number() over (order by dt)
from #x
),
cte2
as
(
select *, grp = 1
from cte1
where row_no = 1

union all

select a.dt, a.row_no, grp = case when datediff(day, b.dt, a.dt) <= 7 then b.grp else b.grp + 1 end
from cte1 a
inner join cte2 b on a.row_no = b.row_no + 1
)
select fromdt = min(dt), todt = max(dt)
from cte2
group by grp
[/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