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

Откуда:
Сообщений: 22
Добрый день, хочу попросить Вашей помощи в таком вопросе:
Существует таблица отпусков: Дата начала отпуска, дата конца, таб. номер сотрудника.
Промежуточная таблица: таб. номер, год(к примеру 2011,2012,2013).
Нужно получить кол-во дней нахождения на больничном и периоды в каждом из годов промежуточной таблицы, причем если сотрудник был на больничном с 01.11.2011 по 02.01.2012 то в результате должно быть так:
2011г
01.11.11 - 31.12.11 | 61день
2012г.
01.01.2012 - 02.01.2012 | 2 дня.


пробовал делать через case, где @DMin= 01.01.2011, @DMax=01.01.2013, nYear=2011,2012,2013

TabNum = tr.TabNum,
Dbeg = case when datepart(year,hol.DateBegin) < (select min(nYear) from #TRes) then @DMin else hol.DateBegin end,
Dend = case when datepart(year,hol.DateEnd) > (select max(nYear) from #TRes) then @Dmax else hol.DateEnd end,
SumDays = case when datepart(year,hol.DateBegin) < (select min(nYear) from #TRes) then datediff(day,@DMin,hol.DateEnd+1)
when datepart(year,hol.DateEnd) > (select max(nYear) from #TRes) then datediff(day,hol.DateBegin,@DMax+1)[/FIXED][/FIXED][/FIXED]


но это отрабатывает только если год начала отпуска не входит в nYear.
Как лучше поступить? Заранее спасибо!
24 июл 13, 10:05    [14608489]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2428
rukesa,

зачем новую тему? в старой бы написали.
напишите скрипт создания таблиц, ввода тестовых данных (здесь же, еще тему создавать не надо)
и приведите свой ПОЛНЫЙ запрос, а не отрывок
24 июл 13, 10:11    [14608542]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6727
Рекомендации по оформлению сообщений в форуме
Не написали скрипт тестовых данных.
24 июл 13, 10:17    [14608617]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Добрый Э - Эх
Guest
rukesa,

Чего-то на зал за здравие отпуска, кончил за упокой больничные
24 июл 13, 10:24    [14608679]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх
rukesa,

Чего-то на зал начал за здравие отпуска, кончил за упокой больничные
24 июл 13, 10:25    [14608685]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6727
Да, ладно, забей. Человек совершает тысячи ошибок в день. Хуже, когда настолько слеп, что не замечаешь их.
WITH [HoliDays] ([From],[To],[TID]) AS (SELECT Convert(Date,[From]),Convert(Date,[To]),TID FROM (VALUES
 ('20111101','20120101',1)
,('20111101','20120601',2)
,('20100301','20100501',3)
)hd([From],[To],[TID])
), [Years] ([Year],[From]) AS (SELECT Year([From]),Convert(Date,[From]) FROM (VALUES
 ('20100101')
,('20110101')
,('20120101')
,('20130101')
)y([From])
)	SELECT	 H.TID
		,Y.[Year]
		,CASE WHEN H.[From] >                Y.[From]  THEN H.[From] ELSE                Y.[From]  END	AS [From]
		,CASE WHEN H.[To]   < DateAdd(Year,1,Y.[From]) THEN H.[To]   ELSE DateAdd(Year,1,Y.[From]) END	AS [To]
	FROM	[HoliDays]	H
	JOIN	[Years]		Y ON Y.[From] >= DateAdd(Year,-1,H.[From])
				 AND Y.[From] <  H.[To]
;
И запомните - время непрерывно.
31.12.11 - выводите так в интерфейсе, но внутрях должны фиксироваться моменты времени (2012-01-01 00:00:00.0000000) и не важно, с какой точностью тип хранения. И не важно "реальность" - главное модель.

И не думайте что вы заметили все те 42 принципа, которые использовались в написании запроса. Главное постепенно их впитывать.
24 июл 13, 10:49    [14608837]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6727
Согласен, так организовать таблицы (вычисляемые колонки) удобнее:
WITH [HoliDays] ([From],[To],[TID]) AS (SELECT Convert(Date,[From]),Convert(Date,[To]),TID FROM (VALUES
 ('20111101','20120101',1)
,('20111101','20120601',2)
,('20100301','20100501',3)
)hd([From],[To],[TID])
), [Years] ([Year],[From],[To]) AS (SELECT Year([From]),Convert(Date,[From]),DateAdd(Year,1,Convert(Date,[From])) FROM (VALUES
 ('20100101')
,('20110101')
,('20120101')
,('20130101')
)y([From])
)	SELECT	 H.TID
		,Y.[Year]
		,CASE WHEN H.[From] > Y.[From] THEN H.[From] ELSE Y.[From] END	AS [From]
		,CASE WHEN H.[To]   < Y.[To]   THEN H.[To]   ELSE Y.[To]   END	AS [To]
	FROM	[HoliDays]	H
	JOIN	[Years]		Y ON Y.[From] >= DateAdd(Year,-1,H.[From])
				 AND Y.[From] <  H.[To]
;
24 июл 13, 10:54    [14608867]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
rukesa
Member

Откуда:
Сообщений: 22
не совсем понял 2 последних сообщения и как это относится к моему вопросу...
перепутал больничные и отпуска, так мне нужно будет и то и то, без разницы на чем пробовать.
исходные таблицы:
---------Таблица год, сотрудник             + также есть две известные переменные @DMin = '20110101' и @DMax = '20130101', --может они пригодятся
Create table #TRes (nYear int, TabNum varchar(15))

insert #TRes values(2011,'123')
insert #TRes values(2012,'123')
insert #TRes values(2013,'123')

---------Таблица больничных(дата начала, дата окончания, сотрудник)
create table #Ill (DateBegin datetime, DateEnd datetime, TabNum varchar(15))
insert #TRes values('20111204','20120301','123')
insert #TRes values('20110901','20130201','123')
insert #TRes values('20121101','20121201','123')
--Нужно получить  следующий select: Год, дата начала периода отсутствия,дата конца периода отсутствия, кол-во дней --отсутствия. 
select
  Year        = ?
  DBeg       = ?
  DEnd       = ?
  SumDays  = ?
From
  #TRes tr
  Join  #Ill il
    On il.TabNum = tr.TabNum and
         (datepart(year,il.DateBegin) in (select nYear from #TRes) or  datepart(year,il.DateEnd) in (select nYear from #TRes))

Соответственно я спрашиваю, что будет вместо "?", чтобы получить за 3 года(2011,2012,2013) периоды, в которые сотрудник болел. Т.е.
2011 | 20111204 | 20111231 | 27
2011 | 20110901 | 20111231 | 122
2012 | 20120101 | 20120301 | 61
2012 | 20120101 | 20121231 | 365
2012 | 20121101 | 20121201 | 30
2012 | 20130101 | 20130201 | 30
Надеюсь теперь стало понятнее....спасибо ответившим и поправляющим
24 июл 13, 13:13    [14609975]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
rukesa
Member

Откуда:
Сообщений: 22
блин опять ошибся, после
create ill
должны идти
insert #Ill
сорри
24 июл 13, 13:15    [14609996]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
rukesa
Member

Откуда:
Сообщений: 22
Никаких предложений/мыслей нет?
24 июл 13, 15:10    [14611078]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2400
Блог
rukesa,

есть. Поищи по форуму "пересечение интервалов". Лень майкросовтовский синтаксис вспоминать просто. Не найдешь - попытаюсь вспомнить.
24 июл 13, 15:11    [14611096]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
rukesa
Member

Откуда:
Сообщений: 22
спасибо, нашел много. только вот пока никак не могу связать их со своей задачей...
похоже Mnior что-то похожее мне написал, спасибо, но что-то с синтаксисом разобраться не получается, да и опять же к своей задаче привязать не могу.
24 июл 13, 16:21    [14611812]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
rukesa,

для вашего последнего примера соединять таблицы надо так, например
JOIN #Ill il ON il.TabNum = tr.TabNum AND DATEPART(Year, il.DateBegin) <= tr.nYear AND DATEPART(Year, il.DateEnd) >= tr.nYear
, получатся пересечения для каждого года. и уже для результата искать границы интервалов, обрезая выходящее за границы года tr.nYear
24 июл 13, 16:27    [14611884]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
efqwefqwe
Guest
rukesa,

а почему у вас данные

insert #Ill values('20111204','20120301','123')
insert #Ill values('20110901','20130201','123')
insert #Ill values('20121101','20121201','123')

имеют не пустое пересечение? что больной одновременно два раза болел? хотел запилить через window функции, но вот этот момэнт всю малину портит
24 июл 13, 20:23    [14613233]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
rukesa
Member

Откуда:
Сообщений: 22
Извиняюсь, писал этот запрос прямо здесь, поэтому даты брал из головы. Действительно, даты не должны пересекаться. Если нужно, могу придумать новые даты.
25 июл 13, 08:32    [14614340]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
rukesa
Member

Откуда:
Сообщений: 22
Shakill, спасибо, учту
25 июл 13, 08:33    [14614346]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Добрый Э - Эх
Guest
efqwefqwe
rukesa,

а почему у вас данные

insert #Ill values('20111204','20120301','123')
insert #Ill values('20110901','20130201','123')
insert #Ill values('20121101','20121201','123')

имеют не пустое пересечение? что больной одновременно два раза болел? хотел запилить через window функции, но вот этот момэнт всю малину портит
Кто мешает "однотипные" интервалы, имеющие пересечения, "склеить" в единый интервал, после чего "пилить" уже оконными функциями?
25 июл 13, 08:37    [14614355]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Добрый Э - Эх
Guest
rukesa,

очень часто интервальные задачи решаются через преобразование интервалов в точки, после чего решается, как из полученного множества точек вновь собрать интервалы. :)
25 июл 13, 08:43    [14614376]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх,

В целом, по сотруднику пересечение разнотиповых интервалов допускается?
К примеру, человек пошел в отпуск и в середине отпуска заболел. Ну или наоборот: заболел, а в середине болезни случился очередной отпуск.
Или такое в принципе невозможно и в каждый момент времени у сотрудника может быть ровно одно состояние: работает, отпуск, на больничном, в командировке и т.д.?
25 июл 13, 08:54    [14614413]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
rukesa
Member

Откуда:
Сообщений: 22
Пересечение не допускается, в примере я ошибся, когда писал интервалы.
25 июл 13, 09:24    [14614515]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Добрый Э - Эх
Guest
Адаптированный вариант
25 июл 13, 10:32    [14614818]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6727
Shakill
rukesa,
для вашего последнего примера соединять таблицы надо так, например
JOIN #Ill il
ON il.TabNum = tr.TabNum
AND tr.nYear >= DATEPART(Year, il.DateBegin)
AND tr.nYear <= DATEPART(Year, il.DateEnd)
, получатся пересечения для каждого года. и уже для результата искать границы интервалов, обрезая выходящее за границы года tr.nYear
Для непрерывных интервалов не может быть оба знака с равенством.
Всегда с одной стороны отрезка одна точка выколота.

А если добавятся время. Вы будете писать 23:59:59:997 ?
Вы всё равно упустите кусок времени в зависимости от точности.
----------------
Ну вот с подсчётом дней. Можно было самому дописать:
WITH [HoliDays] ([From],[To],[TID]) AS (SELECT Convert(Date,[From]),Convert(Date,[To]),TID FROM (VALUES
 ('20111204','20120301',123)
,('20110901','20130201',124)
,('20121101','20121201',125)
)hd([From],[To],[TID])
), [Years] ([Year],[From],[To]) AS (SELECT Year([From]),Convert(Date,[From]),DateAdd(Year,1,Convert(Date,[From])) FROM (VALUES
 ('20100101')
,('20110101')
,('20120101')
,('20130101')
,('20140101')
)y([From])
)	SELECT	 H.TID
		,Y.[Year]
		,X.[From]
		,X.[To]
		,DateDiff(Day,X.[From],X.[To])	AS [Days]
	FROM	[HoliDays]	H
	JOIN	[Years]		Y ON Y.[From] >= DateAdd(Year,-1,H.[From])
				 AND Y.[From] <  H.[To]
	CROSS APPLY (SELECT
		 CASE WHEN H.[From] > Y.[From] THEN H.[From] ELSE Y.[From] END
		,CASE WHEN H.[To]   < Y.[To]   THEN H.[To]   ELSE Y.[To]   END
			)	X([From],[To])
+ Не нравится пример через WITH? Вот через таблы
DECLARE @HoliDays TABLE ([From] Date,[To] Date, [TID] Int, PRIMARY KEY ([TID],[From]))
INSERT	@HoliDays VALUES
 ('20111204','20120301',123)
,('20110901','20130201',124)
,('20121101','20121201',125)
DECLARE @Years TABLE ([Year] AS Year([From]),[From] Date PRIMARY KEY, [To] AS DateAdd(Year,1,Convert(Date,[From])))
INSERT	@Years ([From]) VALUES
 ('20100101')
,('20110101')
,('20120101')
,('20130101')
,('20140101')

SELECT	 H.TID
	,Y.[Year]
	,X.[From]
	,X.[To]
	,DateDiff(Day,X.[From],X.[To])	AS [Days]
FROM	@HoliDays	H
JOIN	@Years		Y ON Y.[From] >= DateAdd(Year,-1,H.[From])
			 AND Y.[From] <  H.[To]
CROSS APPLY (SELECT
	 CASE WHEN H.[From] > Y.[From] THEN H.[From] ELSE Y.[From] END
	,CASE WHEN H.[To]   < Y.[To]   THEN H.[To]   ELSE Y.[To]   END
		)	X([From],[To])


Зачем нужна таблица (год, сотрудник)? Хватает просто список годов, для разделения.

И что тут ещё мусолить?
25 июл 13, 12:21    [14615466]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
rukesa
Member

Откуда:
Сообщений: 22
Добрый Э - Эх,
Огромное спасибо! Это-то я и пытался все получить, пробовал и курсором и через case, да все никак.
также большое спасибо всем ответившим!
25 июл 13, 12:23    [14615482]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по выборке  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Mnior
Shakill
rukesa,
для вашего последнего примера соединять таблицы надо так, например
JOIN #Ill il
ON il.TabNum = tr.TabNum
AND tr.nYear >= DATEPART(Year, il.DateBegin)
AND tr.nYear <= DATEPART(Year, il.DateEnd)
, получатся пересечения для каждого года. и уже для результата искать границы интервалов, обрезая выходящее за границы года tr.nYear
Для непрерывных интервалов не может быть оба знака с равенством.
Всегда с одной стороны отрезка одна точка выколота.

А если добавятся время. Вы будете писать 23:59:59:997 ?
Вы всё равно упустите кусок времени в зависимости от точности.

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

Откуда:
Сообщений: 22
действительно, время в данной таблице всегда 00:00:00.000
25 июл 13, 12:57    [14615744]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить