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

Откуда:
Сообщений: 168
Есть таблица Вклады, в которой хранится информация о сумме вклада, о типе валюты, о сроке вклада в месяцах и о типе клиента:
физическое лицо - fiz
юридическое лицо - jur
иностранец - for
NAME SUMMA VALUTA RATA DATA DURATION TYPE_CLIENT
A 10000 RUR 11 01.03.2012 24 fiz
B 20000 EUR 15 24.05.2012 12 jur
R 5000 USA 20 12.05.2012 36 fiz
H 25000 RUR 13 05.03.2012 24 jur
YY 10000 EUR 12 01.02.2012 12 for
WW 6000 RUR 18 04.06.05 24 fiz
DD 10000 EUR 11 01.06.2012 12 jur


Необходимо сделать отчет по вкладам (за определенный период времени) в виде таблицы

номер строки ---------------------------- меньше 1 года...............| меньше 2 лет...................| более 2 лет...................... .....................................................

номер строки -- средний процент сумма средний процент сумма| средний процент сумма| общая сумма
1 Всего вкладов из которых:
2 В национальной валюте:
3 юридические лица
4 физические лица
5 иностранцы
6 В иностранной валюте:
7 юридические лица
8 физические лица
9 иностранцы


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


в строках 3, 4, 5 и 7, 8, 9, я считаю средний процент по вкладам и общую сумму вкладов в зависимости от срока вклада и типа валюты
строка 2 = сумма строк 3, 4, 5
строка 6 = сумма строк 7, 8, 9

строка 1 = сумма строк 2 и 6
17 июл 12, 14:35    [12877984]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Len4ick
Member

Откуда:
Сообщений: 168
попробовала что-то набросать но что дальше с этим делать не знаю
declare
@BEG_DATE as date = '20120701'
,@END_DATE as date = '20120108'


-- Raport BNM anexa 1 (saptaminal)
with query1 as (
  select VALUTA
        ,case 
           when DURATION<=12 then '< 1 year'
           when DURATION<= then '< 2 year'
           else '> 2 years'
         end as perioada
  ,SUM(SUMMA) as suma 
  ,RATA
  from Tab1
  where cast(DATA as DATE) between @BEG_DATE and @END_DATE
  group by VALUTA
        ,CASE 
             WHEN DURATION<=12 then '< 1 year'
             when DURATION<= then '< 2 year'
             else '> 2 years'
         end
) 
17 июл 12, 14:37    [12877989]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
_ч_
Member

Откуда:
Сообщений: 1447
Len4ick,

У вас в первом сообщении не очень понятная таблица, вы привели пример входных данных, приведите пример выходных.

И еще вопрос, а как это вы будете считать сумму в иностранной валюте (а уж тем более общую сумму по вкладам), вы же ее не можете суммировать без конвертации в какую-нибудь другую валюту. Да и каким образом считается средний процент? Средний процент чего именно?
17 июл 12, 15:33    [12878381]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Len4ick
Member

Откуда:
Сообщений: 168
да, с валютой я поспешила
добавляю в исходную таблицу еще один столбец SM_LOC, в котором будет хранится сумма вклада в национальной валюте

SM_LOC
10000
800000
160000
25000
400000
6000
10000
17 июл 12, 15:46    [12878499]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Len4ick
Member

Откуда:
Сообщений: 168
И и весь отчет соответственно будет в рублях
17 июл 12, 15:48    [12878514]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
_ч_
Member

Откуда:
Сообщений: 1447
Len4ick,

а что на счет таблицы, в которой будут храниться результаты запросов? Мне трудно понять из первого поста какого вида должен быть результат запроса.
17 июл 12, 15:55    [12878592]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Len4ick
Member

Откуда:
Сообщений: 168
тут довольно сложная формула для вычисления процентов, попробую объяснить наглядно

NAME SUMMA VALUTA RATA DATA DURATION TYPE_CLIENT SM_LOC
B 20000 EUR 15 24.05.2012 12 jur 800000
YY 10000 EUR 12 01.02.2012 12 for 400000
DD 10000 EUR 11 01.06.2012 12 jur 10000
A 10000 RUR 11 01.03.2012 24 fiz 10000
H 25000 RUR 13 05.03.2012 24 jur 25000
WW 6000 RUR 18 04.06.2005 24 fiz 6000
R 5000 USA 20 12.05.2012 36 fiz 160000
FF 7000 USA 17 12.07.2012 36 fiz 42000


возьме вклады в рублях
NAME SUMMA VALUTA RATA DATA DURATION TYPE_CLIENT SM_LOC
A 10000 RUR 11 01.03.2012 24 fiz 10000
H 25000 RUR 13 05.03.2012 24 jur 25000
WW 6000 RUR 18 04.06.2005 24 fiz 6000

формулу рассмотрим на примере fiz
средний процент = sum(RATA *SM_LOC ) /sum(SM_LOC) = (11 * 10000 + 18 * 6000) / (10000 + 6000) = 13.625
17 июл 12, 16:29    [12878899]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Len4ick
Member

Откуда:
Сообщений: 168
результат такой (в прикрепленном файле)

К сообщению приложен файл (rezult.xlsx - 13Kb) cкачать
17 июл 12, 16:57    [12879179]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Len4ick
Member

Откуда:
Сообщений: 168
хотя бы идеи, как реализовать, подкиньте
18 июл 12, 11:13    [12882200]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Glory
Member

Откуда:
Сообщений: 104751
GROUP BY ... WITH ROLLUP
18 июл 12, 11:54    [12882512]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
filthy_lie
Member

Откуда:
Сообщений: 2
как-нить так:
select
case
when t3.TYPE_CLIENT = 'fiz' then 'физические лица:'
when t3.TYPE_CLIENT = 'jur' then 'юридические лица:'
when t3.TYPE_CLIENT = 'for' then 'иностранцы:'
when GROUPING(t3.TYPE_CLIENT) = 1 and t3.VALUTA = 'RUR' then 'В национальной валюте:'
when GROUPING(t3.TYPE_CLIENT) = 1 and t3.VALUTA <> 'RUR' then 'В иностранной валюте:'
when GROUPING(t3.VALUTA) = 1 then 'Всего вкладов из которых:'
end as [Наименование],
str(CAST(sum(RATA *SM_LOC ) as float)/cast (sum(SM_LOC) as float),5,2) as [Средний процент],
SUM(SM_LOC) as [Сумма],
t3.VALUTA, COUNT(*) as [RowCount], GROUPING(t3.VALUTA) as GroupVALUTA, GROUPING(t3.TYPE_CLIENT) as GroupClient
from tab3 as t3
group by rollup (t3.VALUTA, t3.TYPE_CLIENT)
18 июл 12, 14:00    [12883265]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Len4ick
Member

Откуда:
Сообщений: 168
но вы не учитываете период DURATION, ведь данные делятся еще и так
CASE 
             WHEN DURATION<=12 then '< 1 year'
             when DURATION<= then '< 2 year'
             else '> 2 years'
         end
18 июл 12, 15:55    [12884156]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Len4ick
Member

Откуда:
Сообщений: 168
выдается ошибка
The CUBE() and ROLLUP() grouping constructs are not allowed in the current compatibility mode. They are only allowed in 100 mode or higher.
declare  @SomeTable TABLE
(
NAME		varchar(63) NOT NULL,
SUMMA		int NOT NULL,
VALUTA		varchar(10) NOT NULL,
RATA		decimal NOT NULL,
DURATION	int NOT NULL,
TYPE_CLIENT	varchar(5) NOT NULL,
SM_LOC		int NOT NULL
)

INSERT
INTO @SomeTable (NAME,  SUMMA,   VALUTA, RATA,  DURATION,  TYPE_CLIENT,   SM_LOC)
VALUES		('B',    20000,   'EUR',   15,     36,       'jur',      800000),
		('YY',   10000,   'USA',   12,     12,       'for',      400000),
		('DD',   20000,   'RUR',   15,	   24,       'jur',       10000),
		('GF',   10000,   'RUR',   12,	   36,       'fiz',       10000),
		('HE',   10000,   'EUR',   11,	   12,       'jur',       110000),
		('QAZ',  10000,   'USA',   18,	   24,       'FIZ',       70000),
		('ERR',  1000,    'RUR',   10,	   12,       'jur',       10000),
		('DD',   2000,    'EUR',   11,	   24,       'fiz',       10000),
		('ER',   10000,   'EUR',   11,	   36,       'for',       180000),
		('DD',   10000,   'RUR',   11,	   12,       'jur',       10000),
		('DD',   7000,    'RUR',   17,	   24,       'for',       903000),
		('DD',   10000,   'EUR',   11,	   12,       'fiz',       100000),
		('PO',   4000,    'EUR',   25,	   24,       'jur',       9000),
		('TTT',  10000,   'EUR',   22,	   36,       'jur',       10000),
		('GDF',  10000,   'RUR',   8,	   24,       'for',       102000),
		('OP',   10000,   'USA',   13,	   12,       'fiz',       30000),
		('BF',   10000,   'EUR',   9,	   36,       'jur',       70000)
			
	
select  case
	when t3.TYPE_CLIENT = 'fiz' then 'физические лица:'
	when t3.TYPE_CLIENT = 'jur' then 'юридические лица:'
	when t3.TYPE_CLIENT = 'for' then 'иностранцы:'
	when GROUPING(t3.TYPE_CLIENT) = 1 and t3.VALUTA = 'RUR' then 'В национальной валюте:'
	when GROUPING(t3.TYPE_CLIENT) = 1 and t3.VALUTA <> 'RUR' then 'В иностранной валюте:'
	when GROUPING(t3.VALUTA) = 1 then 'Всего вкладов из которых:'
	end as [Наименование],
	STR(cast(sum(RATA *SM_LOC ) as float)/CAST (SUM(SM_LOC) as float),5,2) as [Средний процент],
	SUM(SM_LOC) as [Сумма],
	t3.VALUTA
	GROUPING(t3.VALUTA) as GroupVALUTA,
	GROUPING(t3.TYPE_CLIENT) as GroupClient
from @SomeTable as t3 
group by rollup (VALUTA, TYPE_CLIENT) 
19 июл 12, 09:46    [12886728]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Len4ick
Member

Откуда:
Сообщений: 168
так работает но выдает данные не в том порядке и без группировки в зависимости от срока вклада
select  case
	when t3.TYPE_CLIENT = 'fiz' then 'физические лица:'
	when t3.TYPE_CLIENT = 'jur' then 'юридические лица:'
	when t3.TYPE_CLIENT = 'for' then 'иностранцы:'
	when GROUPING(t3.TYPE_CLIENT) = 1 and t3.VALUTA = 'RUR' then 'В национальной валюте:'
	when GROUPING(t3.TYPE_CLIENT) = 1 and t3.VALUTA <> 'RUR' then 'В иностранной валюте:'
	when GROUPING(t3.VALUTA) = 1 then 'Всего вкладов из которых:'
	end as [Наименование],
	STR(cast(sum(RATA *SM_LOC ) as float)/CAST (SUM(SM_LOC) as float),5,2) as [Средний процент],
	SUM(SM_LOC) as [Сумма],
	t3.VALUTA,
	GROUPING(t3.VALUTA) as GroupVALUTA,
	GROUPING(t3.TYPE_CLIENT) as GroupClient
from @SomeTable as t3 
group by VALUTA, TYPE_CLIENT
with cube 
19 июл 12, 10:11    [12886844]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Len4ick
Member

Откуда:
Сообщений: 168
если меняю cube на rollup то уже лучше но опять не знаю как быть с группировкой в зависимости от срока вклада
19 июл 12, 10:15    [12886861]     Ответить | Цитировать Сообщить модератору
 Re: группировка данных и итоговая сумма  [new]
Len4ick
Member

Откуда:
Сообщений: 168
вопрос закрыт
19 июл 12, 12:47    [12887843]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить