Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 rolling count by date periods  [new]
Igor0000
Guest 		vsem privet.

est' (dannie mogut bit' drugimi, eto prosto primer)

select * from
(values
	(1, '1/1/2004', '3/1/2004', 3),
	(1, '1/1/2004', '3/1/2004', 3),
	(1, '1/1/2004', '3/1/2004', 3),
	(1, '1/1/2004', '6/1/2004', 6),
	(1, '1/1/2004', '6/1/2004', 6),
	(1, '1/1/2004', '6/1/2004', 6),
	(1, '7/1/2004', '9/1/2004', 3),
	(1, '7/1/2004', '9/1/2004', 3),
	(1, '7/1/2004', '9/1/2004', 3),
	(1, '1/1/2004', '12/1/2004', 12),
	(1, '1/1/2004', '12/1/2004', 12),
	(1, '1/1/2004', '12/1/2004', 12),
	(2, '1/1/2005', '3/1/2005', 3),
	(2, '1/1/2005', '3/1/2005', 3),
	(2, '1/1/2005', '3/1/2005', 3),
	(2, '1/1/2005', '6/1/2005', 6),
	(2, '1/1/2005', '6/1/2005', 6),
	(2, '1/1/2005', '6/1/2005', 6),
	(2, '1/1/2005', '9/1/2005', 3),
	(2, '1/1/2005', '9/1/2005', 3),
	(2, '1/1/2005', '9/1/2005', 3),
	(2, '1/1/2005', '12/1/2005', 12),
	(2, '1/1/2005', '12/1/2005', 12),
	(2, '1/1/2005', '12/1/2005', 12)

) t (id, begdt, enddt, nm)


dla zapisey s odinakovim id nado naity summarniy count strok predidushih periodov sortiruja po"nm". esli begdt, enddt ne vhodit v diapazon sleduushego nm, to ego v count dobavlat ne nado. dolgno puluchitsa

1 1/1/2004 3/1/2004 3 0
1 1/1/2004 3/1/2004 3 0
1 1/1/2004 3/1/2004 3 0
1 1/1/2004 6/1/2004 6 3
1 1/1/2004 6/1/2004 6 3
1 1/1/2004 6/1/2004 6 3
1 7/1/2004 9/1/2004 3 0
1 7/1/2004 9/1/2004 3 0
1 7/1/2004 9/1/2004 3 0
1 1/1/2004 12/1/2004 12 9
1 1/1/2004 12/1/2004 12 9
1 1/1/2004 12/1/2004 12 9
2 1/1/2005 3/1/2005 3 0
2 1/1/2005 3/1/2005 3 0
2 1/1/2005 3/1/2005 3 0
2 1/1/2005 6/1/2005 6 6
2 1/1/2005 6/1/2005 6 6
2 1/1/2005 6/1/2005 6 6
2 1/1/2005 9/1/2005 3 0
2 1/1/2005 9/1/2005 3 0
2 1/1/2005 9/1/2005 3 0
2 1/1/2005 12/1/2005 12 9
2 1/1/2005 12/1/2005 12 9
2 1/1/2005 12/1/2005 12 9


spasibo
21 дек 17, 18:33    [21053205]     Ответить | Цитировать Сообщить модератору
 Re: rolling count by date periods  [new]
Igor0000
Guest 		Sorry, ploho pereneslos' iz excel. date format yyyy-mm-dd

	select * from
	(values
		(1, '2004-01-01', '2004-03-01', 3),
		(1, '2004-01-01', '2004-03-01', 3),
		(1, '2004-01-01', '2004-03-01', 3),
		(1, '2004-01-01', '2004-06-01', 6),
		(1, '2004-01-01', '2004-06-01', 6),
		(1, '2004-01-01', '2004-06-01', 6),
		(1, '2004-07-01', '2004-09-01', 3),
		(1, '2004-07-01', '2004-09-01', 3),
		(1, '2004-07-01', '2004-09-01', 3),
		(1, '2004-01-01', '2004-12-01', 12),
		(1, '2004-01-01', '2004-12-01', 12),
		(1, '2004-01-01', '2004-12-01', 12),
		(2, '2004-01-01', '2004-03-01', 3),
		(2, '2004-01-01', '2004-03-01', 3),
		(2, '2004-01-01', '2004-03-01', 3),
		(2, '2004-01-01', '2004-06-01', 6),
		(2, '2004-01-01', '2004-06-01', 6),
		(2, '2004-01-01', '2004-06-01', 6),
		(2, '2004-01-01', '2004-09-01', 3),
		(2, '2004-01-01', '2004-09-01', 3),
		(2, '2004-01-01', '2004-09-01', 3),
		(2, '2004-01-01', '2004-12-01', 12),
		(2, '2004-01-01', '2004-12-01', 12),
		(2, '2004-01-01', '2004-12-01', 12)
    
	) t (id, begdt, enddt, nm)
21 дек 17, 18:45    [21053235]     Ответить | Цитировать Сообщить модератору
 Re: rolling count by date periods  [new]
Remind
Member

Откуда: UK
Сообщений: 523
 		Igor0000,

Сформулируйте условие более точно. А то у вас тут периоды пересекаются, очень много пограничных случаев может быть.
21 дек 17, 20:14    [21053563]     Ответить | Цитировать Сообщить модератору
 Re: rolling count by date periods  [new]
Remind
Member

Откуда: UK
Сообщений: 523
 
with cte as
(
select * from
	(values
		(1, '2004-01-01', '2004-03-01', 3),
		(1, '2004-01-01', '2004-03-01', 3),
		(1, '2004-01-01', '2004-03-01', 3),
		(1, '2004-01-01', '2004-06-01', 6),
		(1, '2004-01-01', '2004-06-01', 6),
		(1, '2004-01-01', '2004-06-01', 6),
		(1, '2004-07-01', '2004-09-01', 3),
		(1, '2004-07-01', '2004-09-01', 3),
		(1, '2004-07-01', '2004-09-01', 3),
		(1, '2004-01-01', '2004-12-01', 12),
		(1, '2004-01-01', '2004-12-01', 12),
		(1, '2004-01-01', '2004-12-01', 12),
		(2, '2004-01-01', '2004-03-01', 3),
		(2, '2004-01-01', '2004-03-01', 3),
		(2, '2004-01-01', '2004-03-01', 3),
		(2, '2004-01-01', '2004-06-01', 6),
		(2, '2004-01-01', '2004-06-01', 6),
		(2, '2004-01-01', '2004-06-01', 6),
		(2, '2004-01-01', '2004-09-01', 3),
		(2, '2004-01-01', '2004-09-01', 3),
		(2, '2004-01-01', '2004-09-01', 3),
		(2, '2004-01-01', '2004-12-01', 12),
		(2, '2004-01-01', '2004-12-01', 12),
		(2, '2004-01-01', '2004-12-01', 12)
    
	) t (id, begdt, enddt, nm)
)
select *, (select count(*) from cte c2 where c2.id = c.id and c.begdt <= c2.enddt and c.enddt >= c2.begdt and c2.nm < c.nm)
from cte c
21 дек 17, 20:34    [21053614]     Ответить | Цитировать Сообщить модератору
 Re: rolling count by date periods  [new]
Igor0000
Guest 		Spasibo za otvet,

moget, mojno bez subquery sdelat'? cherez count() over()?
21 дек 17, 21:14    [21053714]     Ответить | Цитировать Сообщить модератору
 Re: rolling count by date periods  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
Igor0000
Spasibo za otvet,

moget, mojno bez subquery sdelat'? cherez count() over()?

Можно, если будет уникальный ключ в исходной таблице.
21 дек 17, 21:26    [21053743]     Ответить | Цитировать Сообщить модератору
 Re: rolling count by date periods  [new]
Igor0000
Guest 
	select * from
	(values
		(1, '2004-01-01', '2004-01-01', '2004-03-01', 3),
		(1, '2004-02-01', '2004-01-01', '2004-03-01', 3),
		(1, '2004-03-01', '2004-01-01', '2004-03-01', 3),
		(1, '2004-04-01', '2004-01-01', '2004-06-01', 6),
		(1, '2004-05-01', '2004-01-01', '2004-06-01', 6),
		(1, '2004-06-01', '2004-01-01', '2004-06-01', 6),
		(1, '2004-07-01', '2004-07-01', '2004-09-01', 3),
		(1, '2004-08-01', '2004-07-01', '2004-09-01', 3),
		(1, '2004-09-01', '2004-07-01', '2004-09-01', 3),
		(1, '2004-10-01', '2004-01-01', '2004-12-01', 12),
		(1, '2004-11-01', '2004-01-01', '2004-12-01', 12),
		(1, '2004-12-01', '2004-01-01', '2004-12-01', 12),
		(2, '2005-01-01', '2005-01-01', '2005-03-01', 3),
		(2, '2005-02-01', '2005-01-01', '2005-03-01', 3),
		(2, '2005-03-01', '2005-01-01', '2005-03-01', 3),
		(2, '2005-04-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-05-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-06-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-07-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-08-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-09-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-10-01', '2005-10-01', '2005-12-01', 3),
		(2, '2005-11-01', '2005-10-01', '2005-12-01', 3),
		(2, '2005-12-01', '2005-10-01', '2005-12-01', 3)
    
	) t (id, dt, begdt, enddt, nm)


Unique key is dt
21 дек 17, 21:28    [21053754]     Ответить | Цитировать Сообщить модератору
 Re: rolling count by date periods  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
 		Igor0000,

Если я правильно понял задачу, то так:

IF OBJECT_ID('tempdb..#periods') IS NOT NULL DROP TABLE #periods
CREATE TABLE #periods (
  id    int,
  dt    datetime,
  begdt datetime,
  enddt datetime,
  nm    int
)
INSERT #periods (id, dt, begdt, enddt, nm) VALUES
        (1, '2004-01-01', '2004-01-01', '2004-03-01', 3),
		(1, '2004-02-01', '2004-01-01', '2004-03-01', 3),
		(1, '2004-03-01', '2004-01-01', '2004-03-01', 3),
		(1, '2004-04-01', '2004-01-01', '2004-06-01', 6),
		(1, '2004-05-01', '2004-01-01', '2004-06-01', 6),
		(1, '2004-06-01', '2004-01-01', '2004-06-01', 6),
		(1, '2004-07-01', '2004-07-01', '2004-09-01', 3),
		(1, '2004-08-01', '2004-07-01', '2004-09-01', 3),
		(1, '2004-09-01', '2004-07-01', '2004-09-01', 3),
		(1, '2004-10-01', '2004-01-01', '2004-12-01', 12),
		(1, '2004-11-01', '2004-01-01', '2004-12-01', 12),
		(1, '2004-12-01', '2004-01-01', '2004-12-01', 12),
		(2, '2005-01-01', '2005-01-01', '2005-03-01', 3),
		(2, '2005-02-01', '2005-01-01', '2005-03-01', 3),
		(2, '2005-03-01', '2005-01-01', '2005-03-01', 3),
		(2, '2005-04-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-05-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-06-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-07-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-08-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-09-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-10-01', '2005-10-01', '2005-12-01', 3),
		(2, '2005-11-01', '2005-10-01', '2005-12-01', 3),
		(2, '2005-12-01', '2005-10-01', '2005-12-01', 3)
SELECT P.id, P.begdt, P.enddt, P.nm, ISNULL(COUNT(C.id),0) AS cnt
FROM #periods P
LEFT JOIN #periods C ON C.id=P.id AND C.enddt<P.enddt
  AND C.begdt BETWEEN P.begdt AND P.enddt AND C.nm<P.nm
GROUP BY P.dt, P.id, P.begdt, P.enddt, P.nm
ORDER BY P.id, P.enddt, P.begdt, P.nm
21 дек 17, 21:39    [21053779]     Ответить | Цитировать Сообщить модератору
 Re: rolling count by date periods  [new]
Remind
Member

Откуда: UK
Сообщений: 523
 		Igor0000,

Скорее всего нет, вам нужно проверять условие пересечения периодов
21 дек 17, 21:41    [21053785]     Ответить | Цитировать Сообщить модератору
 Re: rolling count by date periods  [new]
Igor0000
Guest 		Mojno

with cte as
(
	select * from
	(values
		(1, '2004-01-01', '2004-01-01', '2004-03-01', 3),
		(1, '2004-02-01', '2004-01-01', '2004-03-01', 3),
		(1, '2004-03-01', '2004-01-01', '2004-03-01', 3),
		(1, '2004-04-01', '2004-01-01', '2004-06-01', 6),
		(1, '2004-05-01', '2004-01-01', '2004-06-01', 6),
		(1, '2004-06-01', '2004-01-01', '2004-06-01', 6),
		(1, '2004-07-01', '2004-07-01', '2004-09-01', 3),
		(1, '2004-08-01', '2004-07-01', '2004-09-01', 3),
		(1, '2004-09-01', '2004-07-01', '2004-09-01', 3),
		(1, '2004-10-01', '2004-01-01', '2004-12-01', 12),
		(1, '2004-11-01', '2004-01-01', '2004-12-01', 12),
		(1, '2004-12-01', '2004-01-01', '2004-12-01', 12),
		(2, '2005-01-01', '2005-01-01', '2005-03-01', 3),
		(2, '2005-02-01', '2005-01-01', '2005-03-01', 3),
		(2, '2005-03-01', '2005-01-01', '2005-03-01', 3),
		(2, '2005-04-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-05-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-06-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-07-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-08-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-09-01', '2005-01-01', '2005-12-01', 12),
		(2, '2005-10-01', '2005-10-01', '2005-12-01', 3),
		(2, '2005-11-01', '2005-10-01', '2005-12-01', 3),
		(2, '2005-12-01', '2005-10-01', '2005-12-01', 3),
		(3, '2006-01-01', '2006-01-01', '2006-03-01', 3),
		(3, '2006-02-01', '2006-01-01', '2006-03-01', 3),
		(3, '2006-03-01', '2006-01-01', '2006-03-01', 3),
		(3, '2006-04-01', '2006-03-01', '2006-06-01', 3),
		(3, '2006-05-01', '2006-03-01', '2006-06-01', 3),
		(3, '2006-06-01', '2006-03-01', '2006-06-01', 3),
		(3, '2006-07-01', '2006-01-01', '2006-09-01', 9),
		(3, '2006-08-01', '2006-01-01', '2006-09-01', 9),
		(3, '2006-09-01', '2006-01-01', '2006-09-01', 9),
		(3, '2006-10-01', '2006-01-01', '2006-12-01', 12),
		(3, '2006-11-01', '2006-01-01', '2006-12-01', 12),
		(3, '2006-12-01', '2006-01-01', '2006-12-01', 12)
    
	) t (id, dt, begdt, enddt, nm)

) 

select 
	id, dt, begdt, enddt, nm,
	sum(EC) over(partition by ID, SubGroupID) ExistingCount
from 
( --b
	select *,  
		case when SubGroupID = 1 or SubGroupID = lag(SubGroupID, 1, NULL) over(partition by id order by dt)
			 then 0 else lag(SubEndDTGroupIDCnt, 1, NULL) over(partition by id order by SubEndDTGroupIDCnt) end EC
	from 
	( --a
		select *,
			dense_rank() over (partition by ID order by nm) SubGroupID,
			row_number() over (partition by ID order by enddt, dt) SubEndDTGroupIDCnt
		from cte
	) a
) b
order by id, dt
22 дек 17, 01:21    [21054086]     Ответить | Цитировать Сообщить модератору
 Re: rolling count by date periods  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 888
Igor0000
Mojno

Вообще-то тут два вложенных подзапроса.
22 дек 17, 01:59    [21054114]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить