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

Откуда:
Сообщений: 38
Всем привет! Помогите пожалуйста решить такое задание

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

В 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
)
3 май 18, 11:44    [21383894]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос  [new]
Кареглазая_зая
Member

Откуда:
Сообщений: 38
Дописать запрос, для получения результата

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

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

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

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

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

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



Делала так :
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'
)
3 май 18, 12:00    [21383935]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Кареглазая_зая,

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, (вычислить название месяца)
3 май 18, 12:38    [21384074]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Название месяца там лишнее.
3 май 18, 12:39    [21384079]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Кареглазая_зая,

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

Из-за дополнительных чтений таблицы движений.
3 май 18, 12:52    [21384124]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос  [new]
Кареглазая_зая
Member

Откуда:
Сообщений: 38
Kopelly, а если заменить конструкцию

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,

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


по сути же тоже самое? или case лучше?
3 май 18, 17:26    [21385405]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3462
Кареглазая_зая
Kopelly, а если заменить конструкцию

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,

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


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


А если @@VERSION написать?
3 май 18, 17:51    [21385520]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить