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

Откуда:
Сообщений: 154
Добрый день, коллеги!
Помогите, пожалуйста, с запросом

есть таблица с проводками
kod date1 date2 summa
EOF001 01.01.201928.02.2019 1000
EPR001 01.12.201815.01.2019 300


Нужно разделить по месяцам. Суммы поделить соответственно по количеству дней
и получить такой результат

kod date1 date2 summa
EOF001 01.01.201931.01.2019 525.42
EOF001 01.02.201928.02.2019 474.58
EPR001 01.12.201831.12.2018 202.17
EPR001 01.01.201931.01.2019 97.83


Посоветуйте, пожалуйста, как бы Вы сделали такое
18 фев 19, 00:52    [21812444]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
Таблица-календарь.
18 фев 19, 00:57    [21812445]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20533
Почему date2 для EPR001 из исходного 15.01.2019 превратилось в 31.01.2019?
18 фев 19, 07:33    [21812484]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Tactical Nuclear Penguin
Member

Откуда: холодно тут
Сообщений: 2730
Akina
Почему date2 для EPR001 из исходного 15.01.2019 превратилось в 31.01.2019?


потому что в январе 31 день
18 фев 19, 07:50    [21812489]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20533
Tactical Nuclear Penguin
потому что в январе 31 день
Исходной является таблица проводок. Изменение данных из неё должно быть установлено правилом преобразования, которое не озвучено. А в нынешнем варианте его нет, и я сомневаюсь, что оно вообще есть. Кроме того, подобная замена делает преобразование данных необратимым, что неправильно с точки зрения логики выполняемого преобразования с учётом предметной области, и лишает смысла получаемый результат.
18 фев 19, 08:26    [21812496]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
ilshatkin
Member

Откуда:
Сообщений: 154
Akina,
Это преобразование во временную таблицу для формирования отчета по месяцам. В принципе можно оставлять даты не изменными, главное поделить по месяцам. Как это лучше всего сделать?
18 фев 19, 09:19    [21812530]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20533
ilshatkin
Как это лучше всего сделать?
Строите опорную таблицу периодов в CTE. Элементарно.
Находите пересечения опорных периодов с периодами проводок в CTE2. Обсуждалось сто раз.
На основании продолжительностей полученных пересечений получаете их длительность.
Множите длительность на "стоимость одного дня". Получаете раскладку по месяцам.
18 фев 19, 10:05    [21812562]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20533
ilshatkin
В принципе можно оставлять даты не изменными
Тогда в результате date1 - преобразовать в месяц-год, а date2 вообще выбросить.
18 фев 19, 10:06    [21812564]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
set dateformat dmy;

Declare @t table 
 (Kod varchar(10),
 date1 date,
 date2 date,
 summa decimal(10,2))

 Insert into @t 
VALUES ('EOF001','01.01.2019','28.02.2019', 1000),
       ('EPR001','01.12.2018','15.01.2019',300.01),
       ('ДляОкругл','17.12.2018','15.01.2019',300.01);



With dates as (Select (Select dateadd(d,1-day(min(date1)),min(date1)) From @t) as Date_Begin, 
                      (Select max(date2) From @t) as Date_End),

  calendar as (Select Date_Begin as Month_Begin,
                      dateadd(d,-1,dateadd(m,1,Date_Begin)) as Month_End
                 From dates
                union all
               Select dateadd(m,1,Month_Begin),
                      dateadd(d,-1,dateadd(m,2,Month_Begin))
                 From calendar 
                 join dates on dateadd(m,1,Month_Begin) <= Date_End),

 Month_Sum as (Select a.Kod,
                      iif(a.date1>c.Month_Begin,a.date1,c.Month_Begin) as Date1,
                      iif(a.date2<c.Month_End,  a.date2,c.Month_End)   as Date2,
                      Cast(a.Summa
                           *(datediff(d,iif(a.date1>c.Month_Begin,a.date1,c.Month_Begin),iif(a.date2<c.Month_End,  a.date2,c.Month_End))+1)
                           /(datediff(d,a.date1,a.date2)+1) as Decimal(10,2)) as Summa_Month,
                      a.Summa as Summa_All
                 From @t a
                join calendar c on a.date1<=c.Month_End and a.date2 >= c.Month_Begin)
--Убираем ошибку окргуления
 Select Kod,Date1,Date2,
        Summa_month
        +iif(row_number() Over (Partition by Kod,Summa_all Order by Summa_Month desc) = 1, 
             summa_all-sum(Summa_month) over (Partition by Kod,Summa_all), 
             0)
   From Month_Sum
  Order by Kod,date1
18 фев 19, 10:45    [21812616]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
_human
Member

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

	SET DATEFORMAT dmy; 

	declare @t table (kod	varchar(10), date1 date,	date2	date, summa int)
	insert @t values 
	 ('EOF001',	'01.01.2019',	'28.02.2019',	1000)
	,('EPR001',	'01.12.2018',	'15.01.2019',	300	)

		select kod
			, case when c.v = 1 then date1 else DATEFROMPARTS(year(date2), month(date2), 1) end										
			, case when c.v = 1 then EOMONTH(date1) else EOMONTH(date2) end								
			, case when c.v = 1 then 1.*summa*datediff(day, date1, EOMONTH(date1))/(datediff(day, date1, EOMONTH(date1))+datediff(day, DATEFROMPARTS(year(date2), month(date2), 1),date2)) else 1.*summa*datediff(day, DATEFROMPARTS(year(date2), month(date2), 1),date2)/(datediff(day, date1, EOMONTH(date1))+datediff(day, DATEFROMPARTS(year(date2), month(date2), 1),date2)) end								
		from @t
		cross apply (select v from (values (1), (2)) q(v)) as C
18 фев 19, 14:02    [21812995]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
ilshatkin
Member

Откуда:
Сообщений: 154
_human, работает!
подскажи, пожалуйста, как это работает.

Не понятна конструкция (select v from (values (1), (2)) q(v)),
что такое q(v)?
19 фев 19, 04:04    [21813859]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
ilshatkin
Member

Откуда:
Сообщений: 154
Kopelly, тоже работает,

Спасибо, друзья

теперь бы еще разобраться как все это работает
19 фев 19, 04:38    [21813862]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Kopelly
Member

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

Не корректно при datediff(m,Date1,Date2)>=2?
19 фев 19, 04:52    [21813864]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iap
Member

Откуда: Москва
Сообщений: 46978
ilshatkin
_human, работает!
подскажи, пожалуйста, как это работает.

Не понятна конструкция (select v from (values (1), (2)) q(v)),
что такое q(v)?


Конструктор табличных значений (В)
19 фев 19, 09:43    [21813966]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить