{"id":285843,"date":"2017-05-03T16:19:51","date_gmt":"2017-05-03T12:19:51","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=285843"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=285843","title":{"rendered":"\u042d\u043a\u0441\u043f\u0435\u0440\u0438\u043c\u0435\u043d\u0442\u044b \u043d\u0430\u0434 \u043e\u043b\u0438\u043c\u043f\u0438\u0430\u0434\u043d\u043e\u0439 \u0437\u0430\u0434\u0430\u0447\u0435\u0439"},"content":{"rendered":"

\u0422\u0430\u043a \u043f\u043e\u043b\u0443\u0447\u0438\u043b\u043e\u0441\u044c, \u0447\u0442\u043e \u044f \u043f\u043e\u043f\u0430\u043b \u0432 \u043c\u0430\u0433\u0438\u0441\u0442\u0440\u0430\u0442\u0443\u0440\u0443, \u0438 \u043a\u0430\u043a \u0442\u043e \u0433\u0443\u043b\u044f\u044f \u043c\u0438\u043c\u043e \u043a\u0430\u0444\u0435\u0434\u0440\u044b \u043d\u0430 \u0433\u043b\u0430\u0437\u0430 \u043f\u043e\u043f\u0430\u043b\u0430\u0441\u044c \u043e\u043b\u0438\u043c\u043f\u0438\u0430\u0434\u043d\u0430\u044f \u0437\u0430\u0434\u0430\u0447\u0430 \u043f\u043e 1\u0421. \u041a\u0440\u0430\u0442\u043a\u043e \u0437\u0430\u0434\u0430\u0447\u0430 \u0437\u0432\u0443\u0447\u0438\u0442 \u0442\u0430\u043a: \u00ab\u0415\u0441\u0442\u044c \u0437\u0430\u043f\u0438\u0441\u0438 \u043f\u0440\u043e\u0434\u0430\u0436\u0438 \u0437\u0430 \u043a\u0430\u0436\u0434\u044b\u0439 \u0434\u0435\u043d\u044c, \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u043d\u0430\u0439\u0442\u0438 \u043d\u0430\u0438\u0431\u043e\u043b\u044c\u0448\u0438\u0439 \u043f\u0435\u0440\u0438\u043e\u0434 \u043a\u043e\u0433\u0434\u0430 \u043f\u043b\u0430\u043d \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u043b\u0441\u044f\u00bb. \u0410 \u043f\u043e\u0442\u043e\u043c \u043a\u043e\u0433\u0434\u0430 \u044f \u0433\u0443\u043b\u044f\u043b \u0441\u043e \u0441\u043f\u044f\u0449\u0435\u0439 \u0434\u043e\u0447\u043a\u043e\u0439 \u0443 \u043c\u0435\u043d\u044f \u0432\u0441\u0442\u0430\u0432 \u0432\u043e\u043f\u0440\u043e\u0441, \u0430 \u0441\u043a\u043e\u043b\u044c\u043a\u0438\u043c\u0438 \u0441\u043f\u043e\u0441\u043e\u0431\u0430\u043c\u0438 \u044d\u0442\u043e \u043c\u043e\u0436\u043d\u043e \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u043d\u0430 SQL. \u0420\u0435\u0448\u0435\u043d\u0438\u044f \u0431\u0443\u0434\u0443\u0442 \u043d\u0430 \u043e\u0441\u043d\u043e\u0432\u0435 MS SQL.
<\/a>
\u0421\u043e\u0437\u0434\u0430\u0434\u0438\u043c \u0442\u0430\u0431\u043b\u0438\u0446\u0443 \u0438 \u043d\u0430\u0447\u043d\u0435\u043c <\/p>\n

\u0422\u0435\u043a\u0441\u0442 SQL \u0441\u043a\u0440\u0438\u043f\u0442\u0430 \u043f\u043e \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u044b<\/b><\/p>\n
\n
CREATE TABLE [tmp].[forFindDate]( \t[date] [datetime] NOT NULL, \t[value] [int] NOT NULL,  CONSTRAINT [PK_forFindDate] PRIMARY KEY CLUSTERED  ( \t[date] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]  GO  INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170401', 10) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170402', 20) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170403', 20) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170404', 20) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170405', 10) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170406', 10) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170407', 30) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170408', 36) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170409', 35) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170410', 30) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170411', 30) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170412', 20) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170413', 10) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170414', 40) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170415', 40) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170416', 10) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170417', 50) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170418', 52) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170419', 53) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170420', 53) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170421', 50) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170422', 51) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170423', 52) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170424', 50) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170425', 50) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170426', 50) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170427', 10) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170428', 10) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170429', 10) INSERT INTO [tmp].[forFindDate] ([date], [value]) VALUES ('20170430', 10)  GO <\/code><\/pre>\n<\/div>\n<\/div>\n
  \u041f\u0435\u0440\u0432\u044b\u0439 \u0441\u043f\u043e\u0441\u043e\u0431<\/b> \u0447\u0435\u0440\u0435\u0437 \u0441\u043e\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u044f \u0441\u0430\u043c\u043e\u0433\u043e \u043d\u0430 \u0441\u0435\u0431\u044f (\u0447\u0435\u0440\u0435\u0437 join-\u044b).   <\/p>\n
SQL \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0447\u0435\u0440\u0435\u0437 join<\/b><\/p>\n
\n
declare @planValue int = 15  --\u0441\u0430\u043c\u044b\u0439 \u043e\u0447\u0435\u0432\u0438\u0434\u043d\u044b\u0439 left select \t \ttop 1 \t'\u0441\u0430\u043c \u043d\u0430 \u0441\u0435\u0431\u044f', \tBeginDate, \tDATEADD(Day, -1, EndDate) EndDate from (select  \t\ts.date BeginDate,  \t\tISNULL(min(e.date), '20170501') EndDate \tfrom [tmp].[forFindDate] s \t\tleft join [tmp].[forFindDate] e \t\t\ton \t\t\t\ts.date < e.date \t\t\tand \t\t\t\tnot e.value > @planValue \twhere \t\ts.value > @planValue \tgroup by \t\ts.date) tmp order by \tDATEDIFF(day, BeginDate, EndDate) desc  -- \u0438\u043b\u0438 \u043a\u0430\u043a \u043c\u043d\u0435 \u043d\u0440\u0430\u0432\u0438\u0442\u044c\u0441\u044f \u0431\u043e\u043b\u044c\u0448\u0435 \u0447\u0435\u0440\u0435\u0437 with  ;with periodDate1 as( \tselect  \t\ts.date BeginDate,  \t\tISNULL(min(e.date), '20170501') EndDate \tfrom [tmp].[forFindDate] s \t\tleft join [tmp].[forFindDate] e \t\t\ton \t\t\t\ts.date < e.date \t\t\tand \t\t\t\tnot e.value > @planValue \twhere \t\ts.value > @planValue \tgroup by \t\ts.date ) select  \ttop 1 \t'\u0441\u0430\u043c \u043d\u0430 \u0441\u0435\u0431\u044f (with left)' title, \tBeginDate, \tDATEADD(Day, -1, EndDate) EndDate from periodDate1 order by \tDATEDIFF(day, BeginDate, EndDate) desc  --\u043c\u0435\u043d\u044f\u0435\u043c \u043d\u0430 full  ;with periodDate2 as( \tselect  \t\ts.date BeginDate,  \t\tISNULL(min(e.date), '20170501') EndDate \tfrom [tmp].[forFindDate] s \t\tfull join [tmp].[forFindDate] e \t\t\ton \t\t\t\ts.date < e.date \t\t\tand \t\t\t\tnot e.value > @planValue \twhere \t\ts.value > @planValue \tgroup by \t\ts.date ) select  \ttop 1 \t'\u0441\u0430\u043c \u043d\u0430 \u0441\u0435\u0431\u044f (with full)' title, \tBeginDate, \tDATEADD(Day, -1, EndDate) EndDate from periodDate2 order by \tDATEDIFF(day, BeginDate, EndDate) desc --\u043c\u0435\u043d\u044f\u0435\u043c \u043d\u0430 right ;with periodDate3 as( \tselect  \t\tISNULL(max(s.date), '20170401') BeginDate, \t\te.date EndDate \tfrom [tmp].[forFindDate] s \t\tright join [tmp].[forFindDate] e \t\t\ton \t\t\t\ts.date < e.date \t\t\tand \t\t\t\tnot s.value > @planValue \twhere \t\te.value > @planValue \tgroup by \t\te.date ) select  \ttop 1 \t'\u0441\u0430\u043c \u043d\u0430 \u0441\u0435\u0431\u044f (with right)' title, \tDATEADD(Day, 1, BeginDate) BeginDate,  \tEndDate from periodDate3 order by \tDATEDIFF(day, BeginDate, EndDate) desc <\/code><\/pre>\n<\/div>\n<\/div>\n
  \u041f\u0440\u0438\u0447\u0435\u043c \u043f\u0440\u0438 \u043b\u044e\u0431\u043e\u043c join \u043f\u043e\u043b\u0443\u0447\u0438\u043c \u0430\u0431\u0441\u043e\u043b\u044e\u0442\u043d\u043e \u043e\u0434\u0438\u043d\u0430\u043a\u043e\u0432\u044b\u0439 \u043f\u043b\u0430\u043d (\u0447\u0435\u0440\u0435\u0437 NESTED LOOP (left outer join)).  <\/p>\n
\u041f\u043b\u0430\u043d\u044b \u0438\u0441\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u0447\u0435\u0440\u0435\u0437 join<\/b><\/p>\n

  
  
    <\/div>\n<\/div>\n
  \u0412\u0442\u043e\u0440\u043e\u0439 \u0441\u043f\u043e\u0441\u043e\u0431<\/b> \u0447\u0435\u0440\u0435\u0437 \u043a\u043e\u0440\u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441  <\/p>\n
SQL \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0447\u0435\u0440\u0435\u0437 \u043a\u043e\u0440\u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441<\/b><\/p>\n
\n
declare @planValue int = 15  ;with periodDate4 as( \tselect  \t\ts.date BeginDate,  \t\tISNULL((select DATEADD(DAY, -1, isNull(min(d.date), '20170501')) from [tmp].[forFindDate] d where d.date > s.date and not d.value > @planValue), '20170501') EndDate \tfrom  \t\t[tmp].[forFindDate] s \twhere \t\ts.value > @planValue\t\t\t ) select top 1  \t'\u043a\u043e\u0440\u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441', p.BeginDate, p.EndDate from  \tperiodDate4 p  order by  \tDATEDIFF(DAY,  p.BeginDate, p.EndDate) desc <\/code><\/pre>\n<\/div>\n<\/div>\n
  \u0412 \u0434\u0430\u043d\u043d\u043e\u043c \u0441\u043b\u0443\u0447\u0430\u0439 \u043c\u044b \u043f\u043e\u043b\u0443\u0447\u0430\u0435\u043c NESTED LOOP (inner join)).  <\/p>\n
\u041f\u043b\u0430\u043d\u044b \u0438\u0441\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u043a\u043e\u0440\u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u043e\u0433\u043e \u0437\u0430\u043f\u0440\u043e\u0441\u0430<\/b><\/p>\n
  <\/div>\n<\/div>\n
  \u0422\u0440\u0435\u0442\u0438\u0439 \u0441\u043f\u043e\u0441\u043e\u0431<\/b> \u043f\u043e\u043a\u0430 \u0432\u0441\u0435 \u043d\u0435 \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u043e, \u0442\u0435\u043f\u0435\u0440\u044c \u0432\u043e\u0437\u044c\u043c\u0435\u043c \u0444\u0443\u043d\u043a\u0446\u0438\u044e APPLY (\u043f\u043e\u044f\u0432\u0438\u043b\u0430\u0441\u044c \u0432 MS SQL 2005). \u0424\u0430\u043a\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043d\u0430 \u043a\u0430\u0436\u0434\u0443\u044e \u0441\u0442\u0440\u043e\u043a\u0443 \u0431\u0443\u0434\u0435\u043c \u0434\u0435\u043b\u0430\u0442\u044c \u043f\u043e\u0434 \u0437\u0430\u043f\u0440\u043e\u0441.  <\/p>\n
SQL \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0447\u0435\u0440\u0435\u0437 outer apply<\/b><\/p>\n
\n
declare @planValue int = 15  ;with periodDate5 as( \tselect  \t\ts.date BeginDate,  \t\tISNULL(min(e.date), '20170501') EndDate \tfrom [tmp].[forFindDate] s \t\touter apply \t\t( \t\t--\u043e\u0441\u043e\u0431\u0435\u043d\u043d\u043e\u0441\u0442\u044c\u044e \u0445\u0440\u0430\u043d\u0435\u043d\u0438\u043c \u0434\u0430\u043d\u043d\u044b\u0445 (\u0441\u043e\u0440\u0442\u0438\u0440\u043e\u0432\u043a\u0430 \u043f\u043e \u0434\u0430\u0442\u0435) \t\tselect \t\t\ttop 1 \t\t\t\tee.date \t\tfrom \t\t\t[tmp].[forFindDate] ee \t\twhere \t\t\t\ts.date < ee.date \t\t\tand \t\t\t\tnot ee.value > @planValue \t\t) e \twhere \t\ts.value > @planValue \tgroup by \t\ts.date )  select  \ttop 1 \t'sql 2005 apply' title, \tBeginDate, \tDATEADD(Day, -1, EndDate) EndDate from periodDate5 order by \tDATEDIFF(day, BeginDate, EndDate) desc <\/code><\/pre>\n<\/div>\n<\/div>\n
  \u0412 \u0434\u0430\u043d\u043d\u043e\u043c \u0441\u043b\u0443\u0447\u0430\u0439 \u043e\u043f\u044f\u0442\u044c \u043f\u043e\u043b\u0443\u0447\u0430\u0435\u043c NESTED LOOP (left outer join), \u043d\u043e \u044d\u0442\u043e\u0442 \u0441\u043f\u043e\u0441\u043e\u0431 \u0441\u0430\u043c\u044b\u0439 \u043e\u043f\u0442\u0438\u043c\u0430\u043b\u044c\u043d\u044b\u0439 \u043d\u0430 \u0434\u0430\u043d\u043d\u044b\u0439 \u043c\u043e\u043c\u0435\u043d\u0442 (\u043f\u043e \u043a\u0440\u0430\u0439\u043d\u0435 \u043c\u0435\u0440\u0435 \u0442\u0430\u043a \u0441\u0447\u0438\u0442\u0430\u0435\u0442 MS SQL).  <\/p>\n
\u041f\u043b\u0430\u043d\u044b \u0438\u0441\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f apply<\/b><\/p>\n
  <\/div>\n<\/div>\n
  \u041f\u043e\u043a\u0430 \u0432\u0441\u0435 \u0431\u044b\u043b\u043e \u0441\u043a\u0443\u0447\u043d\u043e \u0438 \u043e\u0431\u044b\u0434\u0435\u043d\u043d\u043e.<\/p>\n
  \u0427\u0435\u0442\u0432\u0435\u0440\u0442\u044b\u0439 \u0441\u043f\u043e\u0441\u043e\u0431<\/b> \u043f\u043e\u0438\u0433\u0440\u0430\u0435\u043c \u0441 \u0440\u0435\u043a\u0443\u0440\u0441\u0438\u0435\u0439: \u043a \u0442\u0435\u043a\u0443\u0449\u0435\u0439 \u0441\u0442\u0440\u043e\u043a\u0435 \u0431\u0443\u0434\u0435\u043c \u043a\u043b\u0435\u0438\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0435 \u0435\u0441\u043b\u0438 \u0434\u0430\u0442\u0430 \u043d\u0430 \u043e\u0434\u0438\u043d \u0434\u0435\u043d\u044c \u0441\u0442\u0430\u0440\u0448\u0435 \u0438 \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u0435\u0442\u0441\u044f \u043f\u043b\u0430\u043d \u043f\u0440\u043e\u0434\u0430\u0436. \u0422\u0430\u043a\u0438\u043c \u043e\u0431\u0440\u0430\u0437\u043e\u043c \u0431\u0443\u0434\u0435\u043c \u0440\u0430\u0441\u0448\u0438\u0440\u044f\u0442\u044c \u0438\u043d\u0442\u0435\u0440\u0432\u0430\u043b. \u0422.\u043a. \u0434\u0430\u043d\u043d\u044b\u0445 \u043d\u0435 \u043c\u043d\u043e\u0433\u043e, \u0442\u043e \u0434\u043b\u0438\u043d\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043d\u0435\u0431\u043e\u043b\u044c\u0448\u0430\u044f, \u043a\u0430\u043a \u0438 \u0433\u043b\u0443\u0431\u0438\u043d\u0430 \u0441\u0442\u0435\u043a\u0430 \u0432\u044b\u0437\u043e\u0432\u043e\u0432 (CTE recursive \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e \u043d\u0430\u0447\u0438\u043d\u0430\u044f \u0441 2005)  <\/p>\n
SQL \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0440\u0435\u043a\u0443\u0440\u0441\u0438\u044f<\/b><\/p>\n
\n
declare @planValue int = 15  ;with periodDate6 as ( \tselect \t\tf.date BeginDate, \t\tDATEADD(day, 1, f.date) EndDate \tfrom \t\t[tmp].[forFindDate] f \twhere \t\tf.date = '20170417' \tand \t\tf.value > @planValue \tunion all \tselect \t\tr.BeginDate, \t\tDATEADD(day, 1, f.date) EndDate \tfrom \t\t[tmp].[forFindDate] f \t\t\tinner join periodDate6 r \t\t\t\ton \t\t\t\t\tr.EndDate = f.Date \t\t\t\tand \t\t\t\t\tvalue > @planValue ) select  \ttop 1 \t'recursive' title, \tBeginDate, \tDATEADD(Day, -1, EndDate) EndDate from periodDate6 order by \tDATEDIFF(day, BeginDate, EndDate) desc <\/code><\/pre>\n<\/div>\n<\/div>\n
  <\/p>\n
\u041f\u043b\u0430\u043d\u044b \u0438\u0441\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u043a\u043e\u0440\u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u043e\u0433\u043e \u0437\u0430\u043f\u0440\u043e\u0441\u0430<\/b><\/p>\n
  <\/div>\n<\/div>\n
  \u041f\u044f\u0442\u044b\u0439 \u0441\u043f\u043e\u0441\u043e\u0431<\/b> \u043f\u0435\u0440\u0435\u0439\u0434\u0435\u043c \u043a \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044f\u043c MS SQL 2012 \u043a \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u043e\u0439 \u0444\u0443\u043d\u043a\u0446\u0438\u0439 LEAD. \u0424\u0443\u043d\u043a\u0446\u0438\u044f LEAD \u0432\u043e\u0437\u0432\u0440\u0430\u0449\u0430\u0435\u0442 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u0441\u043e \u0441\u0434\u0432\u0438\u0433\u043e\u043c \u0432 \u043f\u0440\u0435\u0434\u0435\u043b\u0430\u0445 \u0441\u0435\u043a\u0446\u0438\u0439 \u043f\u043e \u0441\u043e\u0440\u0442\u0438\u0440\u043e\u0432\u043a\u0435. \u0421\u043e\u0440\u0442\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0431\u0443\u0434\u0435\u043c \u043f\u043e \u0434\u0430\u0442\u0430\u043c, \u0441\u0435\u043a\u0446\u0438\u0439 \u0443 \u043d\u0430\u0441 \u0434\u0432\u0435 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0438 \u043d\u0435 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u043f\u043b\u0430\u043d\u0430, \u0447\u0442\u043e \u0431\u044b \u043d\u0430\u0439\u0442\u0438 \u043c\u0430\u043a\u0441\u0438\u043c\u0430\u043b\u044c\u043d\u044b\u0439 \u043f\u0435\u0440\u0438\u043e\u0434 \u043f\u043e\u0441\u0442\u0443\u043f\u0438\u043c \u0445\u0438\u0442\u0440\u043e: \u0431\u0443\u0434\u0435\u043c \u0438\u0441\u043a\u0430\u0442\u044c \u043f\u0440\u043e\u043f\u0443\u0441\u043a\u0438 \u0432 \u043d\u0435 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0438 \u043f\u043b\u0430\u043d\u0430, \u0442.\u0435. \u043a\u043e\u0433\u0434\u0430 \u043c\u044b \u043f\u043e\u0442\u043e\u043c \u0441\u0434\u0432\u0438\u043d\u0435\u043c \u0434\u0430\u0442\u044b \u043d\u0430\u0447\u0430\u043b\u0430 \u0432\u043f\u0435\u0440\u0435\u0434, \u0430 \u043a\u043e\u043d\u0435\u0446 \u043d\u0430\u0437\u0430\u0434, \u0442\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u043c \u043e\u0442\u0440\u0435\u0437\u043a\u0438 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u043f\u043b\u0430\u043d\u0430.  <\/p>\n
SQL \u0437\u0430\u043f\u0440\u043e\u0441\u044b LEAD<\/b><\/p>\n
\n
declare @planValue int = 15  ;with periodDate7 as ( \tselect \t\tdate beforDate, \t\tlead(f.date, 1, '20170501') \t\tOVER ( PARTITION BY iif(f.value < @planValue, 1, 0) order by f.date) afterDate  \tfrom [tmp].[forFindDate] f ) select  \ttop 1 \t'func 2012' title, \tDATEADD(Day, 1, beforDate) BeginDate, \tDATEADD(Day, -1, afterDate) EndDate from periodDate7 order by \tDATEDIFF(day, beforDate, afterDate) desc <\/code><\/pre>\n<\/div>\n<\/div>\n
  \u041d\u0430 \u043f\u043b\u0430\u043d\u0435 \u0438\u0441\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0432\u0438\u0434\u043d\u043e, \u0447\u0442\u043e \u043d\u0435 \u043f\u0440\u043e\u0438\u0441\u0445\u043e\u0434\u0438\u0442 \u0441\u043e\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446  <\/p>\n
\u041f\u043b\u0430\u043d\u044b \u0438\u0441\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f LEAD<\/b><\/p>\n
  <\/div>\n<\/div>\n
  \u0428\u0435\u0441\u0442\u043e\u0439 \u0441\u043f\u043e\u0441\u043e\u0431<\/b> \u0410 \u0442\u0435\u043f\u0435\u0440\u044c \u043f\u043e\u0431\u0430\u043b\u0443\u0435\u043c\u0441\u044f \u043f\u0435\u0440\u0435\u043c\u043d\u043e\u0436\u0438\u043c \u0442\u0430\u0431\u043b\u0438\u0446\u0443 \u0441\u0430\u043c\u0443 \u043d\u0430 \u0441\u0435\u0431\u044f: \u043d\u0430\u0439\u0434\u0435\u043c \u0432\u0441\u0435 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u044b\u0435 \u0441\u043e\u0447\u0435\u0442\u0430\u043d\u0438\u044f \u0434\u0430\u0442 (30*30 = 900), \u043e\u0442\u0431\u0435\u0440\u0435\u043c \u0442\u0435 \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0434\u0430\u0442\u0430 \u043d\u0430\u0447\u0430\u043b\u043e \u043c\u0435\u043d\u044c\u0448\u0435 \u0434\u0430\u0442\u044b \u043a\u043e\u043d\u0446\u0430 \u0438 \u0432 \u044d\u0442\u043e\u043c \u0438\u043d\u0442\u0435\u0440\u0432\u0430\u043b\u0435 \u043d\u0435\u0442 \u0441\u043e\u0431\u044b\u0442\u0438\u044f \u043d\u0435 \u0438\u0441\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u043f\u043b\u0430\u043d\u0430.  <\/p>\n
SQL \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0440\u0435\u043a\u0443\u0440\u0441\u0438\u044f<\/b><\/p>\n
\n
declare @planValue int = 15  ;with periodDate8 as( \tselect  \t\tISNULL(s.date, '20170401') BeginDate,  \t\tISNULL(e.date, '20170501') EndDate \tfrom [tmp].[forFindDate] s, [tmp].[forFindDate] e ) select top 1  \t'for Fun', p.BeginDate, p.EndDate from periodDate8 p  \tleft join [tmp].[forFindDate] b \t\ton \t\t\tb.date between p.BeginDate and p.EndDate \t\tand \t\t\tnot b.value > @planValue where \tp.BeginDate < p.EndDate and \tb.date is null order by \tDATEDIFF(day, BeginDate, EndDate) desc <\/code><\/pre>\n<\/div>\n<\/div>\n
  \u0421\u0430\u043c\u044b\u0439 \u043c\u0435\u0434\u043b\u0435\u043d\u043d\u044b\u0439 \u0438 \u0442\u044f\u0436\u0435\u043b\u044b\u0439 \u0441\u043f\u043e\u0441\u043e\u0431. \u041d\u043e \u043c\u044b \u0433\u043e\u043d\u0438\u043c\u0441\u044f \u043d\u0435 \u0437\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e, \u0430 \u0437\u0430 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0440\u0435\u0448\u0435\u043d\u0438\u0439 \u0437\u0430\u0434\u0430\u0447\u0438.  <\/p>\n
\u041f\u043b\u0430\u043d\u044b \u0438\u0441\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u043f\u0435\u0440\u0435\u043c\u043d\u043e\u0436\u0435\u043d\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u044b<\/b><\/p>\n
  <\/div>\n<\/div>\n
  \u0421\u0435\u0434\u044c\u043c\u043e\u0439 \u0441\u043f\u043e\u0441\u043e\u0431<\/b> \u041e\u0431\u044b\u0447\u043d\u044b\u0439 \u043a\u0443\u0440\u0441\u043e\u0440. \u041f\u043e \u043f\u0435\u0440\u0435\u0431\u0438\u0440\u0430\u0435\u043c \u0434\u0430\u043d\u043d\u044b\u0435 \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043f\u043b\u0430\u043d \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d, \u0438 \u0438\u0449\u0435\u043c \u043c\u0430\u043a\u0441\u0438\u043c\u0430\u043b\u044c\u043d\u044b\u0439 \u043f\u043e\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u043d\u044b\u0439 \u0443\u0447\u0430\u0441\u0442\u043e\u043a.  <\/p>\n
SQL \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u043a\u0443\u0440\u0441\u043e\u0440\u043e\u043c<\/b><\/p>\n
\n
declare @planValue int = 15 declare @endDate datetime = null,  \t\t@Intervat int = 0, \t\t@maxIntervat int = 0,  \t\t@old_date datetime = '20170401',  \t\t@cur_date datetime  DECLARE date_cursor CURSOR       FOR SELECT date FROM [tmp].[forFindDate] where value > @planValue \tOPEN date_cursor   \t\tFETCH NEXT FROM date_cursor  \t\tINTO @cur_date   \t\tWHILE @@FETCH_STATUS = 0   \t\t\tBEGIN  \t\t\tIF (@cur_date = DATEADD(DAY, 1, @old_date)) BEGIN \t\t\t\tSET @Intervat = @Intervat + 1 \t\t\t\tIF(@Intervat > @maxIntervat) BEGIN \t\t\t\t\tSET @maxIntervat = @Intervat \t\t\t\t\tSET @endDate = @cur_date \t\t\t\tEND \t\t\tEND else  \t\t\tBegin \t\t\t\tset @Intervat = 0 \t\t\tend \t\t\tSET @old_date = @cur_date \t\t\tFETCH NEXT FROM date_cursor  \t\t\tINTO @cur_date \t\tEND    \tCLOSE date_cursor;   DEALLOCATE date_cursor;    select 'cursor' title, DATEADD(DAY, - @maxIntervat, @endDate) BeginDate, @endDate EndDate <\/code><\/pre>\n<\/div>\n<\/div>\n
  \u041f\u043e\u043a\u0430 \u044d\u0442\u043e \u0432\u0441\u0435, \u0447\u0442\u043e \u0441\u043c\u043e\u0433 \u043f\u0440\u0438\u0434\u0443\u043c\u0430\u0442\u044c. \u0416\u0434\u0443 \u0435\u0449\u0435 \u0432\u0430\u0440\u0438\u0430\u043d\u0442\u043e\u0432 \u0432 \u043a\u043e\u043c\u043c\u0435\u043d\u0442\u0430\u0440\u0438\u044f\u0445.
 \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438  https:\/\/habrahabr.ru\/post\/327862\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
\u0422\u0430\u043a \u043f\u043e\u043b\u0443\u0447\u0438\u043b\u043e\u0441\u044c, \u0447\u0442\u043e \u044f \u043f\u043e\u043f\u0430\u043b \u0432 \u043c\u0430\u0433\u0438\u0441\u0442\u0440\u0430\u0442\u0443\u0440\u0443, \u0438 \u043a\u0430\u043a \u0442\u043e \u0433\u0443\u043b\u044f\u044f \u043c\u0438\u043c\u043e \u043a\u0430\u0444\u0435\u0434\u0440\u044b \u043d\u0430 \u0433\u043b\u0430\u0437\u0430 \u043f\u043e\u043f\u0430\u043b\u0430\u0441\u044c \u043e\u043b\u0438\u043c\u043f\u0438\u0430\u0434\u043d\u0430\u044f \u0437\u0430\u0434\u0430\u0447\u0430 \u043f\u043e 1\u0421. \u041a\u0440\u0430\u0442\u043a\u043e \u0437\u0430\u0434\u0430\u0447\u0430 \u0437\u0432\u0443\u0447\u0438\u0442 \u0442\u0430\u043a: \u00ab\u0415\u0441\u0442\u044c \u0437\u0430\u043f\u0438\u0441\u0438 \u043f\u0440\u043e\u0434\u0430\u0436\u0438 \u0437\u0430 \u043a\u0430\u0436\u0434\u044b\u0439 \u0434\u0435\u043d\u044c, \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u043d\u0430\u0439\u0442\u0438 \u043d\u0430\u0438\u0431\u043e\u043b\u044c\u0448\u0438\u0439 \u043f\u0435\u0440\u0438\u043e\u0434 \u043a\u043e\u0433\u0434\u0430 \u043f\u043b\u0430\u043d \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u043b\u0441\u044f\u00bb. \u0410 \u043f\u043e\u0442\u043e\u043c \u043a\u043e\u0433\u0434\u0430 \u044f \u0433\u0443\u043b\u044f\u043b \u0441\u043e \u0441\u043f\u044f\u0449\u0435\u0439 \u0434\u043e\u0447\u043a\u043e\u0439 \u0443 \u043c\u0435\u043d\u044f \u0432\u0441\u0442\u0430\u0432 \u0432\u043e\u043f\u0440\u043e\u0441, \u0430 \u0441\u043a\u043e\u043b\u044c\u043a\u0438\u043c\u0438 \u0441\u043f\u043e\u0441\u043e\u0431\u0430\u043c\u0438 \u044d\u0442\u043e \u043c\u043e\u0436\u043d\u043e \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u043d\u0430 SQL. \u0420\u0435\u0448\u0435\u043d\u0438\u044f \u0431\u0443\u0434\u0443\u0442 \u043d\u0430 \u043e\u0441\u043d\u043e\u0432\u0435 MS SQL.  <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-285843","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/285843","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=285843"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/285843\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=285843"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=285843"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=285843"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}