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

Откуда:
Сообщений: 107
Всем хорошего дня. Стоит задачка, мозг вскипает.
Есть 2 таблицы:
1. с выходными днями в году (обновляется периодически), просто один datetime, если надо можно изменить.
2. таблица со всеми датами за несколько лет

Нужно создать таблицу, в которой будет дата и номер десятидневки, за вычетом дат из первой таблицы с выходными.
К примеру:
10 января первый раб день в этом году и имеет номер 1. Номер 2 начинается с 24 января. Нужен сквозной просчет до конца года.
Какие мысли по этому поводу?
Можно банально вбить вручную, но выходные в нашей стране такая не постоянная вещь :)
Заранее прошу прощения, если все окажется тупо до безобразия.
sql 2008 r2
19 янв 12, 12:16    [11931296]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
Glory
Member

Откуда:
Сообщений: 104751
syavik
Какие мысли по этому поводу?
Можно банально вбить вручную, но выходные в нашей стране такая не постоянная вещь :)

Ну так создайте календарь и банально разметьте его.
19 янв 12, 12:22    [11931369]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
syavik
Member

Откуда:
Сообщений: 107
Glory
syavik
Какие мысли по этому поводу?
Можно банально вбить вручную, но выходные в нашей стране такая не постоянная вещь :)

Ну так создайте календарь и банально разметьте его.


Так в этом и вопрос, как мне поставить просчет автоматом?
19 янв 12, 12:29    [11931462]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
Glory
Member

Откуда:
Сообщений: 104751
syavik
Так в этом и вопрос, как мне поставить просчет автоматом?

Ну так отметьте все праздники в своем календаре и просчитайте
19 янв 12, 12:33    [11931499]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
SELECT [Day],[Decade]=ROW_NUMBER()OVER(ORDER BY [Day])/10
FROM Dates
WHERE [Day] NOT IN(SELECT [Day] FROM DayOff);
???
19 янв 12, 12:43    [11931606]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Единицу забыл
iap
SELECT [Day],[Decade]=1+ROW_NUMBER()OVER(ORDER BY [Day])/10
FROM Dates
WHERE [Day] NOT IN(SELECT [Day] FROM DayOff);
???
19 янв 12, 12:43    [11931616]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
syavik
Member

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

Хорошо, предположим есть таблица
#t (date datetime, holiday int)
insert into #t select '20120101', '1'
insert into #t select '20120110', '0'
и так далее.

Как мне посчитать что 2012-02-10 = 2 ?
19 янв 12, 12:45    [11931632]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Совсем плох стал...
SELECT [Day],[Decade]=1+(ROW_NUMBER()OVER(ORDER BY [Day])-1)/10
FROM Dates
WHERE [Day] NOT IN(SELECT [Day] FROM DayOff);
19 янв 12, 12:45    [11931641]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
syavik
Member

Откуда:
Сообщений: 107
iap
Совсем плох стал...
SELECT [Day],[Decade]=1+(ROW_NUMBER()OVER(ORDER BY [Day])-1)/10
FROM Dates
WHERE [Day] NOT IN(SELECT [Day] FROM DayOff);

Сейчас покрутим, спасибо!
19 янв 12, 12:47    [11931660]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
Glory
Member

Откуда:
Сообщений: 104751
syavik
Хорошо, предположим есть таблица
#t (date datetime, holiday int)
insert into #t select '20120101', '1'
insert into #t select '20120110', '0'
и так далее.

Это не календарь
Вот как должна выглядеть таблица
insert into #t select '20120101', '1'
insert into #t select '20120102', '0'
insert into #t select '20120103', '0'
insert into #t select '20120104', '0'
insert into #t select '20120105', '0'
insert into #t select '20120106', '0'
insert into #t select '20120107', '0'
insert into #t select '20120108', '0'
insert into #t select '20120109', '0'
insert into #t select '20120110', '0'

А как пронумеровать эти даты группами по 10, исключая праздники, вам уже показали
19 янв 12, 12:49    [11931683]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
уже всё написали, ну да ладно
declare
  @dt1 date = '20120101',
  @dt2 date = '20121231';

declare @dates table (date date not null primary key);
declare @holidays table (date date not null primary key);

with A as
(
  select date = @dt1
   where @dt1 <= @dt2
  union all
  select dateadd(day, 1, date)
    from A
   where date < @dt2
)
insert into @dates(date)
select date
  from A
option (maxrecursion 0);

insert into @holidays(date)
select date
  from @dates
 where datediff(day, '', date) % 7 in (5, 6);

select A.date,
       rn = row_number() over(partition by year(A.date) order by A.date) / 10 + 1
  from @dates A
  left join @holidays B on A.date = B.date
 where B.date is null;
19 янв 12, 12:51    [11931700]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
syavik
Member

Откуда:
Сообщений: 107
syavik
iap
Совсем плох стал...
SELECT [Day],[Decade]=1+(ROW_NUMBER()OVER(ORDER BY [Day])-1)/10
FROM Dates
WHERE [Day] NOT IN(SELECT [Day] FROM DayOff);

Сейчас покрутим, спасибо!


Спасибо, работает, только идет сквозная нумерация с первой записи по последнюю, а можно это сделать с группировкой по году (что бы нумерация сбрасывалась с началом года)? В календаре есть отдельный столбец с годом.
19 янв 12, 12:59    [11931789]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
syavik
Member

Откуда:
Сообщений: 107
Glory
syavik
Хорошо, предположим есть таблица
#t (date datetime, holiday int)
insert into #t select '20120101', '1'
insert into #t select '20120110', '0'
и так далее.

Это не календарь
Вот как должна выглядеть таблица
insert into #t select '20120101', '1'
insert into #t select '20120102', '0'
insert into #t select '20120103', '0'
insert into #t select '20120104', '0'
insert into #t select '20120105', '0'
insert into #t select '20120106', '0'
insert into #t select '20120107', '0'
insert into #t select '20120108', '0'
insert into #t select '20120109', '0'
insert into #t select '20120110', '0'

А как пронумеровать эти даты группами по 10, исключая праздники, вам уже показали



Ну у меня календарь выглядит так:
declare @t table (dt smalldatetime)
declare @dt1 datetime, @dt2 datetime
set @dt1='20050101'
set @dt2='20200110'

while @dt1<=@dt2 begin
insert into @t select @dt1
set @dt1=DATEADD(day, 1, @dt1)
end
create table #t (d smalldatetime)
insert into #t
select dt from @t
select * from #t


set language Russian
create table #t1
(
[Дата] datetime,
[Год] int,
[Квартал]int,
[N_of_month]int,
[Месяц]varchar (20),
[Неделя]int,
[День месяца] int,
[День недели] varchar (20)
)
insert into #t1
select d,
YEAR(d) [year],
datepart(qq,d) [quarter],
datepart(MONTH,d) [month],
DATENAME(month, d) [MonthName],
datepart(week,d) [Week],
datepart(day,d) [Day],
DATENAME(weekday,d)[DayName]
from #t

select * from #t1
19 янв 12, 13:02    [11931811]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
syavik
syavik
пропущено...

Сейчас покрутим, спасибо!


Спасибо, работает, только идет сквозная нумерация с первой записи по последнюю, а можно это сделать с группировкой по году (что бы нумерация сбрасывалась с началом года)? В календаре есть отдельный столбец с годом.
SELECT [Day],[Decade]=1+(ROW_NUMBER()OVER(PARTITION BY [Year] ORDER BY [Day])-1)/10
FROM Dates
WHERE [Day] NOT IN(SELECT [Day] FROM DayOff);
19 янв 12, 13:23    [11932115]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
syavik
Member

Откуда:
Сообщений: 107
Зайцев Фёдор,

Вариант хороший, но на сколько понимаю, он не может учитывать рабочих выходных (сб, вс)?
19 янв 12, 13:23    [11932117]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Зайцев Фёдор
уже всё написали, ну да ладно
declare
  @dt1 date = '20120101',
  @dt2 date = '20121231';

declare @dates table (date date not null primary key);
declare @holidays table (date date not null primary key);

with A as
(
  select date = @dt1
   where @dt1 <= @dt2
  union all
  select dateadd(day, 1, date)
    from A
   where date < @dt2
)
insert into @dates(date)
select date
  from A
option (maxrecursion 0);

insert into @holidays(date)
select date
  from @dates
 where datediff(day, '', date) % 7 in (5, 6);

select A.date,
       rn = row_number() over(partition by year(A.date) order by A.date) / 10 + 1
  from @dates A
  left join @holidays B on A.date = B.date
 where B.date is null;
IMHO, ошибка в нумерации.
Ведь первые 9 записей дадут 1, а десятая - 2.
Из-за того, что ROW_NUMBER() нумерует с единицы, а не с нуля
19 янв 12, 13:26    [11932145]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
syavik
Зайцев Фёдор,

Вариант хороший, но на сколько понимаю, он не может учитывать рабочих выходных (сб, вс)?

это не настоящие данные о выходных, а демонстрационные
19 янв 12, 13:26    [11932148]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
iap
IMHO, ошибка в нумерации.
Ведь первые 9 записей дадут 1, а десятая - 2.
Из-за того, что ROW_NUMBER() нумерует с единицы, а не с нуля

согласен, явный косяк
19 янв 12, 13:28    [11932172]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
syavik
Member

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

Огромнейшее спасибо, именно то, что нужно!
19 янв 12, 13:29    [11932191]     Ответить | Цитировать Сообщить модератору
 Re: высчитать 10- днейвный период по условиям  [new]
syavik
Member

Откуда:
Сообщений: 107
Спасибо всем, кто откликнулся на проблему!
19 янв 12, 13:30    [11932209]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить