Отправляет email-рассылки с помощью сервиса Sendsay

RFpro.ru: Базы данных MS SQL

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 176 RSS
←   Сентябрь 2013   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


За последние 60 дней ни разу не выходила


Сайт рассылки: http://rfpro.ru/issues/5/3/380
Открыта: 07-11-2009

Автор
Калашников О.А.

Статистика

176 подписчиков
0 за неделю
←   Все выпуски   →

RFpro.ru: Базы данных MS SQL


Хостинг портала RFpro.ru:
Московский хостер
Профессиональный ХОСТИНГ на базе Linux x64 и Windows x64

РАССЫЛКИ ПОРТАЛА RFPRO.RU

Лучшие эксперты по данной тематике

Коцюрбенко Алексей aka Жерар
Статус: Советник
Рейтинг: 3991
∙ повысить рейтинг » 		Megaloman
Статус: Академик
Рейтинг: 1788
∙ повысить рейтинг » 		Абаянцев Юрий Леонидович aka Ayl
Статус: Профессионал
Рейтинг: 1762
∙ повысить рейтинг »

/ КОМПЬЮТЕРЫ И СОФТ / Базы данных / MSSQL, Oracle, Interbase, Firebird, FoxPro

Номер выпуска:101
Дата выхода:13.09.2013, 11:00
Администратор рассылки:Филатов Евгений Геннадьевич (Профессионал)
Подписчиков / экспертов:39 / 19
Вопросов / ответов:1 / 1

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

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

Здравствуйте! У меня возникли сложности с таким вопросом:
Уважаемые эксперты, есть база на 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:20
Вопрос задал: Иванов Д.И. (Посетитель)
Всего ответов: 1
Страница онлайн-консультации »

Консультирует Филатов Евгений Геннадьевич (Профессионал):

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

Рабочая база в формате 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_stavk a,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_p roc, sum ( deb_day ) AS deb_period
FROM
( SELECT
acc as ac, date_n as dat, turncred as cred_sum,stavka as cred_stavka,iif ( turn cred>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 не проверял.

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

С уважением.

Консультировал: Филатов Евгений Геннадьевич (Профессионал)
Дата отправки: 12.09.2013, 15:29
Рейтинг ответа:

НЕ одобряю 0 одобряю!

Оценить выпуск | Задать вопрос экспертам

главная страница  |  стать участником  |  получить консультацию
техническая поддержка  |  восстановить логин/пароль

Дорогой читатель!
Команда портала RFPRO.RU благодарит Вас за то, что Вы пользуетесь нашими услугами. Вы только что прочли очередной выпуск рассылки. Мы старались. Пожалуйста, оцените его. Если совет помог Вам, если Вам понравился ответ, Вы можете поблагодарить автора - для этого в каждом ответе есть специальные ссылки. Вы можете оставить отзыв о работе портале. Нам очень важно знать Ваше мнение. Вы можете поближе познакомиться с жизнью портала, посетив наш форум, почитав журнал, который издают наши эксперты. Если у Вас есть желание помочь людям, поделиться своими знаниями, Вы можете зарегистрироваться экспертом. Заходите - у нас интересно!
МЫ РАБОТАЕМ ДЛЯ ВАС!

© 2001-2012, Портал RFPRO.RU, Россия
Авторское право: ООО "Мастер-Эксперт Про"
Калашников О.А. | Гладенюк А.Г.
Хостинг: Компания "Московский хостер"
Версия системы: 2011.6.36 от 26.01.2012
В избранное