Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / еженедельное суммирование полей за 5-летний период / 18 сообщений из 18, страница 1 из 1
14.12.2017, 17:07:28
    #39569792
rhasta
rhasta
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
Всем доброго врмени суток!
У меня возник вопрос такого рода. Делаю джойн двух таблиц - получаю даты и суммы, около 1000 записей.
Получаю это "добро" таким кодом:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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 год. И все. А нужно за полный период.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 17:27:57
    #39569824
EliDergun
EliDergun
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
Можно решит в лоб, не корректно, но решить.

через day(date)
как только возвращаемое значение day(date) больше 7 то сумму обнулить и складывать заново
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 17:34:27
    #39569835
rhasta
rhasta
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
МОжно и не в лоб, главное, чтоб корректно было
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 17:40:25
    #39569850
Minamoto
Minamoto
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
rhasta, такое хотите?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 17:41:47
    #39569853
Руслан Дамирович
Руслан Дамирович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
Код: sql
1.
2.
3.
4.
5.
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() ) )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 17:50:34
    #39569860
iiyama
iiyama
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
rhasta,

Я бы завел табличку-календарь, присоединил бы и группировал бы по нужному полю из календаря.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 18:05:07
    #39569869
rhasta
rhasta
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
Minamotorhasta, такое хотите?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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 и выдало как я и ожидал понедельно
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 18:16:15
    #39569874
rhasta
rhasta
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
iiyamarhasta,

Я бы завел табличку-календарь, присоединил бы и группировал бы по нужному полю из календаря.
У меня совсем критична производительность, поэтому низзя вот так разбрасываться на временные таблицы
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 18:18:02
    #39569876
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
rhasta,

статический календарь достаточно полезная вещь. Ну и про производительность вы ничего не знаете если такое пишите
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.12.2017, 09:17:49
    #39570109
rhasta
rhasta
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
Скорее всего и не знаю про производительность...спорить не буду, особенно в том, в чем не уверен. Буду гуглить, что это за статический календарь и как с ним работать
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.12.2017, 10:07:56
    #39570142
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
rhastaiiyamarhasta,

Я бы завел табличку-календарь, присоединил бы и группировал бы по нужному полю из календаря.
У меня совсем критична производительность, поэтому низзя вот так разбрасываться на временные таблицыДополнительная таблица обычно повышает производительность, а не понижает, потому что она хранит данные, которые в противном случае пришлось бы вычислять при каждом запросе.
А вообще для высокого быстродействия хранят агрегаты, а не вычисляют их каждый раз для отчёта.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.12.2017, 11:46:37
    #39570228
rhasta
rhasta
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
И это я тоже учту
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.12.2017, 15:48:19
    #39570398
rhasta
rhasta
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
В итоге, по моему вопросу, чтобы учесть еженедельную статистику

Код: 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.
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)



Код выдает то, что я именно от него и хотел получить
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.12.2017, 18:02:13
    #39570495
qwertEHOK
qwertEHOK
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
кто там писал про производительность?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.12.2017, 09:21:30
    #39571174
rhasta
rhasta
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
А что сней пока не так?Я имею в виду - производительность?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.12.2017, 09:55:19
    #39571189
iii2
iii2
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
Вот, кстати, табличка с датами, может кому пригодится:
Источник http://data.gov.ru/opendata/resource/8ba5011a-233e-4e01-a1d2-ff5598d0f34f#2/0.0/0.0
А вот - его парсинг:
 

Код: 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.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
/****** 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 нужно запилить (если этого там нет). Часто всплывает вопрос.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.12.2017, 10:00:39
    #39571190
iii2
iii2
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
Упс, забыл еще функцию.
 
Код: 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.
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 версию), так что мопед не мой :-)
Просто для иллюстрации, т.с.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.12.2017, 12:46:29
    #39571325
rhasta
rhasta
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
еженедельное суммирование полей за 5-летний период
Спасибо, пригодится
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / еженедельное суммирование полей за 5-летний период / 18 сообщений из 18, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 473ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1690654]:
 0ms
get settings:
 7ms
get forum list:
 20ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 140ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 231ms
total:  473ms
x
x
Закрыть


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