Вопрос по выборке / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Вопрос по выборке

25 сообщений из 38, страница 1 из 2

все

Вопрос по выборке

#38341622

rukesa

Участник

Сообщения: 24

Рейтинг: 0 / 0

Добрый день, хочу попросить Вашей помощи в таком вопросе:
Существует таблица отпусков: Дата начала отпуска, дата конца, таб. номер сотрудника.
Промежуточная таблица: таб. номер, год(к примеру 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

Код: sql

1.
2.
3.
4.
5.

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.
Как лучше поступить? Заранее спасибо!

...

Рейтинг:

0 / 0

24.07.2013, 10:05

| Ответить | Цитировать | Написать

Вопрос по выборке

#38341635

StarikNavy

Участник

Откуда: Москва

Сообщения: 1 888

Рейтинг: 0 / 0

rukesa,

зачем новую тему? в старой бы написали.
напишите скрипт создания таблиц, ввода тестовых данных (здесь же, еще тему создавать не надо)
и приведите свой ПОЛНЫЙ запрос, а не отрывок

...

Рейтинг:

0 / 0

24.07.2013, 10:11

| Ответить | Цитировать | Написать

Вопрос по выборке

#38341645

Mnior

Участник

Откуда: Кишинёв

Сообщения: 6 772

Рейтинг: 0 / 0

Рекомендации по оформлению сообщений в форуме
Не написали скрипт тестовых данных.

...

Рейтинг:

0 / 0

24.07.2013, 10:17

| Ответить | Цитировать | Написать

Вопрос по выборке

#38341650

Добрый Э - Эх

Гость

rukesa,

Чего-то на зал за здравие отпуска, кончил за упокой больничные

...

Рейтинг:

0 / 0

24.07.2013, 10:24

| Ответить | Цитировать | Написать

Вопрос по выборке

#38341651

Добрый Э - Эх

Гость

Добрый Э - Эхrukesa,

Чего-то на зал начал за здравие отпуска, кончил за упокой больничные

...

Рейтинг:

0 / 0

24.07.2013, 10:25

| Ответить | Цитировать | Написать

Вопрос по выборке

#38341691

Mnior

Участник

Откуда: Кишинёв

Сообщения: 6 772

Рейтинг: 0 / 0

Да, ладно, забей. Человек совершает тысячи ошибок в день. Хуже, когда настолько слеп, что не замечаешь их.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

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 принципа, которые использовались в написании запроса. Главное постепенно их впитывать.

...

Рейтинг:

0 / 0

24.07.2013, 10:49

| Ответить | Цитировать | Написать

Вопрос по выборке

#38341701

Mnior

Участник

Откуда: Кишинёв

Сообщения: 6 772

Рейтинг: 0 / 0

Согласен, так организовать таблицы (вычисляемые колонки) удобнее:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

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]
;

...

Рейтинг:

0 / 0

24.07.2013, 10:54

| Ответить | Цитировать | Написать

Вопрос по выборке

#38341998

rukesa

Участник

Сообщения: 24

Рейтинг: 0 / 0

не совсем понял 2 последних сообщения и как это относится к моему вопросу...
перепутал больничные и отпуска, так мне нужно будет и то и то, без разницы на чем пробовать.
исходные таблицы:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

---------Таблица год, сотрудник             + также есть две известные переменные @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
Надеюсь теперь стало понятнее....спасибо ответившим и поправляющим

...

Рейтинг:

0 / 0

24.07.2013, 13:13

| Ответить | Цитировать | Написать

Вопрос по выборке

#38342002

rukesa

Участник

Сообщения: 24

Рейтинг: 0 / 0

блин опять ошибся, после

Код: sql

create ill

должны идти

Код: sql

insert #Ill

сорри

...

Рейтинг:

0 / 0

24.07.2013, 13:15

| Ответить | Цитировать | Написать

Вопрос по выборке

#38342275

rukesa

Участник

Сообщения: 24

Рейтинг: 0 / 0

Никаких предложений/мыслей нет?

...

Рейтинг:

0 / 0

24.07.2013, 15:10

| Ответить | Цитировать | Написать

Вопрос по выборке

#38342280

Павел Воронцов

Участник

Откуда: Новосибирск

Сообщения: 2 395

Рейтинг: 0 / 0

rukesa,

есть. Поищи по форуму "пересечение интервалов". Лень майкросовтовский синтаксис вспоминать просто. Не найдешь - попытаюсь вспомнить.

...

Рейтинг:

0 / 0

24.07.2013, 15:11

| Ответить | Цитировать | Написать

Вопрос по выборке

#38342462

rukesa

Участник

Сообщения: 24

Рейтинг: 0 / 0

спасибо, нашел много. только вот пока никак не могу связать их со своей задачей...
похоже Mnior что-то похожее мне написал, спасибо, но что-то с синтаксисом разобраться не получается, да и опять же к своей задаче привязать не могу.

...

Рейтинг:

0 / 0

24.07.2013, 16:21

| Ответить | Цитировать | Написать

Вопрос по выборке

#38342475

Shakill

Участник

Откуда: мск

Сообщения: 1 887

Рейтинг: 0 / 0

rukesa,

для вашего последнего примера соединять таблицы надо так, например

Код: sql

JOIN #Ill il ON il.TabNum = tr.TabNum AND DATEPART(Year, il.DateBegin) <= tr.nYear AND DATEPART(Year, il.DateEnd) >= tr.nYear

, получатся пересечения для каждого года. и уже для результата искать границы интервалов, обрезая выходящее за границы года tr.nYear

...

Рейтинг:

0 / 0

24.07.2013, 16:27

| Ответить | Цитировать | Написать

Вопрос по выборке

#38342844

efqwefqwe

Гость

rukesa,

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

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

имеют не пустое пересечение? что больной одновременно два раза болел? хотел запилить через window функции, но вот этот момэнт всю малину портит

...

Рейтинг:

0 / 0

24.07.2013, 20:23

| Ответить | Цитировать | Написать

Вопрос по выборке

#38343136

rukesa

Участник

Сообщения: 24

Рейтинг: 0 / 0

Извиняюсь, писал этот запрос прямо здесь, поэтому даты брал из головы. Действительно, даты не должны пересекаться. Если нужно, могу придумать новые даты.

...

Рейтинг:

0 / 0

25.07.2013, 08:32

| Ответить | Цитировать | Написать

Вопрос по выборке

#38343137

rukesa

Участник

Сообщения: 24

Рейтинг: 0 / 0

Shakill, спасибо, учту

...

Рейтинг:

0 / 0

25.07.2013, 08:33

| Ответить | Цитировать | Написать

Вопрос по выборке

#38343138

Добрый Э - Эх

Гость

efqwefqwerukesa,

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

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

имеют не пустое пересечение? что больной одновременно два раза болел? хотел запилить через window функции, но вот этот момэнт всю малину портитКто мешает "однотипные" интервалы, имеющие пересечения, "склеить" в единый интервал, после чего "пилить" уже оконными функциями?

...

Рейтинг:

0 / 0

25.07.2013, 08:37

| Ответить | Цитировать | Написать

Вопрос по выборке

#38343143

Добрый Э - Эх

Гость

rukesa,

очень часто интервальные задачи решаются через преобразование интервалов в точки, после чего решается, как из полученного множества точек вновь собрать интервалы. :)

...

Рейтинг:

0 / 0

25.07.2013, 08:43

| Ответить | Цитировать | Написать

Вопрос по выборке

#38343147

Добрый Э - Эх

Гость

Добрый Э - Эх,

В целом, по сотруднику пересечение разнотиповых интервалов допускается?
К примеру, человек пошел в отпуск и в середине отпуска заболел. Ну или наоборот: заболел, а в середине болезни случился очередной отпуск.
Или такое в принципе невозможно и в каждый момент времени у сотрудника может быть ровно одно состояние: работает, отпуск, на больничном, в командировке и т.д.?

...

Рейтинг:

0 / 0

25.07.2013, 08:54

| Ответить | Цитировать | Написать

Вопрос по выборке

#38343177

rukesa

Участник

Сообщения: 24

Рейтинг: 0 / 0

Пересечение не допускается, в примере я ошибся, когда писал интервалы.

...

Рейтинг:

0 / 0

25.07.2013, 09:24

| Ответить | Цитировать | Написать

Вопрос по выборке

#38343277

Добрый Э - Эх

Гость

Адаптированный вариант

...

Рейтинг:

0 / 0

25.07.2013, 10:32

| Ответить | Цитировать | Написать

Вопрос по выборке

#38343462

Mnior

Участник

Откуда: Кишинёв

Сообщения: 6 772

Рейтинг: 0 / 0

Shakillrukesa,
для вашего последнего примера соединять таблицы надо так, например

Код: sql

1.
2.
3.
4.

JOIN #Ill il
ON il.TabNum = tr.TabNum
AND tr.nYear >= DATEPART(Year, il.DateBegin)
AND tr.nYear <= DATEPART(Year, il.DateEnd)

Код: 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.

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? Вот через таблы

Код: 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.

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])

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

И что тут ещё мусолить?

...

Рейтинг:

0 / 0

25.07.2013, 12:21

| Ответить | Цитировать | Написать

Вопрос по выборке

#38343467

rukesa

Участник

Сообщения: 24

Рейтинг: 0 / 0

Добрый Э - Эх,
Огромное спасибо! Это-то я и пытался все получить, пробовал и курсором и через case, да все никак.
также большое спасибо всем ответившим!

...

Рейтинг:

0 / 0

25.07.2013, 12:23

| Ответить | Цитировать | Написать

Вопрос по выборке

#38343546

Shakill

Участник

Откуда: мск

Сообщения: 1 887

Рейтинг: 0 / 0

MniorShakillrukesa,
для вашего последнего примера соединять таблицы надо так, например

Код: sql

1.
2.
3.
4.

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 ?
Вы всё равно упустите кусок времени в зависимости от точности.

а зачем усложнять задачу?
если в опорной таблице только номера годов, то для проверки попадания в заданный год подходят нестрогие сравнения (и неважно, есть в таблице больничных время или нет) и это самый простой вариант.
можно, конечно, поступить другим образом - преобразовать номер года в начало года (включаем) и начало следующего года (его не включаем), но это общий подход и код бы получился несколько сложнее, смысла тут нет

...

Рейтинг:

0 / 0

25.07.2013, 12:55

| Ответить | Цитировать | Написать

Вопрос по выборке

#38343551

rukesa

Участник

Сообщения: 24

Рейтинг: 0 / 0

действительно, время в данной таблице всегда 00:00:00.000

...

Рейтинг:

0 / 0

25.07.2013, 12:57

| Ответить | Цитировать | Написать

25 сообщений из 38, страница 1 из 2

все

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Вопрос по выборке

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&msg=38343546&tid=1705947]:	0ms
get settings:	8ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	157ms
get topic data:	11ms
get forum data:	3ms
get page messages:	58ms
get tp. blocked users:	1ms
others:	250ms

total:	510ms