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

Откуда: Керчь
Сообщений: 10
Всем доброго врмени суток!
У меня возник вопрос такого рода. Делаю джойн двух таблиц - получаю даты и суммы, около 1000 записей.
Получаю это "добро" таким кодом:

DECLARE @Category_RentHouse INT = 3
DECLARE @Category_Parents INT = 5
DECLARE @Category_Salary INT = 9
DECLARE @TestDateStart DATE = '2012-01-01'
DECLARE @TestDateFinish DATE = '2017-03-10'
select JoinedDays, SUM(Cash_Usd) TotalMoney
from (
select CAST(TransactionDate AS DATE) JoinedDays, CASE
WHEN CategoryID = @Category_RentHouse THEN (TransactionAmount*(-1))
WHEN CategoryID = @Category_Parents THEN TransactionAmount
WHEN CategoryID = @Category_Salary THEN CAST((TransactionAmount / RateValue) AS MONEY)
ELSE CAST((TransactionAmount*(-1) / RateValue) AS MONEY)
END AS Cash_Usd
FROM (select *
from Marathon.dbo.Transactions T 
LEFT JOIN IntermediateM.dbo.Rates R
ON T.TransactionDate = R.RateDate) Y
) RR
WHERE JoinedDays BETWEEN @TestDateStart AND @TestDateFinish
GROUP BY JoinedDays
ORDER BY JoinedDays


А вопрос такой - мне нужно вместо этого выводить соотвествующую сумму за каждые 7 дней в течение всего этого 5-ти летнего периода.
Попробовал несколько вариантов - самый близкий - суммирует понедельно, но только за один 2012 год. И все. А нужно за полный период.

К сообщению приложен файл. Размер - 43Kb
14 дек 17, 17:07    [21034442]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
EliDergun
Member

Откуда:
Сообщений: 10
Можно решит в лоб, не корректно, но решить.

через day(date)
как только возвращаемое значение day(date) больше 7 то сумму обнулить и складывать заново
14 дек 17, 17:27    [21034513]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
rhasta
Member

Откуда: Керчь
Сообщений: 10
МОжно и не в лоб, главное, чтоб корректно было
14 дек 17, 17:34    [21034540]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
rhasta, такое хотите?

DECLARE @Category_RentHouse INT = 3
DECLARE @Category_Parents INT = 5
DECLARE @Category_Salary INT = 9
DECLARE @TestDateStart DATE = '2012-01-01'
DECLARE @TestDateFinish DATE = '2017-03-10'

select dateadd(@TestDateStart, (datediff(day, @TestDateStart, JoinedDays) / 7) * 7, @TestDateStart) as JoinedDaysWeek, SUM(Cash_Usd) TotalMoney
from (
        select CAST(TransactionDate AS DATE) JoinedDays
            ,   case
                    when CategoryID = @Category_RentHouse THEN (TransactionAmount*(-1))
                    when CategoryID = @Category_Parents THEN TransactionAmount
                    when CategoryID = @Category_Salary THEN CAST((TransactionAmount / RateValue) AS MONEY)
                    else CAST((TransactionAmount*(-1) / RateValue) AS MONEY)
                  end AS Cash_Usd
        from (select *
                from Marathon.dbo.Transactions T 
                        left JOIN IntermediateM.dbo.Rates R
                            on T.TransactionDate = R.RateDate) Y
        ) RR
WHERE JoinedDays BETWEEN @TestDateStart AND @TestDateFinish
GROUP BY (datediff(day, @TestDateStart, JoinedDays) / 7)
ORDER BY (datediff(day, @TestDateStart, JoinedDays) / 7)
14 дек 17, 17:40    [21034570]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
SET DATEFIRST 1
SELECT
  [dd] = CONVERT( DATE, GETDATE() ),
  [wb] = DATEADD( DAY, 1 - DATEPART( WEEKDAY, GETDATE() ), CONVERT( DATE, GETDATE() ) ),
  [we] = DATEADD( DAY, 7 - DATEPART( WEEKDAY, GETDATE() ), CONVERT( DATE, GETDATE() ) )
14 дек 17, 17:41    [21034578]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
iiyama
Member

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

Я бы завел табличку-календарь, присоединил бы и группировал бы по нужному полю из календаря.
14 дек 17, 17:50    [21034602]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
rhasta
Member

Откуда: Керчь
Сообщений: 10
Minamoto
rhasta, такое хотите?

DECLARE @Category_RentHouse INT = 3
DECLARE @Category_Parents INT = 5
DECLARE @Category_Salary INT = 9
DECLARE @TestDateStart DATE = '2012-01-01'
DECLARE @TestDateFinish DATE = '2017-03-10'

select dateadd(@TestDateStart, (datediff(day, @TestDateStart, JoinedDays) / 7) * 7, @TestDateStart) as JoinedDaysWeek, SUM(Cash_Usd) TotalMoney
from (
        select CAST(TransactionDate AS DATE) JoinedDays
            ,   case
                    when CategoryID = @Category_RentHouse THEN (TransactionAmount*(-1))
                    when CategoryID = @Category_Parents THEN TransactionAmount
                    when CategoryID = @Category_Salary THEN CAST((TransactionAmount / RateValue) AS MONEY)
                    else CAST((TransactionAmount*(-1) / RateValue) AS MONEY)
                  end AS Cash_Usd
        from (select *
                from Marathon.dbo.Transactions T 
                        left JOIN IntermediateM.dbo.Rates R
                            on T.TransactionDate = R.RateDate) Y
        ) RR
WHERE JoinedDays BETWEEN @TestDateStart AND @TestDateFinish
GROUP BY (datediff(day, @TestDateStart, JoinedDays) / 7)
ORDER BY (datediff(day, @TestDateStart, JoinedDays) / 7)


Да, это очень поохоже на то, что я ожидал. Единственное, у меня заругалось на первый аргумент dateadd(@TestDateStart - я заменил на DAY и выдало как я и ожидал понедельно
14 дек 17, 18:05    [21034639]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
rhasta
Member

Откуда: Керчь
Сообщений: 10
iiyama
rhasta,

Я бы завел табличку-календарь, присоединил бы и группировал бы по нужному полю из календаря.

У меня совсем критична производительность, поэтому низзя вот так разбрасываться на временные таблицы
14 дек 17, 18:16    [21034664]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
rhasta,

статический календарь достаточно полезная вещь. Ну и про производительность вы ничего не знаете если такое пишите
14 дек 17, 18:18    [21034669]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
rhasta
Member

Откуда: Керчь
Сообщений: 10
Скорее всего и не знаю про производительность...спорить не буду, особенно в том, в чем не уверен. Буду гуглить, что это за статический календарь и как с ним работать
15 дек 17, 09:17    [21035715]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30734
rhasta
iiyama
rhasta,

Я бы завел табличку-календарь, присоединил бы и группировал бы по нужному полю из календаря.

У меня совсем критична производительность, поэтому низзя вот так разбрасываться на временные таблицы
Дополнительная таблица обычно повышает производительность, а не понижает, потому что она хранит данные, которые в противном случае пришлось бы вычислять при каждом запросе.
А вообще для высокого быстродействия хранят агрегаты, а не вычисляют их каждый раз для отчёта.
15 дек 17, 10:07    [21035898]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
rhasta
Member

Откуда: Керчь
Сообщений: 10
И это я тоже учту
15 дек 17, 11:46    [21036242]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
rhasta
Member

Откуда: Керчь
Сообщений: 10
В итоге, по моему вопросу, чтобы учесть еженедельную статистику

DECLARE @Category_RentHouse INT = 3
DECLARE @Category_Parents INT = 5
DECLARE @Category_Salary INT = 9
DECLARE @TestDateStart DATE = '2012-01-01'
DECLARE @TestDateFinish DATE = '2017-03-10'
DECLARE @DaysInWeek INT = 7
DECLARE @MinusOne INT = -1
DECLARE @SixDays INT = 6

SELECT DATEADD(DAY, ((DATEDIFF(DAY, @TestDateStart, JoinedDays) / @DaysInWeek) * @DaysInWeek + @SixDays), @TestDateStart) AS WeeksStatistic, SUM(Cash_Usd) CashFlow_USD
FROM (
SELECT CAST(TransactionDate AS DATE) JoinedDays,
CASE
WHEN CategoryID = @Category_RentHouse THEN (TransactionAmount*(@MinusOne))
WHEN CategoryID = @Category_Parents THEN TransactionAmount
WHEN CategoryID = @Category_Salary THEN CAST((TransactionAmount / RateValue) AS MONEY)
ELSE CAST((TransactionAmount*(@MinusOne) / RateValue) AS MONEY)
END AS Cash_Usd
FROM (
SELECT *
FROM Marathon.dbo.Transactions T 
LEFT JOIN IntermediateM.dbo.Rates R
ON T.TransactionDate = R.RateDate) Y
) RR
WHERE JoinedDays BETWEEN @TestDateStart AND @TestDateFinish
GROUP BY (DATEDIFF(day, @TestDateStart, JoinedDays) / @DaysInWeek)
HAVING SUM(Cash_Usd) > 0
ORDER BY (DATEDIFF(day, @TestDateStart, JoinedDays) / @DaysInWeek)


Код выдает то, что я именно от него и хотел получить
15 дек 17, 15:48    [21037308]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
qwertEHOK
Member

Откуда: Волгоград
Сообщений: 270
кто там писал про производительность?
15 дек 17, 18:02    [21037915]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
rhasta
Member

Откуда: Керчь
Сообщений: 10
А что сней пока не так?Я имею в виду - производительность?
18 дек 17, 09:21    [21041600]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
iii2
Member

Откуда:
Сообщений: 202
Вот, кстати, табличка с датами, может кому пригодится:
Источник http://data.gov.ru/opendata/resource/8ba5011a-233e-4e01-a1d2-ff5598d0f34f#2/0.0/0.0
А вот - его парсинг:
+

/****** Object:  Table [spr].[calendar]    Script Date: 05.10.2016 15:37:16 ******/
if object_id('[spr].[calendar]') is not Null
DROP TABLE [spr].[calendar]
GO

/****** Object:  Table [spr].[calendar]    Script Date: 05.10.2016 15:37:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [spr].[calendar](
	[N] [bigint] not NULL,
	[date_n] [date] not NULL,
	[date] [datetime] not NULL,
	[date1] [datetime] not NULL,
	[year] [int] not NULL,
	[month] [int] not NULL,
	[day] [int] not NULL,
	[dayofyear] [int] not NULL,
	[quarter] [int] not NULL,
	[week] [int] not NULL,
	[weekday] [int] not NULL,
	[weekday_name] [nvarchar](30) not NULL,
	[month_name] [nvarchar](30) not NULL,
	[isWorkDay] [int] NOT NULL,
	[date_n_next] date not NULL,
	[date_n_prev] date not NULL,
	[date_next] [datetime] not NULL,
	[date_prev] [datetime] not NULL,
	[StartDayOfMonth_n] date not NULL,
	[EndDayOfMonth_n] date not NULL,
	[StartDayOfMonth] [datetime] not NULL,
	[EndDayOfMonth] [datetime] not NULL,
	Constraint PK_calendar primary key clustered ([date_n])
) 

GO

SET LANGUAGE russian;
; With N
	As
	(
		Select (ROW_NUMBER() over (order by 1/0) - 1) N
		from            master..spt_values a
			Cross join master..spt_values aa
		where (a.type=N'P' and a.number between 0 and 99)
			And (aa.type=N'P' and aa.number between 0 and 999)
	)
	, N_Date
	as
	(
		Select
			 N                        
			,Dateadd(day,N,'19000101') [date]
		from N
		Where Dateadd(day,N,'19000101')<'21000101'
	)
insert into [dbsm_essentials].[spr].calendar
Select
	 N                   
	,Cast([date] as date) [date_n]
	,[date]
     ,DATETIMEFROMPARTS ( year([date]), month([date]), day([date]), '23', '59', '59', '998' ) [date1]
	,year([date]) [year]
	,month([date]) [month]
	,day([date]) [day]
	,DATEPART(dayofyear,[date]) [dayofyear]
	,DATEPART(quarter,[date]) [quarter]
	,DATEPART(week,[date]) [week]
	,DATEPART(weekday,[date]) [weekday]
	,DATENAME(weekday,[date]) [weekday_name]
	,DATENAME(month,[date]) [month_name]
     ,Case When DATEPART(weekday,[date]) in (6,7) then 0 Else 1 End isWorkDay
	,LEAD([date], 1, '21000101') over (order by [date]) [date_n_next]
	,LAG([date], 1, '18991231') over (order by [date]) [date_n_prev]
	,LEAD([date], 1, '21000101') over (order by [date]) [date_next]
	,LAG([date],1,'18991231') over (order by [date]) [date_prev]
	,Cast(DATEADD(DAY,1,EOMONTH([date],-1)) as date) [StartDayOfMonth_n]
	,Cast(EOMONTH([date]) as date) [EndDayOfMonth_n]
	,Cast(DATEADD(DAY,1,EOMONTH([date],-1)) as datetime) [StartDayOfMonth]
	,Cast(EOMONTH([date]) as datetime) [EndDayOfMonth]
From N_Date
Order by N

;With 
[Calendar] as (
SELECT * 
FROM (
VALUES 
(1,1999,'1,2,3,4,6*,7,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28','6,7,8,13,14,20,21,27,28','3,4,10,11,17,18,24,25,30*','1,2,3,4,8,9,10,15,16,22,23,29,30','5,6,11*,12,13,14,19,20,26,27','3,4,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','2,3,9,10,16,17,23,24,30,31','6,7,8,13,14,20,21,27,28','4,5,11,12,13,18,19,25,26, 31*'),
(2,2000,'1,2,3,4,6*,7,8,9,15,16,22,23,29,30','5,6,12,13,19,20,26,27','4,5,7*,8,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','1,2,6,7,8*,9,13,14,20,21,27,28','3,4,10,11,12,17,18,24,25','1,2,8,9,15,16,22,23,29,30','5,6,12,13,19,20,26,27','2,3,9,10,16,17,23,24,30','1,7,8,14,15,21,22,28,29','4,5,7,11,12,18,19,25,26','2,3,9,10,11*,12,16,17,23,24,30,31'),
(3,2001,'1,2,6,7,8,13,14,20,21,27,28','3,4,10,11,17,18,24,25','3,4,7*,8,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29,30*','1,2,5,6,8*,9,12,13,19,20,26,27','2,3,9,10,11*,12,16,17,23,24,30','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,6*,7,10,11,17,18,24,25','1,2,8,9,12,15,16,22,23,29,30'),
(4,2002,'1,2,5,6,7,12,13,19,20,26,27','2,3,9,10,16,17,22*,23,24,25','2,3,7*,8,9,10,16,17,23,24,30,31','6,7,13,14,20,21,28,30*','1,2,3,4,5,8*,9,10,11,12,19,25,26','1,2,8,9,11*,12,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29','5,6,12,13,19,20,26,27','2,3,6*,7,8,9,16,17,23,24,30','1,7,8,11*,12,13,14,21,22,28,29,31*'),
(5,2003,'1,2,3,5*,6,7,11,12,18,19,25,26','1,2,8,9,15,16,22,23,24','1,2,7*,8,9,10,15,16,22,23,29,30','5,6,12,13,19,20,26,27,30*','1,2,3,4,8*,9,10,11,17,18,24,25,31','1,7,8,11*,12,13,14,15,22,28,29','5,6,12,13,19,20,26,27','2,3,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28','4,5,11,12,18,19,25,26','1,2,6*,7,8,9,15,16,22,23,29,30','6,7,11*,12,13,14,20,21,27,28,31*'),
(6,2004,'1,2,3,4,6*,7,10,11,17,18,24,25,31','1,7,8,14,15,21,22,23,28,29','6,7,8,13,14,20,21,27,28','3,4,10,11,17,18,24,25,30*','1,2,3,4,8,9,10,15,16,22,23,29,30','5,6,11*,12,13,14,19,20,26,27','3,4,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','2,3,9,10,16,17,23,24,30,31','6,7,8,13,14,20,21,27,28','4,5,11,12,13,18,19,25,26,31*'),
(7,2005,'1,2,3,4,5,6,7,8,9,10,15,16,22,23,29,30','5,6,12,13,19,20,22*,23,26,27','5*,6,7,8,12,13,19,20,26,27','2,3,9,10,16,17,23,24,30','1,2,7,8,9,14,15,21,22,28,29','4,5,11,12,13,18,19,25,26','2,3,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28','3,4,10,11,17,18,24,25','1,2,8,9,15,16,22,23,29,30','3*,4,5,6,12,13,19,20,26,27','3,4,10,11,17,18,24,25,31'),
(8,2006,'1,2,3,4,5,6,7,8,9,14,15,21,22,28,29','4,5,11,12,18,19,22*,23,24,25,26','4,5,7*,8,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','1,6*,7,8,9,13,14,20,21,27,28','3,4,10,11,12,17,18,24,25','1,2,8,9,15,16,22,23,29,30','5,6,12,13,19,20,26,27','2,3,9,10,16,17,23,24,30','1,7,8,14,15,21,22,28,29','3*,4,5,6,11,12,18,19,25,26','2,3,9,10,16,17,23,24,30,31'),
(9,2007,'1,2,3,4,5,6,7,8,13,14,20,21,27,28','3,4,10,11,17,18,22*,23,24,25','3,4,7*,8,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28*,29,30','1,5,6,8*,9,12,13,19,20,26,27','2,3,9*,10,11,12,16,17,23,24,30','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,5,10,11,17,18,24,25','1,2,8,9,15,16,22,23,29*,30,31'),
(10,2008,'1,2,3,4,5,6,7,8,12,13,19,20,26,27','2,3,9,10,16,17,22*,23,24,25','1,2,7*,8,9,10,15,16,22,23,29,30','5,6,12,13,19,20,26,27,30*','1,2,3,8*,9,10,11,17,18,24,25,31','1,8,11*,12,13,14,15,21,22,28,29','5,6,12,13,19,20,26,27','2,3,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28','4,5,11,12,18,19,25,26','1*,2,3,4,8,9,15,16,22,23,29,30','6,7,13,14,20,21,27,28,31*'),
(11,2009,'1,2,3,4,5,6,7,8,9,10,17,18,24,25,31','1,7,8,14,15,21,22,23,28','1,7,8,9,14,15,21,22,28,29','4,5,11,12,18,19,25,26','1,2,3,8*,9,10,11,16,17,23,24,30,31','6,7,11*,12,13,14,20,21,27,28','4,5,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','5,6,12,13,19,20,26,27','3,4,10,11,17,18,24,25,31','1,3*,4,7,8,14,15,21,22,28,29','5,6,12,13,19,20,26,27,31*'),
(12,2010,'1,2,3,4,5,6,7,8,9,10,16,17,23,24,30,31','6,7,13,14,20,21,22,23,27*,28','6,7,8,13,14,20,21,27,28','3,4,10,11,17,18,24,25,30*','1,2,3,8,9,10,15,16,22,23,29,30','5,6,11*,12,13,14,19,20,26,27','3,4,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','2,3,9,10,16,17,23,24,30,31','3*,4,5,6,7,14,20,21,27,28','4,5,11,12,18,19,25,26,31*'),
(13,2011,'1,2,3,4,5,6,7,8,9,10,15,16,22,23,29,30','5,6,12,13,19,20,22*,23,26,27','5*,6,7,8,12,13,19,20,26,27','2,3,9,10,16,17,23,24,30','1,2,7,8,9,14,15,21,22,28,29','4,5,11,12,13,18,19,25,26','2,3,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28','3,4,10,11,17,18,24,25','1,2,8,9,15,16,22,23,29,30','3*,4,5,6,12,13,19,20,26,27','3,4,10,11,17,18,24,25,31'),
(14,2012,'1,2,3,4,5,6,7,8,9,14,15,21,22,28,29','4,5,11,12,18,19,22*,23,25,26','3,4,7*,8,9,10,17,18,24,25,31','1,7,8,14,15,21,22,28*,29,30','1,6,7,8,9,12*,13,19,20,26,27','2,3,9*,10,11,12,16,17,23,24,30','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,5,10,11,17,18,24,25','1,2,8,9,15,16,22,23,29*,30,31'),
(15,2013,'1,2,3,4,5,6,7,8,12,13,19,20,26,27','2,3,9,10,16,17,22*,23,24','2,3,7*,8,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28','1,2,3,4,5,8*,9,10,11,12,18,19,25,26','1,2,8,9,11*,12,15,16,22,23,29,29,30','6,7,13,14,20,21,27,28','3,4,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29','5,6,12,13,19,20,26,27','2,3,4,9,10,16,17,23,24,30','1,7,8,14,15,21,22,28,29,31*'),
(16,2014,'1,2,3,4,5,6,7,8,11,12,18,19,25,26','1,2,8,9,15,16,22,23,24*','1,2,7*,8,9,10,15,16,22,23,29,30','5,6,12,13,19,20,26,27,30*','1,2,3,4,8*,9,10,11,17,18,24,25,31','1,7,8,11*,12,13,14,15,21,22,28,29','5,6,12,13,19,20,26,27','2,3,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28','4,5,11,12,18,19,25,26','1,2,3,4,8,9,15,16,22,23,29,30','6,7,13,14,20,21,27,28'),
(17,2015,'1,2,3,4,5,6,7,8,9,10,11,17,18,24,25,31','1,7,8,14,15,21,22,23,28','1,7,8,9,14,15,21,22,28,29','4,5,11,12,18,19,25,26,30*','1,2,3,4,8*,9,10,11,16,17,23,24,30,31','6,7,11*,12,13,14,20,21,27,28','4,5,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','5,6,12,13,19,20,26,27','3,4,10,11,17,18,24,25,31','1,3*,4,7,8,14,15,21,22,28,29','5,6,12,13,19,20,26,27,31*'),
(18,2016,'1,2,3,4,5,6,7,8,9,10,16,17,23,24,30,31','6,7,13,14,20*,21,22,23,27,28','5,6,7,8,12,13,19,20,26,27','2,3,9,10,16,17,23,24,30','1,2,3,7,8,9,14,15,21,22,28,29','4,5,11,12,13,18,19,25,26','2,3,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28','3,4,10,11,17,18,24,25','1,2,8,9,15,16,22,23,29,30','3*,4,5,6,12,13,19,20,26,27','3,4,10,11,17,18,24,25,31'),
(19,2017,'1,2,3,4,5,6,7,8,9,14,15,21,22,28,29','4,5,11,12,18,19,22*,23,24,25,26','4,5,7*,8,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','1,6,7,8,9,13,14,20,21,27,28','3,4,10,11,12,17,18,24,25','1,2,8,9,15,16,22,23,29,30','5,6,12,13,19,20,26,27','2,3,9,10,16,17,23,24,30','1,7,8,14,15,21,22,28,29','3*,4,5,6,11,12,18,19,25,26','2,3,9,10,16,17,23,24,30,31'),
(20,2018,'1,2,3,4,5,6,7,8,9,10,13,14,20,21,27,28','3,4,10,11,17,18,22*,23,24,25','3,4,7*,8,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29,30*','1,5,6,8*,9,12,13,19,20,26,27','2,3,9,10,11*,12,16,17,23,24,30','1,7,8,14,15,21,22,28,29','4,5,11,12,18,19,25,26','1,2,8,9,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,5,10,11,17,18,24,25','1,2,8,9,15,16,22,23,29,30,31*'),
(21,2019,'1,2,3,4,5,6,7,8,9,10,12,13,19,20,26,27','2,3,9,10,16,17,22*,23,24,25','2,3,7*,8,9,10,16,17,23,24,30,31','6,7,13,14,20,21,27,28,30*','1*,4,5,8*,9,11,12,18,19,25,26','1,2,8,9,11*,12,15,16,22,23,29,30','6,7,13,14,20,21,27,28','3,4,10,11,17,18,24,25,31','1,7,8,14,15,21,22,28,29','5,6,12,13,19,20,26,27','2,3,4*,9,10,16,17,23,24,30','1,7,8,14,15,21,22,28,29,31*')) AS vtable 
([Код],[Год],[Январь],[Февраль],[Март],[Апрель],[Май],[Июнь],[Июль],[Август],[Сентябрь],[Октябрь],[Ноябрь],[Декабрь])
),
d as (
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,1 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Январь], ',') t
Union all
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,2 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Февраль], ',') t
Union all
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,3 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Март], ',') t
Union all
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,4 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Апрель], ',') t
Union all
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,5 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Май], ',') t
Union all
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,6 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Июнь], ',') t
Union all
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,7 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Июль], ',') t
Union all
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,8 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Август], ',') t
Union all
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,9 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Сентябрь], ',') t
Union all
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,10 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Октябрь], ',') t
Union all
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,11 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Ноябрь], ',') t
Union all
SELECT Cast(Ltrim(Rtrim([Год])) as int) [Year]
      ,12 [Month]
	 ,Cast(Ltrim(Rtrim(Replace(t.Item,'*',''))) as int) [DAY]
	 ,iif(t.Item like '%*%', 1, 0) IsWorkDay
  FROM [Calendar] a
    Cross apply [common].[fnSplitString]([Декабрь], ',') t
),
d_n as (
Select Cast(DATEFROMPARTS([Year], [Month], [DAY]) as date) [date_n], IsWorkDay
from d)
Update a
Set a.[isWorkDay]=IsNull(d_n.[isWorkDay], 1)
from [dbsm_essentials].[spr].[calendar] a left join d_n on a.[date_n] = d_n.[date_n]
Where a.[year] between 1999 and 2019


Наверное, что-то типа этого в FAQ нужно запилить (если этого там нет). Часто всплывает вопрос.

К сообщению приложен файл (data-20171211T1403-structure-20171211T1403.csv - 8Kb) cкачать
18 дек 17, 09:55    [21041675]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
iii2
Member

Откуда:
Сообщений: 202
Упс, забыл еще функцию.
+
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- Разворачивает строку, разделенную @separator в столбец
create function [common].[fnSplitString] 
(
    @str nvarchar(max), 
    @separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
    select 
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(@separator, @str)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(@separator, @str, b + 1)
    from tokens
    where b > 0
)
select
    p-1 ItemIndex,
    substring(
        @str, 
        a, 
        case when b > 0 then b-a ELSE LEN(@str) end) 
    AS Item
from tokens
);

Код - целиком унаследованный (правда кто-то правил под 2014 версию), так что мопед не мой :-)
Просто для иллюстрации, т.с.
18 дек 17, 10:00    [21041691]     Ответить | Цитировать Сообщить модератору
 Re: еженедельное суммирование полей за 5-летний период  [new]
rhasta
Member

Откуда: Керчь
Сообщений: 10
Спасибо, пригодится
18 дек 17, 12:46    [21042255]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить