Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Пересечение периодов с отделением ненужных  [new]
flaky_n
Member

Откуда:
Сообщений: 3
Добрый вечер,

есть таблица periods
car_iddate_startdate_end
100007.12.2011 3:0007.12.2011 13:00


есть таблица nedostup

car_iddate_fromdate_to
100007.12.2011 1:0007.12.2011 5:00
100007.12.2011 7:0007.12.2011 11:00


нужно в результате получить:
car_iddate_fromdate_to
100007.12.2011 5:0007.12.2011 7:00
100007.12.2011 11:0007.12.2011 13:00


Помогите пожалуйста с решением задачи
10 дек 11, 19:19    [11742492]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение периодов с отделением ненужных  [new]
flaky_n
Member

Откуда:
Сообщений: 3
уже ничего не надо:

declare
@priods table(
car_id int,
date_start datetime,
date_end datetime)
insert @priods (
car_id,
date_start,
date_end)
select
1000,
'2011-12-07 07:00:00',
'2011-12-07 13:00:00'
union
select
1000,
'2011-12-07 18:00:00',
'2011-12-07 19:00:00'

declare
@nedost table(
car_id int,
datefrom datetime,
dateto datetime)
insert @nedost(
car_id,
datefrom,
dateto)
select
1000,
'2011-12-07 00:00:00',
'2011-12-07 07:40:00'
union
select
1000,
'2011-12-07 08:00:00',
'2011-12-07 11:00:00'
union
select
1000,
'2011-12-07 12:00:00',
'2011-12-07 18:30:00'
;with
begs as (
select
car_id,
date_start,
tag = ROW_NUMBER() over(partition by car_id ORDER BY date_start)
FROM (
select car_id, date_start FROM @priods
UNION ALL
select car_id, datefrom FROM @nedost
UNION ALL
select car_id, date_end FROM @priods
UNION ALL
select car_id, dateto FROM @nedost
) X
)

select *
from (
select --*
b.car_id,
b.date_start,
date_end = e.date_start
FROM begs b
inner join begs e on b.car_id=e.car_id
AND b.tag+1=e.tag
/*ORDER BY
b.car_id,
b.date_start*/) rez
where exists (
select 1
from @priods p
where p.car_id = rez.car_id
and rez.date_start between p.date_start and p.date_end
and rez.date_end between p.date_start and p.date_end)
and not exists (
select 1
from @nedost n
where n.car_id = rez.car_id
and rez.date_start between n.datefrom and n.dateto
and rez.date_end between n.datefrom and n.dateto)

order by
rez.car_id,
rez.date_start
10 дек 11, 20:11    [11742692]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение периодов с отделением ненужных  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
flaky_n
уже ничего не надо:
DECLARE @Periods TABLE (
	 ID	Int
	,[From]	DateTime
	,[To]	DateTime
)
DECLARE @Exclude TABLE (
	 ID	Int
	,[From]	DateTime
	,[To]	DateTime
)
--------------------------------------------------------------------------------
INSERT	@Periods VALUES
 (1000,'2011-12-07 07:00:00','2011-12-07 13:00:00')
,(1000,'2011-12-07 18:00:00','2011-12-07 19:00:00')

INSERT	@Exclude VALUES
 (1000,'2011-12-07 00:00:00','2011-12-07 07:40:00')
,(1000,'2011-12-07 08:00:00','2011-12-07 11:00:00')
,(1000,'2011-12-07 12:00:00','2011-12-07 18:30:00')
--------------------------------------------------------------------------------
;WITH Points AS (
	SELECT	 ID
		,[Date]
		,Tag		= Row_Number()OVER(PARTITION BY ID ORDER BY [Date])
	FROM	(		SELECT ID, [From]	FROM @Periods
		UNION ALL	SELECT ID, [From]	FROM @Exclude
		UNION ALL	SELECT ID, [To]		FROM @Periods
		UNION ALL	SELECT ID, [To]		FROM @Exclude
		) X (ID,[Date])
), Periods AS (
	SELECT	 B.ID
		,[From]	= B.[Date]
		,[To]	= E.[Date]
	FROM	     Points	B
		JOIN Points	E ON E.ID	= B.ID
				 AND E.Tag	= B.Tag + 1
)
SELECT	*
FROM	Periods	R
WHERE	Exists (
		SELECT	*
		FROM	@Periods	p
		WHERE	    P.ID = R.ID
			AND R.[From]	BETWEEN P.[From] AND P.[To]
			AND R.[To]	BETWEEN P.[From] AND P.[To]
	) AND NOT Exists (
		SELECT	*
		FROM	@Exclude		N
		WHERE	    N.ID = R.ID
			AND R.[From]	BETWEEN N.[From] AND N.[To]
			AND R.[To]	BETWEEN n.[From] AND N.[To]
	)
ORDER BY R.ID
	,R.[From]
По мне это принцип - смешали и разделили, как-то излишен. Да и какое-то злоупотребление оконными функциями.

Для вашего случая когда однотипные периоды не пересекаются (@Periods с @Periods и @Exclude с @Exclude), то можно упростить/оптимизировать запрос.
Могут встречаться 4 случая:
1. Один период перекрывающий начало (берём конец замест начала)
2. Один период перекрывающий конец (берём начало замест конца)
3. Один период перекрывающий весь период (исключаем полностью)
4. Несколько перекрываемых периодов (инвертируем концы)

DECLARE @Periods TABLE (
	 ID	Int
	,[From]	DateTime	-- 0, Включительно
	,[To]	DateTime	-- 1, Не включительно
)
DECLARE @Exclude TABLE (
	 ID	Int
	,[From]	DateTime	-- 0, Включительно
	,[To]	DateTime	-- 1, Не включительно
)
--------------------------------------------------------------------------------
INSERT	@Periods VALUES
 (1000,'2011-12-07 07:00:00','2011-12-07 13:00:00')
,(1000,'2011-12-07 18:00:00','2011-12-07 19:00:00')

INSERT	@Exclude VALUES
 (1000,'2011-12-07 00:00:00','2011-12-07 07:40:00')
,(1000,'2011-12-07 08:00:00','2011-12-07 11:00:00')
,(1000,'2011-12-07 12:00:00','2011-12-07 18:30:00')
--------------------------------------------------------------------------------
;WITH Periods (ID, [From], [To], Period) AS (
	SELECT	 P.ID
		,CASE R.[Type] WHEN 0 THEN R.[Date] END
		,CASE R.[Type] WHEN 1 THEN R.[Date] END
		,Row_Number()OVER(PARTITION BY P.ID ORDER BY R.[Date]) - 1 - R.[Type]
	FROM	@Periods	P	LEFT JOIN	@Exclude F ON F.ID = P.ID AND F.[From] <= P.[From] AND F.[To] >  P.[From]
					LEFT JOIN	@Exclude T ON T.ID = P.ID AND T.[From] <  P.[To]   AND T.[To] >= P.[To]
		CROSS APPLY (	SELECT	IsNull(F.[To],P.[From])	, 0	-- From
		UNION ALL	SELECT	IsNull(T.[From],T.[To])	, 1	-- To
		UNION ALL	SELECT	R.*	FROM	@Exclude I ON I.ID = P.ID AND I.[From] >  F.[From] AND I.[To] <  P.[To]
		CROSS APPLY (	SELECT	       I.[To]		, 0
		UNION ALL	SELECT	       I.[From]		, 1	) R([Date], [Type])
				)	R([Date]		, [Type])
	WHERE	NOT Exists (	SELECT	*	FROM	@Exclude E ON E.ID = P.ID AND E.[From] <= F.[From] AND E.[To] >= P.[To])
)
	SELECT	 P.ID
		,[From]	= Max(P.[From])
		,[To]	= Max(P.[To]  )
	FROM	Periods P
	GROUP BY P.ID
		,P.Period
11 дек 11, 01:10    [11743738]     Ответить | Цитировать Сообщить модератору
 Re: Пересечение периодов с отделением ненужных  [new]
flaky_n
Member

Откуда:
Сообщений: 3
Спасибо,
11 дек 11, 01:30    [11743814]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить