Гость
Целевая тема:
Создать новую тему:
Автор:
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите с запросом / 14 сообщений из 14, страница 1 из 1
18.02.2019, 00:52
    #39775285
ilshatkin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
Добрый день, коллеги!
Помогите, пожалуйста, с запросом

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

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

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

Посоветуйте, пожалуйста, как бы Вы сделали такое
...
Рейтинг: 0 / 0
18.02.2019, 00:57
    #39775286
Гавриленко Сергей Алексеевич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
Таблица-календарь.
...
Рейтинг: 0 / 0
18.02.2019, 07:33
    #39775298
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
Почему date2 для EPR001 из исходного 15.01.2019 превратилось в 31.01.2019?
...
Рейтинг: 0 / 0
18.02.2019, 07:50
    #39775301
Tactical Nuclear Penguin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
AkinaПочему date2 для EPR001 из исходного 15.01.2019 превратилось в 31.01.2019?

потому что в январе 31 день
...
Рейтинг: 0 / 0
18.02.2019, 08:26
    #39775306
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
Tactical Nuclear Penguinпотому что в январе 31 деньИсходной является таблица проводок. Изменение данных из неё должно быть установлено правилом преобразования, которое не озвучено. А в нынешнем варианте его нет, и я сомневаюсь, что оно вообще есть. Кроме того, подобная замена делает преобразование данных необратимым, что неправильно с точки зрения логики выполняемого преобразования с учётом предметной области, и лишает смысла получаемый результат.
...
Рейтинг: 0 / 0
18.02.2019, 09:19
    #39775332
ilshatkin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
Akina,
Это преобразование во временную таблицу для формирования отчета по месяцам. В принципе можно оставлять даты не изменными, главное поделить по месяцам. Как это лучше всего сделать?
...
Рейтинг: 0 / 0
18.02.2019, 10:05
    #39775352
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
ilshatkinКак это лучше всего сделать?Строите опорную таблицу периодов в CTE. Элементарно.
Находите пересечения опорных периодов с периодами проводок в CTE2. Обсуждалось сто раз.
На основании продолжительностей полученных пересечений получаете их длительность.
Множите длительность на "стоимость одного дня". Получаете раскладку по месяцам.
...
Рейтинг: 0 / 0
18.02.2019, 10:06
    #39775353
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
ilshatkinВ принципе можно оставлять даты не изменнымиТогда в результате date1 - преобразовать в месяц-год, а date2 вообще выбросить.
...
Рейтинг: 0 / 0
18.02.2019, 10:45
    #39775378
Kopelly
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
Код: 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.
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
...
Рейтинг: 0 / 0
18.02.2019, 14:02
    #39775560
_human
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
Kopelly,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
	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
...
Рейтинг: 0 / 0
19.02.2019, 04:04
    #39775986
ilshatkin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
_human, работает!
подскажи, пожалуйста, как это работает.

Не понятна конструкция (select v from (values (1), (2)) q(v)),
что такое q(v)?
...
Рейтинг: 0 / 0
19.02.2019, 04:38
    #39775988
ilshatkin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
Kopelly, тоже работает,

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

теперь бы еще разобраться как все это работает
...
Рейтинг: 0 / 0
19.02.2019, 04:52
    #39775989
Kopelly
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
_human,

Не корректно при datediff(m,Date1,Date2)>=2?
...
Рейтинг: 0 / 0
19.02.2019, 09:43
    #39776032
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите с запросом
ilshatkin_human, работает!
подскажи, пожалуйста, как это работает.

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

Конструктор табличных значений (В)
...
Рейтинг: 0 / 0
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите с запросом / 14 сообщений из 14, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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