OVER PARTION ? - 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)

OVER PARTION ?

Author

Topic

gwjones12
Starting Member

9 Posts

Posted - 2011-03-10 : 16:38:35

I have a table "Schedule" like this:

inDates outDates

2010-04-01 NULL
2010-04-02 NULL
2010-04-03 NULL
2010-04-04 NULL
2010-04-05 NULL
NULL 2010-04-06
2010-04-07 NULL
2010-04-08 NULL
2010-04-09 NULL
2010-04-10 NULL
NULL 2010-04-11
2010-04-12 NULL
2010-04-13 NULL

I need to create a query that results in this:

PeriodStart, PeriodEnd

2010-04-01, 2010-04-05
2010-04-07, 2010-04-10
2010-04-12, 2010-04-13

Possible?
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-10 : 20:04:57

Here is one way to do it - probably not the most elegant or not the simplest (and not tested to boot!) Besides, as I was typing this, I had various questions, and I made assumptions in each case. But it compiles, the parser liked it :--)

You have to have an ordering scheme, and I assumed ordereing by date.

with OrderedCte as
(
   select
      row_number() over (order by coalesce(inDates,outDates)) as rowId,
      *
   from YourTable
)
select
   a.inDates as PeriodStart,
   min(b.inDates) PeriodEnd
from
   OrderedCte a
   left join OrderedCte b on
      b.rowId >= a.rowId
      and exists (select * from OrderedCte c where c.rowId = b.rowId+1 and c.Indates is null)
where
   (a.rowId = 1 or exists (select * from OrderedCte c where c.rowId=a.rowId-1 and c.Indates is null))
   and a.inDates is not null
group by
   a.inDates

gwjones12
Starting Member

9 Posts

Posted - 2011-03-11 : 08:56:56

Works great THANKS!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-11 : 13:32:56

Welcome GW.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources