ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / еженедельное суммирование полей за 5-летний период
18 сообщений из 18, страница 1 из 1
еженедельное суммирование полей за 5-летний период
    #39569792
rhasta
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rhasta
Гость
Всем доброго врмени суток!
У меня возник вопрос такого рода. Делаю джойн двух таблиц - получаю даты и суммы, около 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:07:28
| Ответить | Цитировать | Написать  
еженедельное суммирование полей за 5-летний период
    #39569824
EliDergun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
EliDergun
Гость
Можно решит в лоб, не корректно, но решить.

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

Я бы завел табличку-календарь, присоединил бы и группировал бы по нужному полю из календаря.
...
Рейтинг: 0 / 0
14.12.2017, 17:50:34
| Ответить | Цитировать | Написать  
еженедельное суммирование полей за 5-летний период
    #39569869
rhasta
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rhasta
Гость
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:05:07
| Ответить | Цитировать | Написать  
еженедельное суммирование полей за 5-летний период
    #39569874
rhasta
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rhasta
Гость
iiyamarhasta,

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

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

Я бы завел табличку-календарь, присоединил бы и группировал бы по нужному полю из календаря.
У меня совсем критична производительность, поэтому низзя вот так разбрасываться на временные таблицыДополнительная таблица обычно повышает производительность, а не понижает, потому что она хранит данные, которые в противном случае пришлось бы вычислять при каждом запросе.
А вообще для высокого быстродействия хранят агрегаты, а не вычисляют их каждый раз для отчёта.
...
Рейтинг: 0 / 0
15.12.2017, 10:07:56
| Ответить | Цитировать | Написать  
еженедельное суммирование полей за 5-летний период
    #39570228
rhasta
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rhasta
Гость
И это я тоже учту
...
Рейтинг: 0 / 0
15.12.2017, 11:46:37
| Ответить | Цитировать | Написать  
еженедельное суммирование полей за 5-летний период
    #39570398
rhasta
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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.
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, 15:48:19
| Ответить | Цитировать | Написать  
еженедельное суммирование полей за 5-летний период
    #39570495
qwertEHOK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwertEHOK
Гость
кто там писал про производительность?
...
Рейтинг: 0 / 0
15.12.2017, 18:02:13
| Ответить | Цитировать | Написать  
еженедельное суммирование полей за 5-летний период
    #39571174
rhasta
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rhasta
Гость
А что сней пока не так?Я имею в виду - производительность?
...
Рейтинг: 0 / 0
18.12.2017, 09:21:30
| Ответить | Цитировать | Написать  
еженедельное суммирование полей за 5-летний период
    #39571189
iii2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iii2
Гость
Вот, кстати, табличка с датами, может кому пригодится:
Источник 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, 09:55:19
| Ответить | Цитировать | Написать  
еженедельное суммирование полей за 5-летний период
    #39571190
iii2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iii2
Гость
Упс, забыл еще функцию.
 
Код: 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, 10:00:39
| Ответить | Цитировать | Написать  
еженедельное суммирование полей за 5-летний период
    #39571325
rhasta
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rhasta
Гость
Спасибо, пригодится
...
Рейтинг: 0 / 0
18.12.2017, 12:46:29
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / еженедельное суммирование полей за 5-летний период
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=173&tid=1690654]:
 0ms
get settings:
 9ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 53ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 64ms
get tp. blocked users:
 2ms
others: 224ms
total:  390ms
созд. / загр.: 390ms
x
x
Закрыть


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