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

Откуда:
Сообщений: 360
Есть таблица Tab
CREATE TABLE #tab(id INT IDENTITY(1,1),NAME VARCHAR(5),kol_doc INT,wdate DATETIME)
INSERT INTO #tab (NAME,kol_doc,wdate)
SELECT 'ACN',100,'2011-01-01'
UNION SELECT 'ACN',100,'2011-01-05'
UNION SELECT 'ACN',100,'2011-01-08'
UNION SELECT 'ACN',120,'2011-01-10'
UNION SELECT 'ACN',100,'2011-01-15'
UNION SELECT 'ACN',80,'2011-01-16'
UNION SELECT 'ACN',300,'2011-01-25'
UNION SELECT 'ACN',100,'2011-01-27'
UNION SELECT 'BCN',300,'2011-01-01'
UNION SELECT 'BCN',300,'2011-01-02'
UNION SELECT 'BCN',300,'2011-01-03'
UNION SELECT 'BCN',300,'2011-01-04'
UNION SELECT 'BCN',300,'2011-01-05'
UNION SELECT 'BCN',300,'2011-01-06'
UNION SELECT 'BCN',280,'2011-01-07'
UNION SELECT 'BCN',300,'2011-01-10'

Нужно получить
namekol_docbdateedate
ACN1002011-01-012011-01-09
ACN1202011-01-102011-01-14
ACN1002011-01-152011-01-15
ACN802011-01-162011-01-24
ACN3002011-01-252011-01-26
ACN1002011-01-272011-09-12
BCN3002011-01-012011-01-06
BCN2802011-01-072011-01-09
BCN3002011-01-10'2011-09-12


Т.е. нужно определить периоды по полям name и kol_doc
12 сен 11, 12:26    [11263180]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
el_sh,

А алгоритм-то какой? Или вопрос в том, что его угадать надо?
12 сен 11, 12:32    [11263220]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
s.w.a.n.
Member

Откуда: Москва
Сообщений: 137
el_sh,
а поподробнее нельзя расписать, что надо получить? В edate, что там за странные даты? Что за периоды вы хотите получить?
12 сен 11, 12:34    [11263235]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
el_sh
Member

Откуда:
Сообщений: 360
SomewhereSomehow,

Name - система, kol_doc - контрольное число,wdate-дата актуальности
Нужно получить период актуальности контрольного числа по каждой системе.
Период вычисляется по изменению wdate и kol_doc по каждому Name, начала периода берется как wdate, окончание dateadd(d,-1,wdate) изменения wdate и kol_doc . Т.е. в нашем случае система ACN в период с 01/01/2011 по 09/01/2011 имела одно контрольное число документов -100,с 15/01/2011 по 15/01/2011 имела контрольное сисло -120, с 16/01/2011 по 24/01/2011 - 80, с 25/01/11 по 26/11/11 - 300, с 27/01/11 по текущую дату - 100 и т.д.
12 сен 11, 12:43    [11263291]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
el_sh
Member

Откуда:
Сообщений: 360
Пожалуйста, если не понятно написала, скажите! Попробую объеянить иначе, только помогите! Всю голову сломала!
12 сен 11, 13:47    [11263768]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
el_sh,

вот, навскидку, посмотрите только внимательнее, результат совпадает, но дальше не проверял
declare @tab TABLE (id INT IDENTITY(1,1),NAME VARCHAR(5),kol_doc INT,wdate DATETIME)
INSERT INTO @tab (NAME,kol_doc,wdate)
SELECT 'ACN',100,'2011-01-01'
UNION all SELECT 'ACN',100,'2011-01-05'
UNION all SELECT 'ACN',100,'2011-01-08'
UNION all SELECT 'ACN',120,'2011-01-10'
UNION all SELECT 'ACN',100,'2011-01-15'
UNION all SELECT 'ACN',80,'2011-01-16'
UNION all SELECT 'ACN',300,'2011-01-25'
UNION all SELECT 'ACN',100,'2011-01-27'
UNION all SELECT 'BCN',300,'2011-01-01'
UNION all SELECT 'BCN',300,'2011-01-02'
UNION all SELECT 'BCN',300,'2011-01-03'
UNION all SELECT 'BCN',300,'2011-01-04'
UNION all SELECT 'BCN',300,'2011-01-05'
UNION all SELECT 'BCN',300,'2011-01-06'
UNION all SELECT 'BCN',280,'2011-01-07'
UNION all SELECT 'BCN',300,'2011-01-10'

select 
	t1.id,
	t1.kol_doc,
	t1.NAME,
	t1.wdate,
	wdateend = isnull(dateadd(dd,-1,t3.wdate),'20110912')
from 
	@tab t1
	outer apply (select top 1 wdate, kol_doc from @tab t2 where t1.NAME = t2.NAME and t2.wdate > t1.wdate order by wdate) t3
where
	t1.kol_doc <> t3.kol_doc or t3.kol_doc is null
и еще насчет магической даты 2011-09-12 тоже непонятно, так что я просто ее оставил =)
12 сен 11, 14:00    [11263865]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
el_sh
Member

Откуда:
Сообщений: 360
SomewhereSomehow,

Спасибо, сейчас посмотрю. Насчет магической даты 12/09/2011 - это getdate() :)
12 сен 11, 14:07    [11263924]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
el_sh
Member

Откуда:
Сообщений: 360
SomewhereSomehow,

Не получилось, неправильно определились первые периоды по обеим системам
12 сен 11, 14:11    [11263953]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
el_sh,

тогда давайте данные, на которых неправильно и пример правильного результата.
12 сен 11, 14:24    [11264054]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
el_sh
Member

Откуда:
Сообщений: 360
SomewhereSomehow,

Первый период по системе ACN с 01/01/2011 по 09/01/2011, по вашему коду первый период 08/01/2011 по 09/01/2011,
по BCN первый период с 01/01/2011 по 06/01/2011 у вас с 06/01/2011 по 06/01/2011

Данные и желаемый результат в первом топике.
12 сен 11, 14:30    [11264091]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
s.w.a.n.
Member

Откуда: Москва
Сообщений: 137
el_sh,
может такой вариант
select 	t1.NAME,t1.kol_doc,
	min(t1.wdate) as edate,
	wdateend = isnull(dateadd(dd,-1,t3.wdate),getdate())
