Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
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] Ответить | Цитировать Сообщить модератору |
StarikNavy Member Откуда: Москва Сообщений: 2396 |
rukesa, зачем новую тему? в старой бы написали. напишите скрипт создания таблиц, ввода тестовых данных (здесь же, еще тему создавать не надо) и приведите свой ПОЛНЫЙ запрос, а не отрывок |
24 июл 13, 10:11 [14608542] Ответить | Цитировать Сообщить модератору |
Mnior Member Откуда: Кишинёв Сообщений: 6723 |
Рекомендации по оформлению сообщений в форуме Не написали скрипт тестовых данных. |
24 июл 13, 10:17 [14608617] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
rukesa, Чего-то на зал за |
24 июл 13, 10:24 [14608679] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
|
||
24 июл 13, 10:25 [14608685] Ответить | Цитировать Сообщить модератору |
Mnior Member Откуда: Кишинёв Сообщений: 6723 |
Да, ладно, забей. Человек совершает тысячи ошибок в день. Хуже, когда настолько слеп, что не замечаешь их.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] Ответить | Цитировать Сообщить модератору |
Mnior Member Откуда: Кишинёв Сообщений: 6723 |
Согласен, так организовать таблицы (вычисляемые колонки) удобнее: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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
rukesa Member Откуда: Сообщений: 22 |
блин опять ошибся, после
create ill
должны идти
insert #Ill
сорри
|
24 июл 13, 13:15 [14609996] Ответить | Цитировать Сообщить модератору |
rukesa Member Откуда: Сообщений: 22 |
Никаких предложений/мыслей нет? |
24 июл 13, 15:10 [14611078] Ответить | Цитировать Сообщить модератору |
Павел Воронцов Member Откуда: Новосибирск Сообщений: 2390 Блог |
rukesa, есть. Поищи по форуму "пересечение интервалов". Лень майкросовтовский синтаксис вспоминать просто. Не найдешь - попытаюсь вспомнить. |
24 июл 13, 15:11 [14611096] Ответить | Цитировать Сообщить модератору |
rukesa Member Откуда: Сообщений: 22 |
спасибо, нашел много. только вот пока никак не могу связать их со своей задачей... похоже Mnior что-то похожее мне написал, спасибо, но что-то с синтаксисом разобраться не получается, да и опять же к своей задаче привязать не могу. |
24 июл 13, 16:21 [14611812] Ответить | Цитировать Сообщить модератору |
Shakill Member Откуда: мск Сообщений: 1882 |
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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
rukesa Member Откуда: Сообщений: 22 |
Извиняюсь, писал этот запрос прямо здесь, поэтому даты брал из головы. Действительно, даты не должны пересекаться. Если нужно, могу придумать новые даты. |
25 июл 13, 08:32 [14614340] Ответить | Цитировать Сообщить модератору |
rukesa Member Откуда: Сообщений: 22 |
Shakill, спасибо, учту |
25 июл 13, 08:33 [14614346] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
|
||
25 июл 13, 08:37 [14614355] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
rukesa, очень часто интервальные задачи решаются через преобразование интервалов в точки, после чего решается, как из полученного множества точек вновь собрать интервалы. :) |
25 июл 13, 08:43 [14614376] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
Добрый Э - Эх, В целом, по сотруднику пересечение разнотиповых интервалов допускается? К примеру, человек пошел в отпуск и в середине отпуска заболел. Ну или наоборот: заболел, а в середине болезни случился очередной отпуск. Или такое в принципе невозможно и в каждый момент времени у сотрудника может быть ровно одно состояние: работает, отпуск, на больничном, в командировке и т.д.? |
25 июл 13, 08:54 [14614413] Ответить | Цитировать Сообщить модератору |
rukesa Member Откуда: Сообщений: 22 |
Пересечение не допускается, в примере я ошибся, когда писал интервалы. |
25 июл 13, 09:24 [14614515] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
Адаптированный вариант |
25 июл 13, 10:32 [14614818] Ответить | Цитировать Сообщить модератору |
Mnior Member Откуда: Кишинёв Сообщений: 6723 |
Всегда с одной стороны отрезка одна точка выколота. А если добавятся время. Вы будете писать 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])
Зачем нужна таблица (год, сотрудник)? Хватает просто список годов, для разделения. И что тут ещё мусолить? |
|||
25 июл 13, 12:21 [14615466] Ответить | Цитировать Сообщить модератору |
rukesa Member Откуда: Сообщений: 22 |
Добрый Э - Эх, Огромное спасибо! Это-то я и пытался все получить, пробовал и курсором и через case, да все никак. также большое спасибо всем ответившим! |
25 июл 13, 12:23 [14615482] Ответить | Цитировать Сообщить модератору |
Shakill Member Откуда: мск Сообщений: 1882 |
а зачем усложнять задачу? если в опорной таблице только номера годов, то для проверки попадания в заданный год подходят нестрогие сравнения (и неважно, есть в таблице больничных время или нет) и это самый простой вариант. можно, конечно, поступить другим образом - преобразовать номер года в начало года (включаем) и начало следующего года (его не включаем), но это общий подход и код бы получился несколько сложнее, смысла тут нет |
||||
25 июл 13, 12:55 [14615728] Ответить | Цитировать Сообщить модератору |
rukesa Member Откуда: Сообщений: 22 |
действительно, время в данной таблице всегда 00:00:00.000 |
25 июл 13, 12:57 [14615744] Ответить | Цитировать Сообщить модератору |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
Все форумы / Microsoft SQL Server | ![]() |