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

Откуда:
Сообщений: 79
Здравствуйте.
Произошел затуп в восстановлении дыр в последовательности дат. Прошу помощи у сообщества.

Есть таблица, которая выгружается из CRM с посещениями учреждений по адресам(фиас улицы) и датами этих посещений в виде 1-ого числа месяца.

declare @t as table (inn nvarchar(20), fias nvarchar(18),dom nvarchar(10),Qty integer, dm Date)

insert into @t (inn,fias,dom,Qty,dm) values (N'0000000063',N'020000010000000761',N'11',1,'20150101')
insert into @t (inn,fias,dom,Qty,dm) values (N'0000000063',N'020000010000000761',N'11',1,'20150301')
insert into @t (inn,fias,dom,Qty,dm) values (N'0000000063',N'020000010000000761',N'11',12,'20150401')
insert into @t (inn,fias,dom,Qty,dm) values (N'0000000063',N'020000010000000954',N'11',1,'20150101')
insert into @t (inn,fias,dom,Qty,dm) values (N'0000000063',N'020000010000000954',N'11',5,'20150901')
insert into @t (inn,fias,dom,Qty,dm) values (N'0000000063',N'020000010000000954',N'11',1,'20151201')


хочется расширить эту таблицу вставив строки с пропущенными последовательностью дат в разрезе инн-фиас-дом

Чтобы было видно, что, например учреждение с инн '0000000063',по адресу фиас '020000010000000761' дом 11 в феврале '20150201' - количество = 0

Есть табла календарь со всеми датами. Думал делать Cross Join но выходит куча записей и я не понимаю как их ограничить, чтобы получить желаемый результат.
заранее благодарю, если у кого будут какие мысли по этому поводу.
P.S. Поиск по "дыры в датах" именно на такой пример не выводят, а другие похожие приладить не выходит.
9 фев 16, 11:24    [18791811]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Durex_,

Календарь LEFT JOIN Таблица ON ...
9 фев 16, 11:26    [18791838]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Durex_
Думал делать Cross Join но выходит куча записей и я не понимаю как их ограничить, чтобы получить желаемый результат.

Сначала нужно выбрать уникальные комбинации inn-fias-dom
9 фев 16, 11:27    [18791842]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Durex_
Member

Откуда:
Сообщений: 79
Glory,
select * from calendar k cross join (select distinct t.inn, t.fias,t.dom  from @t t) di

да, получил "перемноженный" календарь на уникальные инн-дом-фиас, что бы теперь с этим множеством сделать...
9 фев 16, 11:46    [18792035]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Durex_
да, получил "перемноженный" календарь на уникальные инн-дом-фиас, что бы теперь с этим множеством сделать...

Соединять с таблицей разумеется
9 фев 16, 11:48    [18792052]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Как планируется получить строку с "количество = 0", не подскажете?
Откуда возьмётся строка, которой в таблице нет?
9 фев 16, 11:54    [18792095]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Durex_
Member

Откуда:
Сообщений: 79
iap,
похоже выходит, но все равно что-то не так

select distinct t1.inn, t1.fias,t1.dom,case when date_<>dm then 0 else t1.qty end,date_ from @t t1 left join
(select di.*,k.date_ from calendar k cross join (select distinct t.inn, t.fias,t.dom  from @t t) di where k.day_=1) fff on t1.inn = fff.inn and t1.fias = fff.fias and t1.dom=fff.dom and dm<=date_
order by 1,2,3,5
9 фев 16, 12:08    [18792218]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Durex_
но все равно что-то не так

потому что это к набору со всеми датами надо join-ить таблицу, а не наоборот
9 фев 16, 12:10    [18792234]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Durex_
Member

Откуда:
Сообщений: 79
Glory,Jap - Благодарю. Похоже, что нужный результат получен.

select kkk.inn,
	   kkk.fias,
	   kkk.dom,
	   ISNULL(t.Qty,0) as Qty,
	   kkk.date_ as dm
from
(select sss.inn,
        sss.fias,
		sss.dom,
		sss.date_
from
(select di.*,k.date_
  from calendar k cross join (select distinct t.inn,
							  t.fias,
							  t.dom
				      from @t t) di 
 where k.day_=1
   and year_=2015) sss left join Calendar kk on sss.date_ = kk.date_) kkk left join @t t on kkk.inn = t.inn and kkk.fias = t.fias and kkk.dom = t.dom and kkk.date_=t.dm
  order by 1,2,3,5
9 фев 16, 12:34    [18792410]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Durex_
Похоже, что нужный результат получен.

Зачем два раза Calendar то в запросе?
9 фев 16, 12:36    [18792425]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Durex_
Member

Откуда:
Сообщений: 79
как-то навороченно вышло, ну да ладно сейчас не до красоты, но уверен, что можно было и "покрасивше".
9 фев 16, 12:36    [18792429]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Durex_
Member

Откуда:
Сообщений: 79
Glory,
без него не работало ))))
9 фев 16, 12:37    [18792434]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Durex_
Member

Откуда:
Сообщений: 79
Glory, мне тоже как-то не нравится его повторное присутствие, но уж пусть будет 2 раза, чем неправильно. Вот такой вот г-код у меня вышел. )))
9 фев 16, 12:39    [18792459]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Мда

select kkk.inn,
	   kkk.fias,
	   kkk.dom,
	   ISNULL(t.Qty,0) as Qty,
	   kkk.date_ as dm
from
(select di.*,k.date_
  from calendar k cross join (select distinct t.inn,
							  t.fias,
							  t.dom
				      from @t t) di 
 where k.day_=1
   and year_=2015) kkk left join @t a on kkk.inn = a.inn and kkk.fias = a.fias and kkk.dom = a.dom and kkk.date_=a.dm
  order by 1,2,3,5
9 фев 16, 12:40    [18792472]     Ответить | Цитировать Сообщить модератору
 Re: Дырка в последовательности дат.  [new]
Durex_
Member

Откуда:
Сообщений: 79
Glory,
спасибо.
9 фев 16, 12:44    [18792501]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить