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

Откуда: Екатеринбург
Сообщений: 71
Есть таблица (типа регистра сведений) сальдо по договору-счету (на 1 договор несколько счетов)
set dateformat dmy
create table ostatok(
dogovor int
,dt datetime
, account_id int
, esld decimal(15,2)
);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'15.05.2015',1,3000);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'15.06.2015',1,6000);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'15.07.2015',1,9000);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'27.07.2015',1,0);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'15.08.2015',1,3000);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'15.09.2015',1,6000);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'02.10.2015',1,1000);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'15.06.2015',2,300);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'15.07.2015',2,600);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'27.07.2015',2,0);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'15.08.2015',2,300);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'15.09.2015',2,600);
insert into ostatok(dogovor,dt, account_id, esld)
values(1,'02.10.2015',2,0);

Мне необходимо найти период существования кредитового сальдо по всему договору.
Отдельно по каждому из счетов внутри договора я такие периоды нашел, а как для всего договора - какой-то ступор у меня
Для приведенных данных периоды существования сальдо
для счета 1 с 15.05.2015 по 26.07.2015, с 15.08.2015 по сегодня
для счета 2 с 15.06.2015 по 26.07.2015 и с 15.08.2015 по 01.10.2015
я их так нашёл:
with c as
(
select * from ostatok
where esld <> 0
), c0 as
(
select * from ostatok
where esld = 0
)
, c3 as
(
select c.*, c0.dt as d0
from c
left
join c0 on c.dt < c0.dt and c.account_id = c0.account_id
)
--, c4 as
--(
select dogovor, account_id, min(dt) d1 , coalesce(d0, '20300101') d2
from c3
group by dogovor, account_id, d0
order by 1, 2, 3

d2 - конец периода, - на самом деле нужно на день уменьшить, но не суть
Для всего договора должно выйти
с 15.05.2015 по 26.07.2015 и с 15.08.2015 по сегодня
Но что-то тут я встрял, бьюсь как рыба об стол :C
19 апр 17, 09:06    [20413157]     Ответить | Цитировать Сообщить модератору
 Re: Не получается запросом определить периоды существования кредитового сальдо  [new]
aleks2
Guest
1. Так ищут тока чудаки на букву..
2. Я так подозреваю, что, для начала, надо сказать словами "каковы признаки существования кредитового сальдо по всему договору?".
3. Если это условие "кредитовое сальдо по всему договору" = "кредитовое сальдо по каждому счету отдельно" => просто пересечение периодов.
4. Если это условие "кредитовое сальдо по всему договору" = "кредитовое сальдо по сумме счетов" => еще проще - как для счета.
19 апр 17, 09:53    [20413298]     Ответить | Цитировать Сообщить модератору
 Re: Не получается запросом определить периоды существования кредитового сальдо  [new]
мегамозг
Member

Откуда: Екатеринбург
Сообщений: 71
aleks2
2. Я так подозреваю, что, для начала, надо сказать словами "каковы признаки существования кредитового сальдо по всему договору?".

Исходя из того, что правильный ответ
ТС
Для всего договора должно выйти
с 15.05.2015 по 26.07.2015 и с 15.08.2015 по сегодня

признак существования сальдо по договору - существование сальдо по любому из счетов внутри этого договора.
19 апр 17, 10:58    [20413540]     Ответить | Цитировать Сообщить модератору
 Re: Не получается запросом определить периоды существования кредитового сальдо  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
мегамозг,

используйте аналитические функции или самообъединение. Тогда найдете "перепады" меджу текущей и предыдущей строкой по условиям сортировки..
19 апр 17, 11:18    [20413610]     Ответить | Цитировать Сообщить модератору
 Re: Не получается запросом определить периоды существования кредитового сальдо  [new]
мегамозг
Member

Откуда: Екатеринбург
Сообщений: 71
Владислав Колосов
мегамозг,

используйте аналитические функции или самообъединение. Тогда найдете "перепады" меджу текущей и предыдущей строкой по условиям сортировки..

Я ч-з самообъединение сделал, правильно?

--drop table ostatok2
select dogovor, dt, sum(esld) esld
into ostatok2
from ostatok
group by dogovor, dt

with c as
(
select * from ostatok2
where esld <> 0
), c0 as
(
select * from ostatok2
where esld = 0
)
, c3 as
(
select c.*, c0.dt as d0
from c
left
join c0 on c.dt < c0.dt --and c.account_id = c0.account_id
)
--, 
--(
select dogovor,  min(dt) d1 , coalesce(d0, '20300101') d2
from c3
group by dogovor, d0
order by  2, 3

А как можно было по-другому?
19 апр 17, 12:33    [20413971]     Ответить | Цитировать Сообщить модератору
 Re: Не получается запросом определить периоды существования кредитового сальдо  [new]
aleks2
Guest
мегамозг
aleks2
2. Я так подозреваю, что, для начала, надо сказать словами "каковы признаки существования кредитового сальдо по всему договору?".

Исходя из того, что правильный ответ
ТС
Для всего договора должно выйти
с 15.05.2015 по 26.07.2015 и с 15.08.2015 по сегодня

признак существования сальдо по договору - существование сальдо по любому из счетов внутри этого договора.


Ну, дык, объединение периодов кредитового сальдо всех счетов договора.
19 апр 17, 12:54    [20414082]     Ответить | Цитировать Сообщить модератору
 Re: Не получается запросом определить периоды существования кредитового сальдо  [new]
мегамозг
Member

Откуда: Екатеринбург
Сообщений: 71
Переписал запрос. Какой вар-т более оптимальный, этот или через самосоединения?
with c as
(
select * 
, case esld when 0 then 1 else 0 end isZero
, lead(dt) over(PARTITION by dogovor order by dt) next_dt
, sum (case esld when 0 then 1 else 0 end) over(PARTITION by dogovor order by dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rng
from ostatok2
)
select min(dt), max (coalesce(next_dt, '20300101'))
from c
where iszero <> 1
group by rng 
20 апр 17, 15:24    [20419183]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить