Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Написать запрос / 7 сообщений из 7, страница 1 из 1
03.05.2018, 11:44
    #39639267
Кареглазая_зая
Кареглазая_зая
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Написать запрос
Всем привет! Помогите пожалуйста решить такое задание

На основе данных вывести таблицу остатков на счетах на конец текущего месяца и на конец каждого из пяти предыдущих.

В ko представлены 4 контрагента 'Север', 'Запад', 'Восток', 'Юг'
id - id контрагента
name - название контрагента

В ac представлены движения средств на счетах:
ko - id контрагента из представления ko
oper_date - дата операции
amount - сумма
direction - направление движения средств (1 - поступление, 0 - снятие)

Дописать запрос, для получения результата
[img=Безымянный.png]

with
ko as
(
select 1 id, 'Север' name union all
select 2 id, 'Запад' name union all
select 3 id, 'Восток' name union all
select 4 id, 'Юг' name
),
ac as
(
select 1 ko, convert(datetime, '01.01.2017', 104) oper_date, 100 amount, 1 direction union all
select 1 ko, convert(datetime, '11.01.2017', 104) oper_date, 30 amount, 0 direction union all
select 1 ko, convert(datetime, '01.02.2017', 104) oper_date, 230 amount, 1 direction union all
select 1 ko, convert(datetime, '11.02.2017', 104) oper_date, 40 amount, 0 direction union all
select 1 ko, convert(datetime, '01.03.2017', 104) oper_date, 90 amount, 1 direction union all
select 1 ko, convert(datetime, '11.03.2017', 104) oper_date, 180 amount, 0 direction union all
select 1 ko, convert(datetime, '01.04.2017', 104) oper_date, 400 amount, 1 direction union all
select 1 ko, convert(datetime, '11.04.2017', 104) oper_date, 100 amount, 0 direction union all
select 1 ko, convert(datetime, '01.05.2017', 104) oper_date, 120 amount, 1 direction union all
select 1 ko, convert(datetime, '11.05.2017', 104) oper_date, 310 amount, 0 direction union all
select 1 ko, convert(datetime, '01.06.2017', 104) oper_date, 100 amount, 1 direction union all
select 1 ko, convert(datetime, '11.06.2017', 104) oper_date, 40 amount, 0 direction union all
select 1 ko, convert(datetime, '01.07.2017', 104) oper_date, 90 amount, 1 direction union all
select 1 ko, convert(datetime, '11.07.2017', 104) oper_date, 180 amount, 0 direction union all
select 1 ko, convert(datetime, '01.08.2017', 104) oper_date, 400 amount, 1 direction union all
select 1 ko, convert(datetime, '11.08.2017', 104) oper_date, 100 amount, 0 direction union all
select 1 ko, convert(datetime, '01.09.2017', 104) oper_date, 120 amount, 1 direction
union all
select 2 ko, convert(datetime, '01.02.2017', 104) oper_date, 725 amount, 1 direction union all
select 2 ko, convert(datetime, '11.02.2017', 104) oper_date, 40 amount, 0 direction union all
select 2 ko, convert(datetime, '01.03.2017', 104) oper_date, 90 amount, 1 direction union all
select 2 ko, convert(datetime, '11.03.2017', 104) oper_date, 180 amount, 0 direction union all
select 2 ko, convert(datetime, '01.04.2017', 104) oper_date, 100 amount, 1 direction union all
select 2 ko, convert(datetime, '11.04.2017', 104) oper_date, 380 amount, 0 direction union all
select 2 ko, convert(datetime, '01.05.2017', 104) oper_date, 120 amount, 1 direction union all
select 2 ko, convert(datetime, '11.05.2017', 104) oper_date, 480 amount, 0 direction union all
select 2 ko, convert(datetime, '01.06.2017', 104) oper_date, 80 amount, 1 direction
union all
select 3 ko, convert(datetime, '01.01.2017', 104) oper_date, 125 amount, 1 direction union all
select 3 ko, convert(datetime, '11.02.2017', 104) oper_date, 40 amount, 0 direction union all
select 3 ko, convert(datetime, '01.03.2017', 104) oper_date, 90 amount, 1 direction union all
select 3 ko, convert(datetime, '11.03.2017', 104) oper_date, 180 amount, 0 direction union all
select 3 ko, convert(datetime, '01.06.2017', 104) oper_date, 100 amount, 1 direction union all
select 3 ko, convert(datetime, '11.06.2017', 104) oper_date, 80 amount, 0 direction union all
select 3 ko, convert(datetime, '01.08.2017', 104) oper_date, 120 amount, 1 direction union all
select 3 ko, convert(datetime, '11.08.2017', 104) oper_date, 10 amount, 0 direction union all
select 3 ko, convert(datetime, '01.09.2017', 104) oper_date, 80 amount, 1 direction
union all
select 4 ko, convert(datetime, '01.02.2017', 104) oper_date, 90 amount, 0 direction union all
select 4 ko, convert(datetime, '11.02.2017', 104) oper_date, 180 amount, 1 direction union all
select 4 ko, convert(datetime, '01.05.2017', 104) oper_date, 100 amount, 0 direction union all
select 4 ko, convert(datetime, '01.08.2017', 104) oper_date, 120 amount, 1 direction union all
select 4 ko, convert(datetime, '11.08.2017', 104) oper_date, 480 amount, 1 direction union all
select 4 ko, convert(datetime, '01.09.2017', 104) oper_date, 80 amount, 1 direction
)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 12:00
    #39639277
Кареглазая_зая
Кареглазая_зая
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Написать запрос
Дописать запрос, для получения результата

В результате запроса получаем 7 полей:
NAME - название контрагента
M5-M1 - 5 предыдущих месяцев

M0 - текущий (последнний) месяц

В первой строчке выводиться "Контрагент" и названия месяцев на русском языке

далее идут строчки с названием контрагента и остатком на счете на конец месяца (в случае, если у него есть хоть одна операция со счетом)

последней строчкой идут итоги - сумма средств/задолженностей на счетах всех контрагентов

NAMEM5M4M3M2M1M0 Контрагентапрельмайиюньиюльавгустсентябрь Север470280340250550670Восток-5-51515125205Запад315-4535353535Юг90-10-10-10590670Итого:


Делала так :
Код: sql
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.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
create table #TempRes (
  name_k nvarchar(50),
  m5 nvarchar(50),
  m4 nvarchar(50),
  m3 nvarchar(50),
  m2 nvarchar(50),
  m1 nvarchar(50),
  m0 nvarchar(50)
)

select top 6 
       month(ac.oper_date) as num_month,
       datename(month,oper_date) as name_month
into #TempMonth       
from ac
group by month(ac.oper_date),datename(month,oper_date)
order by month(ac.oper_date) desc



DECLARE @mnth nvarchar(MAX) = ''
DECLARE  @onemnth nVarchar(MAX) = ''

DECLARE Kurs CURSOR
for
	select t.name_month
	from #TempMonth t
	order by t.num_month

OPEN Kurs
FETCH NEXT FROM Kurs into @onemnth
WHILE @@FETCH_STATUS = 0
	begin
	    set @mnth = @mnth + '[' + CAST(@onemnth as nvarchar(20)) + '],'
		FETCH NEXT FROM Kurs into @onemnth
	end
CLOSE Kurs
DEALLOCATE Kurs 

set @mnth = Left(@mnth,Len(@mnth)-1)

declare @temp_p nvarchar(50)
set @temp_p = 'Итоги'

execute 
( '
  select name as Контрагенты,'+ @mnth + '
  from
   ( select 
		datename(month,a.oper_date) as name_month,
		ko.name,
		coalesce((a.direction*(2) - 1)*a.amount,0) as summa
	from ac a
	join dbo.ko on ko.id = a.ko 
	join #TempMonth t on t.num_month = month(a.oper_date)

	union all
	  
	  select
        datename(month,a.oper_date) as name_month,
        'Itog' as name,
        sum(coalesce((a.direction*(2) - 1)*a.amount,0)) as sum
        from ac a
 
       where month(a.oper_date) in (select t.num_month from #TempMonth t) 
        group by datename(month,a.oper_date) 

   ) tbl
   PIVOT (sum(summa) FOR name_month IN (' + @mnth + ')) rep'
)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 12:38
    #39639322
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Написать запрос
Кареглазая_зая,

Код: sql
1.
2.
select ko, (вычислить название месяца), sum(case when oper_date <= dateadd(day, -5, getdate()) then amount else 0 end * (direction*2-1)) m5, ... m4, ...m3, ... m2, ... m1, sum(amount * (direction*2-1)) m0
group by ko, (вычислить название месяца)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 12:39
    #39639326
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Написать запрос
Название месяца там лишнее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 12:52
    #39639343
Kopelly
Kopelly
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Написать запрос
Кареглазая_зая,

1. Остаток считается с начала времен до нужной даты (а не за месяц)
2. Предыдущие 5 месяцев правильнее определять через Dateadd, так операции могут быть не во всех периодах.
3. В подобной задаче Pivot может дать худшую производительность, чем
Код: sql
1.
Sum(Case When oper_date < dateadd(-[Сдвиг],m,@НачалоПериодаСледующегоЗаТекущим) Then (direction*(2) - 1)*amount end) as m[Сдвиг]


Из-за дополнительных чтений таблицы движений.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 17:26
    #39639579
Кареглазая_зая
Кареглазая_зая
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Написать запрос
Kopelly, а если заменить конструкцию

Код: sql
1.
cast(sum(case when a.oper_date < dateadd(month, -5,@dt) then a.amount else 0 end * (a.direction*2-1)) as nvarchar(10)) m5,


на
Код: sql
1.
cast(sum(iif(a.oper_date < dateadd(month, -5, @dt),a.amount, 0) * (a.direction*2-1)) as nvarchar(10)) m5,



по сути же тоже самое? или case лучше?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 17:51
    #39639599
Ролг Хупин
Ролг Хупин
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Написать запрос
Кареглазая_заяKopelly, а если заменить конструкцию

Код: sql
1.
cast(sum(case when a.oper_date < dateadd(month, -5,@dt) then a.amount else 0 end * (a.direction*2-1)) as nvarchar(10)) m5,


на
Код: sql
1.
cast(sum(iif(a.oper_date < dateadd(month, -5, @dt),a.amount, 0) * (a.direction*2-1)) as nvarchar(10)) m5,



по сути же тоже самое? или case лучше?

А если @@VERSION написать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Написать запрос / 7 сообщений из 7, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]