ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Написать запрос
7 сообщений из 7, страница 1 из 1
Написать запрос
    #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, 11:44
| Ответить | Цитировать | Написать  
Написать запрос
    #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:00
| Ответить | Цитировать | Написать  
Написать запрос
    #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:38
| Ответить | Цитировать | Написать  
Написать запрос
    #39639326
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Название месяца там лишнее.
...
Рейтинг: 0 / 0
03.05.2018, 12:39
| Ответить | Цитировать | Написать  
Написать запрос
    #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, 12:52
| Ответить | Цитировать | Написать  
Написать запрос
    #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:26
| Ответить | Цитировать | Написать  
Написать запрос
    #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
03.05.2018, 17:51
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Написать запрос
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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