Re: Zusammenhängende Zeiträume ermitteln

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On 30 Aug., 08:22, Thomas Schremser
<vbng01.20.tschrem...@xxxxxxxxxxxxxxx> wrote:
Leider funktioniert das nicht; weil der CTE ja über alle Datensätze geht,

Leider muss ich Dir widersprechen. Die Abfrage war noch nicht
ausgereift - sie war ja auch nur so hier hinein geklimpert, aber es
geht:

----------------------------------------------------------------------------------------------------------------------------------------
create table datentabelle (PersonID int, OrgID int, Von datetime, Bis
datetime null)
go
insert into datentabelle
select 1,1,'20070101','20070331'
union select 1,1,'20070401',null
union select 1,2,'20070301',null
union select 2,3,'20070101','20070331'
union select 2,3,'20070601',null
union select 3,4,'20070101','20070731'
union select 3,4,'20070501',null
union select 4,5,'20070101','20070731'
union select 4,5,'20070501','20070630'
select * from datentabelle
go
declare @now datetime
set @now = getdate()
;with cte as (
select personid, orgid, von, bis
from datentabelle
union all
select cte.personid, cte.orgid, cte.von, dt.bis
from cte
inner join datentabelle dt
on dt.personid=cte.personid and
dt.orgid=cte.orgid and
dt.von between cte.von and isnull(cte.bis,@now)+1 and
isnull(dt.bis,@now) > isnull(cte.bis,@now)
)
select cte.personid,cte.orgid,cte.von,
case when max(isnull(cte.bis,@now))=@now then null else
max(isnull(cte.bis,@now)) end
from cte
where not exists
(select 1
from cte c2
where c2.personid=cte.personid and c2.orgid=cte.orgid and
c2.von<cte.von and isnull(c2.bis,@now)>=isnull(cte.bis,@now))
group by cte.personid,cte.orgid,cte.von
order by cte.personid,cte.orgid,cte.von
go
drop table datentabelle
go
----------------------------------------------------------------------------------------------------------------------------------------

Viel Erfolg damit.

Gruß, Manfred

.

Relevant Pages

  • Re: Using a CTE for hierarchy data, from bottom up
    ... I, however, would not recommend that you do that since unless the query optimizer is a lot smarter than I think it is, moving the where to the outer query will cause the CTE to roll up every employee, and then the outer query will select only the employees you want. ... Union All Select 6, 5 ...
    (microsoft.public.sqlserver.programming)
  • Re: Doing a dimension...
    ... I think that in union all I have to write the code that you tell me... ... "Uri Dimant" wrote: ... WITH cte ... First column has ID and second column has Name and third is operation... ...
    (microsoft.public.sqlserver.server)
  • Re: Query Optimisation problem. CTE with Ranking function used by12 x union stm
    ... In general, if a CTE is at all expensive, you may well want to use it to load a temp table or variable if you need to reference it more than once. ... Union All ... will execute the cte three times. ... I hope you can help me with this one and get this query to run a bit faster. ...
    (microsoft.public.sqlserver.server)
  • Query Optimisation problem. CTE with Ranking function used by12 x union stm
    ... I hope you can help me with this one and get this query to run a bit faster. ... CTE statement is below. ... rank() over (partition by R2T.Refrl_refno order by R2T.RTTST_Date,R2T. ... The problem is that the 12 DQ tests in the UNION ALL query take 8 seconds to ...
    (microsoft.public.sqlserver.server)