Консультации Портала - Консультация #187532

Задать вопрос Консультации Пользователи Форум

Задать вопрос экспертам

Консультация № 187532

05.09.2013, 10:20

120.00 руб.

0 15 1

Компьютеры Программное обеспечение Базы данных

Здравствуйте! У меня возникли сложности с таким вопросом:
Уважаемые эксперты, есть база на MS Sql 2008
столкнулся с проблемой объединения 2 таблиц,возможно проблема пустяковая но не совсем понимаю как решить.
Есть 2 таблицы:
1таблица:
t1(напишу в русском эквиваленте)
счет
дата
остаток по кредиту
остаток по дебету
2 таблица:
счет
дата
сумма
ставка
1 задача была объеденить 2 таблицы,чтобы узнать у счета(1таблица) ставку(2 таблица) где остатки(1 табл.) равны сумме(2 таб.)
делал так:

Код:

select * from t1 a left join t2 b on a.acc=b.acc where (a.kred_ost=b.summa or a.debet_summa=b.summa)

с этим понятно, но нужно выбрать те записи которые отвечают след.условиям:
чтобы понятно было поясню, 2 таблица - набор данных, где у счетов по разным суммам в разные дни могут быть разные ставки, точнее это ставки фондированияу клиента, которые могут меняться по счету много раз в некотором периоде.
Первая таблица - это набор данных остатков по счетам
что то совсем не пойму как лучше написать
Например возьмем пример:
в первой таблице есть счет(кредитный)
45208%1 у которого были обороты с января по июль 2013 года
во второй таблице хранятся ставки фондирования с суммами
т.е. так(во второй таблице)
счет - дата - сумма_фондирования - ставка

45208%1 - 10.02.2013 - 150000 - 13%
45208%1 - 15.02.2013 - 1478000 - 10%
45208%1 ...................
...................................
...................................
1) Нужно объеденить первую таблицу со второй, чтобы можно было вытянуть ставку, но это в принципе как я приводил селектом
но + к этому нужно взять оставшиеся записи из первой таблице(данные которые не нашли себе пары из второй)
2) Все это нужно для подсчета расхода по клиенту
не пойму как расчитать:
формула такова:
(среднедневной осаток * ставку фондирования)*кол-во дней /365
у счета за период менялась ставка несколько раз
среднедневной = сумме остатков за период / количество дней в этом периоде.
среднедневной у меня есть, допустим есть ставка(которая описана в первом вопросе),как считать?

Обсуждение

Неизвестный

05.09.2013, 10:37

общий

Адресаты:

а можно для примера несколько записей из обеих таблиц? с разными датами и ставками.

давно

Иванов Д.И.

Посетитель
396751
37

05.09.2013, 11:11

общий

Конечно можно, только полностью счета не могу указать
первая(t1) таблица, где обороты по счетам
поля
acc
restrub
turncred
turndeb
date_n

вторая (t2)
stavka
acc
summa_fond
date_n

t1
45204%1 - 70000000.00 - 0.00 - 63242424.00 - 2013-07-02
45204%1 - 16750000.00 - 0.00 - 16750000.00 - 2013-06-02
45401%0 - 1300000.00 - 0.00 - 1300000.00 - 0.00 - 2013-07-01
45201%2 - 1547800.00 - 154878.00 - 0.00 - 2013-07-07
.........
........
.......

t2
0.085 - 45204%1 - 63242424.00 - 2013-07-02
0.075 - 45204%1 - 16750000.00 - 2013-06-02

Неизвестный

05.09.2013, 15:57

общий

Адресаты:

как я понял, считать нужно только те записи, по которым есть данные во второй таблице. для этого почти полностью подойдет запрос, приведенный в вопросе
select * from t1 a inner join t2 b on a.acc=b.acc and a.date_n=b.date_n where (a.kred_ost=b.summa or a.debet_summa=b.summa)

возможно, при этом нужно различать, по какому виду платежа производится соединение - по дебету или кредиту. т.е. для дебета это процент выплаты клиенту, а по кредиту процент удержания за пользование кредитом. для этого данные сохраним в таблице с дополнительными полями
select a1.acc as ac, a1.date_n as dat, a1.turncred as cred_sum,b1.stavka as cred_stavka,1 as cred_day, 0 as deb_sum,0 as deb_stavka,0 as deb_day
from t1 a1 inner join t2 b1 on a1.acc=b1.acc and a1.dat=b1.dat and a1.turncred=b1.summa_fond
union
select a2.acc as ac, a2.date_n as dat, 0 as cred_sum,0 as cred_stavka,0 as cred_day, a2.turndeb as deb_sum,b2.stavka as deb_stavka,1 as deb_day
from t1 a2 inner join t2 b2 on a2.acc=b2.acc and a2.dat=b2.dat and a2.turncred=b2.summa_fond

в итоге получили результирующую таблицу по каждому клиенту за каждый учтенный день из второй таблицы с указанием суммы и процента отдельно по кредиту и дебету. затем можно подбить итого по каждому клиенту за определенный период
select ac, sum(cred_sum*cred_stavka) as cred_proc, sum(cred_day) as cred_period, sum(deb_sum*deb_stavka) as deb_proc, sum(deb_day) as deb_period
from ( здесь вставить предыдущее объединение таблиц )
where dat>... and dat<...
group by ac

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

что еще сюда присоединять?

давно

Иванов Д.И.

Посетитель
396751
37

06.09.2013, 14:51

общий

Спасибо за ответ.
Оказалось не совсем то что нужно...
Данные из первой таблицы(обороты по дебету,кредиту) иногда могут не совпадать с данными по фондированию клиентов из второй таблицы.
Был предложен вариант брать ставку фондирования из второй таблицы следующим образом:
Если данные есть то хорошо, вариант либо тот который я привел, либо Ваш, если нет совпадений, то нужно анализировать по дате. Т.е. есть первая таблица:
счет, дата, обороты
Мы берем счет и эту дату если она входит в диапазон из второй таблицы то берем ставку,если нет то вообще ничего не выводим.
Думал таким образом:
На самом деле есть хранимая процедура, которая принимает 2 параметра, начальная дата и конечная в расчетном периоде
доупустим:

Код:


declare @d1 date='20130101'
declare @d2 date='20130331'


select * from t1 a
join
t2 b on t2.date between @d1 and @d2

P.S. дальше не знаю, или сразу иду не в ту сторону размышлений

Неизвестный

06.09.2013, 15:24

общий

Адресаты:

если нет данных во второй таблице с нужной датой, то можно предположить, что процент не поменялся и нужно брать предыдущее значение. попробую сделать чуть позже.

давно

Иванов Д.И.

Посетитель
396751
37

08.09.2013, 01:10

общий

Если можно то хотелось бы добавить.
Допустим есть в первой таблице счета с остатками,возьмем счет и месяц например март,в нём 31 день,т.е. должно быть 31 движение(остаток) в первой таблице для выбранного счета(так и для остальных, так как если даже не было движения по счету,остаток тянется с последней проводки).Теперь вернемся к таблице со ставками, допустим по выбранному счету в марте была установлена ставка,например 13 марта,допустим была добавлена ещё одна ставка 20 числа,так вот:
если я объединяю таблицы, то у меня в результате появляется 2 записи со ставками. а нужно чтобы оставались остальные 29 проводок и ставка, до 13 бралась предыдущая по данному счету, с 13 по 20 та которая установлена 13 и с 20 по 31 та ставка которая была установлена 20

Неизвестный

10.09.2013, 11:15

общий

10.09.2013, 11:16

Адресаты:

проблема с отбором ставки с периодом, максимально приближенному к текущей дате, не проблема.
создал таблицы и заполнил содержимым
t1
acc restrub turncred turndeb date_n
45208%1 0 0 50000 06.02.13
45208%1 0 0 70000 07.02.13
45208%1 0 0 30000 08.02.13
45208%1 0 0 90000 09.02.13
45208%1 0 0 40000 10.02.13
45208%1 0 0 25000 11.02.13

t2
stavka acc summa_fond date_n
13 45208%1 150000 01.02.13
10 45208%1 1478000 08.02.13
12 45208%1 500000 10.02.13

запрос
SELECT *,(select top 1 t2.stavka from t2 where t2.acc=t1.acc and t2.date_n<=t1.date_n order by t2.date_n desc) as stav
FROM t1;

выдает результат
stav acc restrub turncred turndeb date_n
13 45208%1 0 0 50000 06.02.13
13 45208%1 0 0 70000 07.02.13
10 45208%1 0 0 30000 08.02.13
10 45208%1 0 0 90000 09.02.13
12 45208%1 0 0 40000 10.02.13
12 45208%1 0 0 25000 11.02.13

ставка берется правильно. в дальнейшем с этими данными можно работать, как писал в предыдущем сообщении. основное условие - в таблице t2 обязательно должна присутствовать запись с минимальной датой ( можно 1.1.2013 )

Неизвестный

11.09.2013, 17:17

общий

Адресаты:

завтра составлю ответ.

хотелось бы еще немного поуточнять, но можно и на основе имеющихся ответов что-то сделать.

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

давно

Иванов Д.И.

Посетитель
396751
37

12.09.2013, 08:39

общий

Спасибо большое Вам за участие.
Знаете не совсем понимаю следующее, как считать расход по клиенту?
я ранее писал формулу:
(среднедневной остаток * ставку фондирования)*кол-во дней в расчетном периоде /365
Например мы хотим просчитать расход по каждому счету(по клиенту). Возьмем расчетный период 2 квартал, за это время у счета несколько раз менялась ставка, как подсчитать расход?

Неизвестный

12.09.2013, 09:29

общий

Адресаты:

дело в том, что запрос
SELECT *,(select top 1 t2.stavka from t2 where t2.acc=t1.acc and t2.date_n<=t1.date_n order by t2.date_n desc) as stav
FROM t1;
возвращает построчно за каждый день, какая была процентная ставка и остаток. поэтому можно посчитать их произведение. а дальше подсчитать количество дней, как описано в сообщении 3 этого мини-форума.

меня интересует другой вопрос - почему ставка всего одна? в теории для дебета и кредита должны быть разные значения. и можно не привязываться к полю summa_fond таблицы t2. а среднедневной остаток брать из полей turncred и turndeb таблицы t1.

подожду часа три. потом буду составлять ответ.

давно

Иванов Д.И.

Посетитель
396751
37

12.09.2013, 10:12

общий

ставка фондирования устанавливается на сумму.
т.е. у клиента есть кредитная линия, в ней транши, т.е. на каждый транш(=на каждую сумму) устанавливается своя процентная ставка, это то что касается таблицы 2.
в таблице 1, это таблица с оборотами и проводками по каждому счету,где было изменения баланса по счетам.
т.е.
счет/дата проводки/остаток / оборот по дебету / оборот по кредиту
чтобы более менее идентифицировать то что данная ставка относится к такой то проводке, сравниваю и осатки/обороты по дебету/кредиту, если совпадение предполагаю что ставка равна в данный момент данной ставке.
Как и раньше описывал, если жестко привязываться к оборотам по дебету/кредиту - появляется большая вероятность потери данных, поэтому привязываться стал к дате, где дата из первой таблицы = из второй,либо находится около неё.
както так

Неизвестный

12.09.2013, 11:08

общий

Адресаты:

данные могут потеряться, только если во второй таблице нет записи с датой, меньшей или равной дате из первой таблицы. а такого быть не должно. при создании нового клиента необходимо добавлять во вторую таблицу начальные данные по процентам.

что по поводу разных ставок для дебета или кредита? это вполне можно реализовать.

не совсем понимаю, для чего во второй таблице поле summa_fond. ведь данные о транше ( по дебету или кредиту ) берется из соответствующих полей первой таблицы.

подходят данные
stav acc restrub turncred turndeb date_n
13 45208%1 0 0 50000 06.02.13

отсюда можно узнать turncred *stav или turndeb *stav за каждый день, в котором был транш.

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

( SELECT
acc as ac, date_n as dat, turncred as cred_sum,stavka as cred_stavka,iif ( turncred>0,1,0 ) as cred_day,turndeb as deb_sum,stavka as deb_stavka,iif ( turndeb>0,1,0 ) as deb_day
from ( SELECT *, ( select top 1 t2.stavka from t2 where t2.acc=t1.acc and t2.date_n<=t1.date_n order by t2.date_n desc ) as stavka FROM t1 )
)

в результате имеем таблицу
ac dat cred_sum cred_stavka cred_day deb_sum deb_stavka deb_day
45208%1 06.02.13 0 13 0 50000 13 1
45208%1 07.02.13 0 13 0 70000 13 1
45208%1 08.02.13 0 10 0 30000 10 1
45208%1 09.02.13 0 10 0 90000 10 1
45208%1 10.02.13 0 12 0 40000 12 1
45208%1 11.02.13 0 12 0 25000 12 1

функцию iif ( turncred>0,1,0 ) , которая работает в MS Access, необходимо заменить соответствующей ей в другой версии T-SQL , например Case для MS SQL Server.

далее можно расчитать формулы
select ac, sum(cred_sum*cred_stavka) as cred_proc, sum(cred_day) as cred_period, sum(deb_sum*deb_stavka) as deb_proc, sum(deb_day) as deb_period
from
( предыдущий запрос )
where dat>=cdate('7.2.2013') and dat<=cdate('10.2.2013')
group by ac

в результате имеем таблицу
ac cred_proc cred_period deb_proc deb_period
45208%1 0 0 2590000 4

функцию cdate заменить, например, на Format или Convert.

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

Неизвестный

12.09.2013, 11:21

общий

Адресаты:

немного подправил данные в таблицах

acc restrub turncred turndeb date_n
45208%1 0 0 50000 02.02.13
45208%1 0 0 70000 07.02.13
45208%1 0 0 30000 08.02.13
45208%1 0 0 90000 09.02.13
45208%1 0 0 40000 12.02.13
45208%1 0 0 25000 21.02.13

stavka acc summa_fond date_n
13 45208%1 150000 01.02.13
10 45208%1 1478000 08.02.13
12 45208%1 500000 10.02.13

результирующий запрос
SELECT ac, sum ( cred_sum*cred_stavka ) AS cred_proc, sum ( cred_day ) AS cred_period, sum ( deb_sum*deb_stavka ) AS deb_proc, sum ( deb_day ) AS deb_period
FROM
( SELECT
acc as ac, date_n as dat, turncred as cred_sum,stavka as cred_stavka,iif ( turncred>0,1,0 ) as cred_day,turndeb as deb_sum,stavka as deb_stavka,iif ( turndeb>0,1,0 ) as deb_day
from
( SELECT *, ( select top 1 t2.stavka from t2 where t2.acc=t1.acc and t2.date_n<=t1.date_n order by t2.date_n desc ) as stavka FROM t1 where date_n>=cdate ( '1.2.2013' ) and date_n<=cdate ( '28.2.2013' ) )
)
GROUP BY ac

результат
ac cred_proc cred_period deb_proc deb_period
45208%1 0 0 3540000 6

в итоге нам остается 3540000*6/28 и получим то, что нужно для ответа на вопрос.

сброшу еще саму базу в MS Access 2000. перевод в MS SQL Server не делал, но там не очень сложно.

этот запрос можно сделать как пользовательскую функцию, в которую напрямую передавать значения начального и конечного периода дат.

Неизвестный

12.09.2013, 11:23

общий

Адресаты:

ой. забыл, что проценты нужно на 100 еще делить. 3540000*6/28/100

Неизвестный

12.09.2013, 15:29

общий

это ответ

Здравствуйте, Иванов Д.И.!

Рабочая база в формате MS Access, в которой проверены запросы, находится в База для консультации 187532

Небольшое описание таблиц
Таблица t1
acc
restrub
turncred
turndeb
date_n

Таблица t2
acc
summa_fond
date_n
stavka

При создании нового клиента необходимо добавлять во вторую таблицу начальные данные по процентам с датой меньше или равной дате добавления клиента.
Возможно, было бы лучше хранить два значения процентных ставок – отдельно для дебета и кредита. Это потребует незначительной переделки запросов.

Решение создано для MS Access 2000, но переход на MS SQL Server не займет много времени.

Для начала разобьем решение задачи на несколько этапов.

1. Получение суммы транша и процентной ставки на момент даты транша

SELECT *, ( select top 1 t2.stavka from t2 where t2.acc=t1.acc and t2.date_n<=t1.date_n order by t2.date_n desc ) as stavka FROM t1 where date_n>=cdate ( '1.2.2013' ) and date_n<=cdate ( '28.2.2013' )

Результатом будет таблица
acc restrub turncred turndeb date_n stavka
45208%1 0 0 50000 02.02.13 13
45208%1 0 0 70000 07.02.13 13
45208%1 0 0 30000 08.02.13 10
45208%1 0 0 90000 09.02.13 10
45208%1 0 0 40000 12.02.13 12
45208%1 0 0 25000 21.02.13 12

В которую выводятся все записи, входящие в диапазон дат, заданных условием. Процентная ставка берется из второй таблицы для даты, меньше или равной дате записи в первой таблице

2. Определение дней, в котором производились транши с привязкой к процентной ставке и виду операции – дебет или кредит. Также добавляется 1 день к счетчику дней, в которые производился транш за заданный период.

SELECT
acc as ac, date_n as dat, turncred as cred_sum,stavka as cred_stavka,iif ( turncred>0,1,0 ) as cred_day,turndeb as deb_sum,stavka as deb_stavka,iif ( turndeb>0,1,0 ) as deb_day
from
( запрос 1 )

В результате получим таблицу
ac dat cred_sum cred_stavka cred_day deb_sum deb_stavka deb_day
45208%1 02.02.13 0 13 0 50000 13 1
45208%1 07.02.13 0 13 0 70000 13 1
45208%1 08.02.13 0 10 0 30000 10 1
45208%1 09.02.13 0 10 0 90000 10 1
45208%1 12.02.13 0 12 0 40000 12 1
45208%1 21.02.13 0 12 0 25000 12 1

Перемножая сумму транша по дебету или кредиту с процентной ставкой получаем какое-то значение, которое используем в дальнейших расчетах. Также добавляем 1 день в счетчик дней, когда использовался транш.

3. Определяем среднее значение за период. При этом необходимо знать, сколько дней в этом периоде ( для февраля 2013 это 28 дней ) .

SELECT ac, sum ( cred_sum*cred_stavka ) AS cred_proc, sum ( cred_day ) AS cred_period, sum ( deb_sum*deb_stavka ) AS deb_proc, sum ( deb_day ) AS deb_period
FROM
( запрос 2 )
GROUP BY ac

В результате получим таблицу
ac cred_proc cred_period deb_proc deb_period
45208%1 0 0 3540000 6

Осталось рассчитать среднее значение по формуле
cred_proc * cred_period / 100 / 28 и
deb_proc * deb_period / 100 / 28

Проще это организовать пользовательской функцией в MS SQL Server с передачей значений начального и конечного периода с обязательной проверкой, чтобы конечный период был не меньше начального. Или добавить проверку в функцию с выводом пустой таблицы при ошибках.

GO
CREATE FUNCTION dbo.MyFunc
( @BegDat smalldatetime, @EndDat smalldatetime )
RETURNS TABLE
AS
RETURN (

SELECT ac, (cred_proc * cred_period / 100 / datediff ( ‘d’, @BegDat, dateadd ( 'd' , 1, @EndDat ) ) ) as cred_transh, (deb_proc * deb_period / 100 / datediff ( ‘d’, @BegDat, dateadd ( 'd' , 1, @EndDat ) ) ) as deb_transh
From (
SELECT ac, sum ( cred_sum*cred_stavka ) AS cred_proc, sum ( cred_day ) AS cred_period, sum ( deb_sum*deb_stavka ) AS deb_proc, sum ( deb_day ) AS deb_period
FROM
( SELECT
acc as ac, date_n as dat, turncred as cred_sum,stavka as cred_stavka,iif ( turncred>0,1,0 ) as cred_day,turndeb as deb_sum,stavka as deb_stavka,iif ( turndeb>0,1,0 ) as deb_day
from
( SELECT *, ( select top 1 t2.stavka from t2 where t2.acc=t1.acc and t2.date_n<=t1.date_n order by t2.date_n desc ) as stavka FROM t1 where date_n>=@BegDat and date_n<=@EndDat )
)
GROUP BY ac
) )

В MS Access получился результат
ac cred_transh deb_transh
45208%1 0 7585.71428571429

Так как в MS SQL Server 2012 уже существует функция IIF , то переделывать не нужно. Для остальных версий сервера необходимо заменить
Iif (turncred >0,1,0 ) на CASE turncred >0 THEN 0 ELSE 1 END
Iif ( turndeb>0,1,0 ) на CASE turndeb>0 THEN 0 ELSE 1 END

Вызов пользовательской функции осущевляется так
SELECT *
FROM dbo.MyFunc ( convert ( smalldatetime, ‘2/1/2013’,101 ) , convert ( smalldatetime, ‘2/28/2013’,101 ) )

Работоспособность в MS SQL Server не проверял.

Если будут еще вопросы – отвечу в мини-форуме.

С уважением.

Форма ответа

Отправка постов/ответов доступна только зарегистрированным и подтвержденным пользователям.

Если Вы уже зарегистрированы на Портале - войдите в систему, если Вы еще не регистрировались - пройдите простую процедуру регистрации.