from 
	#tab t1
	outer apply (select top 1 wdate, kol_doc from #tab t2 where t1.NAME = t2.NAME and t2.wdate > t1.wdate and t1.kol_doc <> t2.kol_doc order by wdate) t3
group by t1.kol_doc,t1.NAME, t3.wdate
order by t1.name, edate
12 сен 11, 14:40    [11264165]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
el_sh,

Теперь понял о чем вы, действительно, начало интервала не учитывалось, в таком случае модифицируйте запрос, уже предложили как 11264165.
12 сен 11, 14:47    [11264223]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
el_sh
Member

Откуда:
Сообщений: 360
SomewhereSomehow,s.w.a.n. спасибо, проверила на своих данных - получилось
12 сен 11, 15:03    [11264324]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
SamMan
Member

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

USE tempdb
go
DROP TABLE #tab
go
CREATE TABLE #tab(id INT IDENTITY(1,1),NAME VARCHAR(5),kol_doc INT,wdate DATETIME)
INSERT INTO #tab (NAME,kol_doc,wdate)
SELECT 'ACN',100,'2011-01-01'
UNION SELECT 'ACN',100,'2011-01-05'
UNION SELECT 'ACN',100,'2011-01-08'
UNION SELECT 'ACN',120,'2011-01-10'
UNION SELECT 'ACN',100,'2011-01-15'
UNION SELECT 'ACN',80,'2011-01-16'
UNION SELECT 'ACN',300,'2011-01-25'
UNION SELECT 'ACN',100,'2011-01-27'
UNION SELECT 'BCN',300,'2011-01-01'
UNION SELECT 'BCN',300,'2011-01-02'
UNION SELECT 'BCN',300,'2011-01-03'
UNION SELECT 'BCN',300,'2011-01-04'
UNION SELECT 'BCN',300,'2011-01-05'
UNION SELECT 'BCN',300,'2011-01-06'
UNION SELECT 'BCN',280,'2011-01-07'
UNION SELECT 'BCN',300,'2011-01-10'

;With CTE as
(
	select bname AS n,bkol AS k,bd AS d1,ed AS d2,Row_Number() OVER (Order by bname,bd,ed) AS rn
	FROM (
	Select name AS bname,kol_doc AS bkol,wdate AS bd from #tab
	) AS start LEFT JOIN (
	Select name AS ename,kol_doc AS ekol,DATEADD(dd,-1,wdate) AS ed from #tab
	) AS finish ON start.bname=finish.ename AND start.bkol!=finish.ekol AND bd<=ed
)
SELECT Cur.n,Cur.k,Cur.d1,ISNULL(Cur.d2,GETDATE()) AS d2 FROM CTE AS Cur Left Outer Join CTE AS Prv On Cur.rn=Prv.rn+1
WHERE (Cur.n!=Prv.n) OR (Cur.k!=Prv.k) OR (Prv.n IS NULL)
ORDER BY Cur.n,Cur.d1,Cur.d2
/*
Result:
=======
n	k	d1	d2
ACN	100	2011-01-01 00:00:00.000	2011-01-09 00:00:00.000
ACN	120	2011-01-10 00:00:00.000	2011-01-14 00:00:00.000
ACN	100	2011-01-15 00:00:00.000	2011-01-15 00:00:00.000
ACN	80	2011-01-16 00:00:00.000	2011-01-24 00:00:00.000
ACN	300	2011-01-25 00:00:00.000	2011-01-26 00:00:00.000
ACN	100	2011-01-27 00:00:00.000	2011-09-12 15:17:39.090
BCN	300	2011-01-01 00:00:00.000	2011-01-06 00:00:00.000
BCN	280	2011-01-07 00:00:00.000	2011-01-09 00:00:00.000
BCN	300	2011-01-10 00:00:00.000	2011-09-12 15:17:39.090
*/
12 сен 11, 15:19    [11264433]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
el_sh
Member

Откуда:
Сообщений: 360
SamMan, спасибо
12 сен 11, 15:33    [11264533]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
Добрый Э - Эх
Guest
Вроде, такого варианта ещё не предлагали:
select name, kol_doc as doc_name, min(wdate) as bdate, max(wdate) as edate
  from (
         select name, kol_doc, wdate, 
                row_number() over(partition by name, kol_doc order by wdate) - 
                row_number() over(partition by name order by wdate) as grp_id
           from tab
          order by name, wdate
       ) v
 group by name, kol_doc, grp_id
 order by name, min(wdate)
13 сен 11, 06:53    [11266887]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
Добрый Э - Эх
Guest
Сортировочка лишняя и табличку не так обозвал...
Добрый Э - Эх
Вроде, такого варианта ещё не предлагали:
select name, kol_doc as doc_name, min(wdate) as bdate, max(wdate) as edate
  from (
         select name, kol_doc, wdate, 
                row_number() over(partition by name, kol_doc order by wdate) - 
                row_number() over(partition by name order by wdate) as grp_id
           from #tab
          order by name, wdate
       ) v
 group by name, kol_doc, grp_id
 order by name, min(wdate)


З.Ы.
Сервера под рукой нет, запрос не проверял...
13 сен 11, 06:57    [11266889]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
el_sh
Member

Откуда:
Сообщений: 360
Добрый Э - Эх,

Спасибо, мне понравилось
13 сен 11, 11:11    [11267794]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
el_sh
Member

Откуда:
Сообщений: 360
Добрый Э - Эх,

Увы-увы, проверила дополнительно и вышла ошибочка. Если изменить данные вот так:

CREATE TABLE #tab(id INT IDENTITY(1,1),NAME VARCHAR(5),kol_doc INT,wdate DATETIME)
INSERT INTO #tab (NAME,kol_doc,wdate)
SELECT 'ACN',100,'2011-01-01'
UNION SELECT 'ACN',100,'2011-01-05'
UNION SELECT 'ACN',100,'2011-01-08'
UNION SELECT 'ACN',120,'2011-01-10'
UNION SELECT 'ACN',100,'2011-01-15'
UNION SELECT 'ACN',80,'2011-01-16'
UNION SELECT 'ACN',80,'2011-01-17'
UNION SELECT 'ACN',80,'2011-01-18'
UNION SELECT 'ACN',80,'2011-01-19'
UNION SELECT 'ACN',300,'2011-01-25'
UNION SELECT 'ACN',100,'2011-01-27'
UNION SELECT 'BCN',300,'2011-01-01'
UNION SELECT 'BCN',300,'2011-01-02'
UNION SELECT 'BCN',300,'2011-01-03'
UNION SELECT 'BCN',300,'2011-01-04'
UNION SELECT 'BCN',300,'2011-01-05'
UNION SELECT 'BCN',300,'2011-01-06'
UNION SELECT 'BCN',280,'2011-01-07'
UNION SELECT 'BCN',300,'2011-01-10'

то результат получается ошибочный

(строк обработано: 19)
name  doc_name    bdate                   edate
----- ----------- ----------------------- -----------------------
ACN   100         2011-01-01 00:00:00.000 2011-01-08 00:00:00.000
ACN   120         2011-01-10 00:00:00.000 2011-01-10 00:00:00.000
ACN   100         2011-01-15 00:00:00.000 2011-01-15 00:00:00.000
ACN   80          2011-01-16 00:00:00.000 2011-01-19 00:00:00.000
ACN   300         2011-01-25 00:00:00.000 2011-01-25 00:00:00.000
ACN   100         2011-01-27 00:00:00.000 2011-01-27 00:00:00.000
BCN   300         2011-01-01 00:00:00.000 2011-01-06 00:00:00.000
BCN   280         2011-01-07 00:00:00.000 2011-01-07 00:00:00.000
BCN   300         2011-01-10 00:00:00.000 2011-01-10 00:00:00.000
(строк обработано: 9)
13 сен 11, 11:21    [11267860]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
iljy
Member

Откуда:
Сообщений: 8711
el_sh,

USE tempdb
go
if object_id('tempdb..#tab') is not null DROP TABLE #tab
go
CREATE TABLE #tab(id INT IDENTITY(1,1),NAME VARCHAR(5),kol_doc INT,wdate DATETIME)
INSERT INTO #tab (NAME,kol_doc,wdate)
SELECT 'ACN',100,'2011-01-01'
UNION SELECT 'ACN',100,'2011-01-05'
UNION SELECT 'ACN',100,'2011-01-08'
UNION SELECT 'ACN',120,'2011-01-10'
UNION SELECT 'ACN',100,'2011-01-15'
UNION SELECT 'ACN',80,'2011-01-16'
UNION SELECT 'ACN',300,'2011-01-25'
UNION SELECT 'ACN',100,'2011-01-27'
UNION SELECT 'BCN',300,'2011-01-01'
UNION SELECT 'BCN',300,'2011-01-02'
UNION SELECT 'BCN',300,'2011-01-03'
UNION SELECT 'BCN',300,'2011-01-04'
UNION SELECT 'BCN',300,'2011-01-05'
UNION SELECT 'BCN',300,'2011-01-06'
UNION SELECT 'BCN',280,'2011-01-07'
UNION SELECT 'BCN',300,'2011-01-10'

;with cte as(
	select *, ROW_NUMBER() over(partition by NAME order by wdate) RN from #tab
)
select NAME, MAX(kol_doc) kol_doc, MIN(wdate1) d_begin, dateadd(d, -1, MAX(wdate2)) d_end
from(
	select t1.NAME, t1.kol_doc, t1.wdate wdate1, case t1.kol_doc when t2.kol_doc then NULL else t2.wdate end wdate2,
		   t1.RN - ROW_NUMBER() over(partition by t1.NAME, t1.kol_doc order by t1.RN) grp
	from cte t1 left join cte t2 on t1.NAME = t2.NAME and t2.RN = t1.RN+1
)t
group by NAME, grp
order by NAME, d_begin
13 сен 11, 11:54    [11268147]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Дико извиняюсь, но "классический" вариант (для Version <= 2000 и без TOP 1 ORDER BY):
SELECT t.NAME,t.kol_doc,t.wdate [bdate],
 (
  SELECT tt.wdate
  FROM #tab tt
  WHERE tt.NAME=t.NAME AND tt.kol_doc=t.kol_doc AND tt.wdate>=t.wdate
    AND NOT EXISTS
    (
     SELECT *
     FROM #tab ttt
     WHERE ttt.NAME=tt.NAME AND ttt.kol_doc<>tt.kol_doc AND ttt.wdate>t.wdate AND ttt.wdate<tt.wdate
    )
    AND NOT EXISTS
    (
     SELECT *
     FROM #tab ttt
     WHERE ttt.NAME=tt.NAME AND ttt.kol_doc=tt.kol_doc AND ttt.wdate>tt.wdate
       AND NOT EXISTS
       (
        SELECT *
        FROM #tab tttt
        WHERE tttt.NAME=ttt.NAME AND tttt.kol_doc<>ttt.kol_doc AND tttt.wdate>tt.wdate AND tttt.wdate<ttt.wdate
       )
    )
 ) [edate]
FROM #tab t
WHERE NOT EXISTS
(
 SELECT *
 FROM #tab tt
 WHERE tt.NAME=t.NAME AND tt.kol_doc=t.kol_doc AND tt.wdate<t.wdate
   AND NOT EXISTS
   (
    SELECT *
    FROM #tab ttt
    WHERE ttt.NAME=tt.NAME AND ttt.kol_doc<>tt.kol_doc AND ttt.wdate<t.wdate AND ttt.wdate>tt.wdate
   )
)
ORDER BY t.NAME,t.wdate;


С TOP 1 коррелированный подзапрос получился бы попроще
13 сен 11, 12:05    [11268245]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
iljy
Member

Откуда:
Сообщений: 8711
el_sh,

а можно извратиться и сделать за один проход по таблице
select NAME, MAX(kol_doc) kol_doc, MIN(d1) d_begin, MAX(d2) d_end
from(
	select NAME, max(case x when 0 then kol_doc end) kol_doc,
			MIN(wdate) d1, DATEADD(d,-1,MAX(case x when 1 then wdate end)) d2,
			gr - ROW_NUMBER() over(partition by NAME, max(case x when 0 then kol_doc end) order by gr) gr
	from(
		select *, RN-ROW_NUMBER() over(partition by NAME,x order by wdate) gr
		from(
			select *, ROW_NUMBER() over(partition by NAME order by wdate, x desc) RN
			from #tab t cross join (select 0 union all select 1) tt(x)
		)t
	)t
	group by NAME, gr
	having SUM(1-x) > 0
)t
group by NAME, gr
order by NAME, d_begin
13 сен 11, 12:26    [11268443]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
iljy
и сделать за один проход по таблице


А что это (факт одного прохода) даст? Или так... "чиста" академический интерес? :)
13 сен 11, 13:25    [11268933]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
iljy
Member

Откуда:
Сообщений: 8711
SamMan
iljy
и сделать за один проход по таблице


А что это (факт одного прохода) даст? Или так... "чиста" академический интерес? :)

Конкретно здесь - ничего. А так - может давать выигрыш в производительности.
13 сен 11, 13:46    [11269139]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не могу сообразить как построить период по двум столбцам  [new]
el_sh
Member

Откуда:
Сообщений: 360
iljy
SamMan
пропущено...


А что это (факт одного прохода) даст? Или так... "чиста" академический интерес? :)

Конкретно здесь - ничего. А так - может давать выигрыш в производительности.


iljy, спасибо, прирост производительности по сравнению с первым используемым кодом от SomewhereSomehow на реальных данных 5 раз.
Несколько запоздало, но благодарю всех, кто откликнулся!
28 окт 11, 12:08    [11514793]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить