Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Расчет количества месяцев долга  [new]
GFrunze
Guest
Подскажите ,плз, как сделать
Select fio,igod,imonth,saldo,кол_мес_долга= ... from @tab
для след. скрипта :
declare @table table (fio varchar(10),igod int,imonth int,saldo int)        

-- долг месяцев

insert into @table values ('Ivanov',2003, 1,-50) -- 6

insert into @table values ('Ivanov',2003, 2,-40) -- 7

insert into @table values ('Ivanov',2003, 3,-50) -- 8

insert into @table values ('Ivanov',2002, 4,-70) -- 9

insert into @table values ('Ivanov',2002, 5,130) -- 0

insert into @table values ('Ivanov',2002, 6,240) -- 0

insert into @table values ('Ivanov',2002, 7,432) -- 0

insert into @table values ('Ivanov',2002, 8,-85) -- 1

insert into @table values ('Ivanov',2002, 9,-70) -- 2

insert into @table values ('Ivanov',2002,10,-30) -- 3

insert into @table values ('Ivanov',2002,11,-67) -- 4

insert into @table values ('Ivanov',2002,12,-54) -- 5


insert into @table values ('Petrov',2003, 1,-50) -- 3

insert into @table values ('Petrov',2003, 2,-30) -- 4

insert into @table values ('Petrov',2003, 3,430) -- 0

insert into @table values ('Petrov',2002, 4,840) -- 0

insert into @table values ('Petrov',2002, 5,-22) -- 1

insert into @table values ('Petrov',2002, 6,340) -- 0

insert into @table values ('Petrov',2002, 7,432) -- 0

insert into @table values ('Petrov',2002, 8, 85) -- 0

insert into @table values ('Petrov',2002, 9, 70) -- 0

insert into @table values ('Petrov',2002,10, 40) -- 0

insert into @table values ('Petrov',2002,11,-67) -- 1

insert into @table values ('Petrov',2002,12,-54) -- 2


insert into @table values ('Sidorov',2003, 1,-60) -- 2

insert into @table values ('Sidorov',2003, 2,240) -- 0

insert into @table values ('Sidorov',2003, 3, 70) -- 0

insert into @table values ('Sidorov',2002, 4,-40) -- 1

insert into @table values ('Sidorov',2002, 5,-26) -- 2

insert into @table values ('Sidorov',2002, 6,-85) -- 3

insert into @table values ('Sidorov',2002, 7,543) -- 0

insert into @table values ('Sidorov',2002, 8,324) -- 0

insert into @table values ('Sidorov',2002, 9,735) -- 0

insert into @table values ('Sidorov',2002,10,-78) -- 1

insert into @table values ('Sidorov',2002,11,664) -- 0

insert into @table values ('Sidorov',2002,12,-56) -- 1


Благодарю.
31 июл 03, 16:31    [283899]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
Думаю, можно так, или даже без q

declare

@dt datetime,
@y int,
@m int
select @dt = GetDate()
select
@y = DatePart(y, @dt),
@m = DatePart(m, @dt)

select
q.fio,
q.igod,
q.imonth,
q.saldo,
[кол_мес_долга]= case when q.blablabla <= 0 then q.blablalba else 0 end
from
(select
fio,
igod,
imonth,
saldo,
blablabla = (@y-igod) * 12 + @m-imonth
from @table) q
31 июл 03, 17:20    [284020]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
GFrunze
Guest
To 3JIA9I CyKA
Попробовал Ваш вариант
( с исправлением @y = DatePart(y, @dt) на @y = DatePart(, @dt)) и не получил ожидаемое (ожидаемые результаты приведены в комментариях скрипта).
Прошу Вас оттестировать на моем скрипте если Вас не затруднит.
Спасибо.
31 июл 03, 18:10    [284120]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
Luchkin Dmitry
Member

Откуда: Новосибирск -> Ангарск -> Братск -> Мск
Сообщений: 1921
очевидно-ж две неточности. 1. "yyyy"; 2. ">="

declare
@dt datetime,
@y int,
@m int

select @dt = GetDate()
select
@y = DatePart(yyyy, @dt),
@m = DatePart(mm, @dt)

select
q.fio,
q.igod,
q.imonth,
q.saldo,
[кол_мес_долга]= case when q.blablabla >= 0 then q.blablabla else 0 end
from
(select
fio,
igod,
imonth,
saldo,
blablabla = (@y-igod) * 12 + @m-imonth
from @table) q
1 авг 03, 07:38    [284401]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
GFrunze
Guest
Уважаемые,не пашет верно же.
Сравните ожидаемый и полученные результаты. Ведь количество месяцев долга
ну никак не может превышать 12.
1 авг 03, 09:05    [284477]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
Luchkin Dmitry
Member

Откуда: Новосибирск -> Ангарск -> Братск -> Мск
Сообщений: 1921
Ivanov 2002 4 -70 16

сейчас 2003 год 8 месяц. по сравнению с 4 месяцем 2002 года прошло больше года. а именно 16 месяцев.
ПОЧЕМУ ЖЕ не может быть больше 12 месяцев-то?!!
1 авг 03, 09:18    [284495]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
GFrunze
Guest
Данные в скрипте показаны по состоянию на конец марта 2003 года.
Хранятся и обновляются только за последние 12 месяцев.
Надо ответить на вопрос :
- сколько месяцев долга накопились до i-того месяца.
Пример для Ivanova:
- в июле 2002 года кол_мес_долга = 0 (сальдо положитедьное)
- в августе 2002 года кол_мес_долга = 1 (сальдо отрицательное)
........
- в декабре 2002 года кол_мес_долга = 5 (сальдо все отрицательное)
- в январе 2003 года кол_мес_долга = 6 (сальдо все отрицательное)
........
- в марте 2003 года кол_мес_долга = 8 (сальдо все отрицательное)
1 авг 03, 10:28    [284608]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
Glory
Member

Откуда:
Сообщений: 104760
Как-то так у меня получается.

declare @table table (fio varchar(10),igod int,imonth int,saldo int, 

idate as cast(cast(igod as char(4))+right('0'+cast(imonth as varchar(2)), 2)+'01' as datetime))
-- долг месяцев

insert into @table values ('Ivanov',2003, 1,-50) -- 6

insert into @table values ('Ivanov',2003, 2,-40) -- 7

insert into @table values ('Ivanov',2003, 3,-50) -- 8

insert into @table values ('Ivanov',2002, 4,-70) -- 9

insert into @table values ('Ivanov',2002, 5,130) -- 0

insert into @table values ('Ivanov',2002, 6,240) -- 0

insert into @table values ('Ivanov',2002, 7,432) -- 0

insert into @table values ('Ivanov',2002, 8,-85) -- 1

insert into @table values ('Ivanov',2002, 9,-70) -- 2

insert into @table values ('Ivanov',2002,10,-30) -- 3

insert into @table values ('Ivanov',2002,11,-67) -- 4

insert into @table values ('Ivanov',2002,12,-54) -- 5


insert into @table values ('Petrov',2003, 1,-50) -- 3

insert into @table values ('Petrov',2003, 2,-30) -- 4

insert into @table values ('Petrov',2003, 3,430) -- 0

insert into @table values ('Petrov',2002, 4,840) -- 0

insert into @table values ('Petrov',2002, 5,-22) -- 1

insert into @table values ('Petrov',2002, 6,340) -- 0

insert into @table values ('Petrov',2002, 7,432) -- 0

insert into @table values ('Petrov',2002, 8, 85) -- 0

insert into @table values ('Petrov',2002, 9, 70) -- 0

insert into @table values ('Petrov',2002,10, 40) -- 0

insert into @table values ('Petrov',2002,11,-67) -- 1

insert into @table values ('Petrov',2002,12,-54) -- 2


insert into @table values ('Sidorov',2003, 1,-60) -- 2

insert into @table values ('Sidorov',2003, 2,240) -- 0

insert into @table values ('Sidorov',2003, 3, 70) -- 0

insert into @table values ('Sidorov',2002, 4,-40) -- 1

insert into @table values ('Sidorov',2002, 5,-26) -- 2

insert into @table values ('Sidorov',2002, 6,-85) -- 3

insert into @table values ('Sidorov',2002, 7,543) -- 0

insert into @table values ('Sidorov',2002, 8,324) -- 0

insert into @table values ('Sidorov',2002, 9,735) -- 0

insert into @table values ('Sidorov',2002,10,-78) -- 1

insert into @table values ('Sidorov',2002,11,664) -- 0

insert into @table values ('Sidorov',2002,12,-56) -- 1


select case
when f1 is null and f2 is null and saldo > 0 then 0
when f1 is null and f2 is null and saldo < 0 then 1
when saldo < 0 and f2 > f1 then 1
when saldo < 0 and f2 is null then (select count(*) from @table b where b.fio = a.fio and b.idate <= a.idate)
when saldo < 0 and f1 is null then 1
when saldo < 0 and f2 < f1 then datediff(mm, f2, f1)+1
when saldo > 0 then 0
else 999
end, *
from(
select *,
(select top 1 idate from @table b where b.fio = a.fio and b.idate < a.idate and b.saldo < 0 order by b.idate desc) as f1,
(select top 1 idate from @table b where b.fio = a.fio and b.idate < a.idate and b.saldo > 0 order by b.idate desc) as f2
from @table a
) AS a


Если на реальных данных выскачят "999" тогда нужно рассматривать case. И вообще case наверное можно как-то оптимизировать.
1 авг 03, 10:30    [284611]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
GFrunze
Guest
To Glory
Спасибо, это то что надо, по крайней мере для данного примера.
Но уж очень наворочено, приходится чесать правое ухо левой пяткой.
И как это скажется еще на реальных данных, ведь это сильно упрощенный
вариант конкретной выборки со сложными вычислениями других полей.
Используется в системе расчета с абонентами за услуги связи где количество абонентов около полмиллиона, а количество записей местных и меж. разговоров превышает 100 млн. в месяц и все данные хранятся не менее года.
Hа процедурных языках это решается очень просто, а надо встраивать в stored
procedure.
Вот такие пироги...
1 авг 03, 11:22    [284720]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
Glory
Member

Откуда:
Сообщений: 104760
Ну так я сразу сказал, что case можно оптимизировать.
А идею собственно очень проста и находится во вложенном запросе - для каждой записи ищем меньшую дату с saldo < 0 и дату с saldo > 0. И дальше просто анализируем как эти две даты расположены друг относительно друга. Количество вариантов в case обусловлено еще и наличием NULL-ов.

ЗЫ
А вообще - это задача для триггера.
1 авг 03, 11:29    [284734]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
GFrunze
Guest
To all
Если есть еще идеи, прошу выставить.
Спасибо всем!
1 авг 03, 15:29    [285315]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
Некто
Member

Откуда: Киев
Сообщений: 312
Как вариант:
select t.* ,  max_positive_date = isnull ( max ( t1.idate ) , dateadd ( m , -1 ,  ( select min ( idate ) from @table ) ) )  , 

case when t.saldo > 0 then 0 else datediff ( m , isnull ( max ( t1.idate ) , dateadd ( m , -1 , ( select min ( idate ) from @table ) ) ) , t.idate ) end
from @table t
left outer join ( select * from @table where saldo > 0 ) t1
on t.fio = t1.fio and t.idate > t1.idate
group by t.saldo , t.fio , t.igod , t.imonth , t.idate
order by t.fio , t.igod desc , t.imonth
1 авг 03, 18:10    [285700]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
Некто
Member

Откуда: Киев
Сообщений: 312
А так, по-моему даже быстрее будет:
select t.* , 

max_positive_date = isnull ( ( select max ( idate ) from @table m where m.saldo > 0 and t.fio = m.fio and t.idate > m.idate ) ,
dateadd ( m , -1 , ( select min ( idate ) from @table ) ) ) ,
case when t.saldo > 0 then 0 else
datediff ( m , isnull ( ( select max ( idate ) from @table m where m.saldo > 0 and t.fio = m.fio and t.idate > m.idate ) ,
dateadd ( m , -1 , ( select min ( idate ) from @table ) ) ) , t.idate ) end
from @table t
order by t.fio , t.igod desc , t.imonth


На реальных объемах, нужно будет внимательно планы выполнения смотреть
1 авг 03, 18:46    [285757]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
GFrunze
Guest
To Некто
А что за колонка idate?
4 авг 03, 10:33    [286828]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
Некто
Member

Откуда: Киев
Сообщений: 312
2GFrunze
>А что за колонка idate?
declare @table table (fio varchar(10),igod int,imonth int,saldo int, 

idate as cast(cast(igod as char(4))+right('0'+cast(imonth as varchar(2)), 2)+'01' as datetime))

Я позволил себе использовать DDL Glory.
4 авг 03, 14:45    [287315]     Ответить | Цитировать Сообщить модератору
 Re: Расчет количества месяцев долга  [new]
GFrunze
Guest
Спасибо всем.
Все получается очень мудренно. Что-то надо попроще, трудно разбирать код,
особенно при создании динамического запроса, а без него мне не обойтись.
4 авг 03, 18:11    [287752]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить