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

Откуда:
Сообщений: 16
Ребята, прежде чем вы начнете читать содержимое вопроса, я заверяю Вас, что наличие Ваших хороших ответов для меня очень важно! Их неимение, наоборот, смерти подобно. Я имею ооочень небольшой опыт работы в sql, но выполнить именно эту работу для меня - дело первой важности.

Заранее!
Содержимое таблиц менять нельзя, в принципе вообще ничего менять нельзя, нужно просто написать условие (грамотно)

Предположим у нас имеются две таблицы. первая - rabi. Столбики там (да, столбики, я полный ламер, так что простите)
id_raba и FIO. Вторая таблица - virabotka. Ее столбцы: id_raba, vremya, [in/out], nedelya
Если с первой таблицей все более менее понятно, то столбики второй таблицы означают
id_raba - айди раба соотв.
vremya - время в формате datetime
[in/out] - столбец, который имеет значения 0 и 1. 0 - когда человек выходит из офиса через турникет(об этом ниже), 1 - когда возвращается на работу через тот же турникет.
nedelya - номер календарной недели.

Смысл всего этого.
Имеется турникет, который пропускает сотрудников на работу. Сотрудники соответственно подходят, прикладывают карточку на сенсор, турникет вписывает единицу, вписывает вместе с 1 время прохода. Пришел раб допустим в 8 часов утра 14 июня 2012 года.
Затем он выходит в 13 часов дня покурить. Прикладывает карточку на сенсор, турникет ставит 0, прописывает время выхода. Сотрудник 5 минут курит - снова заходит, и вечером уходит домой.

Того наши таблицы имеют
rabi:
id_raba=1
FIO = Балалайкин Трилалай Карабасович

virabotka:
1. id_raba=1
vremya = 2012-06-14 08:00:00
[in/out] = 1
nedelya = 24
2. id_raba=1
vremya = 2012-06-14 13:00:00
[in/out] = 0
nedelya = 24
3. id_raba=1
vremya = 2012-06-14 13:05:00
[in/out] = 1
nedelya = 24
4. id_raba=1
vremya = 2012-06-14 20:00:00
[in/out] = 0
nedelya = 24

Для того, чтобы представить все в более удобной форме я составил запрос
select t1.id_raba, (select t1.vremya where ([in/out]=1)) as voshel, (select t1.vremya where ([in/out]=0)) as vishel, t1.nedelya, t2.FIO from virabotka as t1, rabi as t2
where t1.id_raba=t2.id_raba and t1.id_raba=1

имеем результат.
1.id_raba=1
voshel=2012-06-14 08:00:00
vishel=NULL
nedelya=24
FIO=Балалайкин Трилалай Карабасович
2.id_raba=1
voshel=NULL
vishel=2012-06-14 13:00:00
nedelya=24
FIO=Балалайкин Трилалай Карабасович
3.id_raba=1
voshel=2012-06-14 13:05:00
vishel=NULL
nedelya=24
FIO=Балалайкин Трилалай Карабасович
4.id_raba=1
voshel=NULL
vishel=2012-06-14 20:00:00
nedelya=24
FIO=Балалайкин Трилалай Карабасович

К чему все это? Идея запроса - посчитать количество часов, которые сотрудник проводит на работе за неделю. Я привел вам конкретный пример, когда человек работает на протяжении дня. Для того, чтобы посчитать время на работе я хочу использовать функцию sum(datediff(hours, voshel, vishel)), однако я не умею составлять условия поэтому при написании следующего:

select sum (datediff(hour, vishel, voshel))
where (vishel in
(select t1.id_raba, (select t1.vremya where ([in/out]=1)) as "voshel", (select t1.vremya where ([in/out]=0)) as "vishel", t1.nedelya, t2.FIO from virabotka as t1, rabi as t2
where t1.id_raba=t2.id_raba and t1.id_raba=1 and nedelya=24))
and (voshel in
(select t1.id_raba, (select t1.vremya where ([in/out]=1)) as "voshel", (select t1.vremya where ([in/out]=0)) as "vishel", t1.nedelya, t2.FIO from virabotka as t1, rabi as t2
where t1.id_raba=t2.id_raba and t1.id_raba=1 and nedelya=24))

sql выдает целую гору ошибок.
Сообщение 207, уровень 16, состояние 1, строка 2
Недопустимое имя столбца "vishel".
Сообщение 116, уровень 16, состояние 1, строка 2
В списке выбора можно указать только одно выражение, если вложенный запрос не предварен EXISTS.
Сообщение 207, уровень 16, состояние 1, строка 5
Недопустимое имя столбца "voshel".
Сообщение 116, уровень 16, состояние 1, строка 5
В списке выбора можно указать только одно выражение, если вложенный запрос не предварен EXISTS.
Сообщение 207, уровень 16, состояние 1, строка 1
Недопустимое имя столбца "vishel".
Сообщение 207, уровень 16, состояние 1, строка 1
Недопустимое имя столбца "voshel".

Я не знаю что делать, а проблему нужно решить как можно скорее. Я всецело полагаюсь на Вас. Пожалуйста, если знаете как оформить условие (where (voshel in()) and (vishel in())) то откройте мне эту тайну.
7 янв 13, 05:43    [13730808]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
qwerty112
Guest
Dimsssss
К чему все это? Идея запроса - посчитать количество часов, которые сотрудник проводит на работе за неделю. Я привел вам конкретный пример, когда человек работает на протяжении дня. Для того, чтобы посчитать время на работе я хочу использовать функцию sum(datediff(hours, voshel, vishel)), однако я не умею составлять условия поэтому при написании следующего:

если ваши "рабы" (id_raba) не сидят на работе "за полночь", т.е. в течении дня, когда зашел - гарантированно "сваливают",
то задача решается очень просто
select rabi.id_raba, rabi.FIO, virabotka.nedelya,
sum(case when [in/out] =0 then vremya else -vremya end) as total
from virabotka inner join rabi on virabotka.id_raba=rabi.id_raba
group by rabi.id_raba, rabi.FIO, virabotka.nedelya
7 янв 13, 11:18    [13730998]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
qwerty112
Guest
qwerty112
sum(case when [in/out] =0 then vremya else -vremya end) as total

даа, это результат "в днях" будет,
для "в часах" - нужно будет умножить на 24
7 янв 13, 11:21    [13731002]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Dimsssss
Member

Откуда:
Сообщений: 16
qwerty112, конечно, большое спасибо, но почему-то sql выдает ошибку следующего содержания
Сообщение 8117, уровень 16, состояние 1, строка 1
Тип данных операнда datetime недопустим для оператора minus.

и да, рабы все же могут сидеть на работе ночью
7 янв 13, 13:11    [13731197]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
qwerty112
Guest
Dimsssss
qwerty112, конечно, большое спасибо, но почему-то sql выдает ошибку следующего содержания
Сообщение 8117, уровень 16, состояние 1, строка 1
Тип данных операнда datetime недопустим для оператора minus.

нуу, так тогда, наверна ..
sum(case when [in/out] =0 then vremya end)- sum(case when [in/out] =1 then vremya end) as total


Dimsssss
и да, рабы все же могут сидеть на работе ночью

https://www.sql.ru/forum/actualthread.aspx?tid=127456 п.6
7 янв 13, 13:29    [13731250]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Dimsssss
Member

Откуда:
Сообщений: 16
qwerty112, Когда я использовал твои запросы у меня вместо времени вывел NULL, тем не менее, когда использовал части операции твоей суммы в своем запросе
select rabi.id_raba, rabi.FIO, virabotka.nedelya,
sum(datediff(hour, case when [in/out] =0 then vremya end, case when [in/out] =1 then vremya end)) as vsego
from virabotka inner join rabi on virabotka.id_raba=rabi.id_raba
where rabi.id_raba=virabotka.id_raba and rabi.id_raba=1
group by rabi.id_raba, rabi.FIO, virabotka.nedelya

результат не изменился
7 янв 13, 17:30    [13732077]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
aleks2
Guest
declare @t table (id_raba int -- айди раба соотв.
			     , vremya datetime -- время в формате datetime
				 , [in/out] bit -- столбец, который имеет значения 0 и 1. 0 - когда человек выходит из офиса через турникет(об этом ниже), 1 - когда возвращается на работу через тот же турникет. 
);

-- твоя основная проблема, страдалец, не в запросе
-- твоя основная проблема - карточка могет не сработать.

-- 1. тупое и медленное решение. Но лехкопонятное.
with
numbered as (select *, ROW_NUMBER() over(id_raba, [in/out] order by vremya asc) n from @t )

select b.id_raba, sum(DATEDIFF(minute, e.[in/out], b.[in/out])) as WorkMinute
from ( select * from numbered where [in/out] = 1 ) b 
     inner join ( select * from numbered where [in/out] = 0 ) e 
     on b.id_raba = e.id_raba and b.n=e.n
where b.vremya between @weekstart and @weekend
group by b.id_raba;

-- 2. Гораздо быстрее, но проблему несрабатывания - не решает.
select b.id_raba
     , sum(CASE [in/out] when 1 then -1 else 1 end * DATEDIFF(MINUTE,0, vremya) ) as WorkMinute
  from @t
  where b.vremya between @weekstart and @weekend1
  group by id_raba;
-- но тут я оставляю простор для доделывания...
7 янв 13, 17:58    [13732204]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Dimsssss
Member

Откуда:
Сообщений: 16
aleks2,
а, если не секрет, можешь подсказать что такое @t? я просто нуб, сам понимаешь ;)
7 янв 13, 21:23    [13733060]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
Dimsssss
что такое @t?
Это тестовая таблица для этого примера.
7 янв 13, 21:33    [13733085]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Dimsssss
Member

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

не знаю мне просто sql жалуется на нее - я не знаю что вместо нее подставить
7 янв 13, 21:47    [13733165]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Scherbakov
Member

Откуда: Нижний Новгород
Сообщений: 82
Dimsssss
не знаю мне просто sql жалуется на нее - я не знаю что вместо нее подставить


что конкретно говорит SQL Server? Ты точно её объявил?
7 янв 13, 22:59    [13733867]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Dimsssss
Member

Откуда:
Сообщений: 16
Scherbakov,
в смысле я и не понял как ее объявить
7 янв 13, 23:06    [13733940]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Scherbakov
Member

Откуда: Нижний Новгород
Сообщений: 82
Dimsssss,

ну так aleks2 же написал:
declare @t table (id_raba int -- айди раба соотв.
		         , vremya datetime -- время в формате datetime
		         , [in/out] bit -- столбец, который имеет значения 0 и 1. 0 - когда человек выходит из офиса через турникет(об этом ниже), 1 - когда возвращается на работу через тот же турникет. 
);


этот кусок надо запускать вместе с остальным кодом...
на что конкретно ругается сервер?
7 янв 13, 23:13    [13733988]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
Dimsssss
Scherbakov,
в смысле я и не понял как ее объявить
Ну вот же, в первой строке скрипта:
declare @t table (...
У вас сиквел какой версии - семерка, штоле?
7 янв 13, 23:14    [13733997]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Dimsssss
Member

Откуда:
Сообщений: 16
Ennor Tiegael,
declare @t table
select b.id_raba
     , sum(CASE [in/out] when 1 then -1 else 1 end * DATEDIFF(MINUTE,0, vremya) ) as WorkMinute
  from @t
  where b.vremya between @weekstart and @weekend1
  group by id_raba;


Сообщение 156, уровень 15, состояние 1, строка 2
Неправильный синтаксис около ключевого слова "select".
Сообщение 137, уровень 15, состояние 2, строка 5
Необходимо объявить скалярную переменную "@weekstart".
7 янв 13, 23:22    [13734052]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Dimsssss
Member

Откуда:
Сообщений: 16
Ennor Tiegael, то есть
declare @t table (id_raba int -- айди раба соотв.
		         , vremya datetime -- время в формате datetime
		         , [in/out] bit -- столбец, который имеет значения 0 и 1. 0 - когда человек выходит из офиса через турникет(об этом ниже), 1 - когда возвращается на работу через тот же турникет. 
);
select b.id_raba
     , sum(CASE [in/out] when 1 then -1 else 1 end * DATEDIFF(MINUTE,0, vremya) ) as WorkMinute
  from @t
  where b.vremya between @weekstart and @weekend1
  group by id_raba;

и ошибка Сообщение 137, уровень 15, состояние 2, строка 8
Необходимо объявить скалярную переменную "@weekstart".
но я не умею(
7 янв 13, 23:24    [13734064]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
Dimsssss
Необходимо объявить скалярную переменную "@weekstart".
но я не умею(
Посмотрите в справке по слову "declare"
7 янв 13, 23:44    [13734193]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
invm
Member

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

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

Ну и еще один пример:
+ Обработка "входа-без-выхода", "выхода-без-входа" и пересечения границы недели
declare @ds datetime = '20130101', @de datetime = '20130201';

declare @t table (id int, dt datetime, isin bit, w as datepart(ww, dt), y as year(dt));

insert into @t
values
 (1, '20130101 09:00', 0),
 (1, '20130101 10:00', 1),
 (1, '20130101 10:30', 1),
 (1, '20130101 11:00', 0),
 (1, '20130101 11:20', 0),
 (1, '20130101 12:45', 1),
 (1, '20130109 14:00', 0),
 (1, '20130109 16:00', 1);

with a as
(
 select
  *,
  row_number() over (partition by id order by dt) - row_number() over (partition by id, isin order by dt) as g
 from
  @t
 where
  dt between @ds and @de
),
b as
(
 select top (1) with ties
  id, dt, isin, w, y
 from
  a
 order by
  row_number() over (partition by id, isin, g order by case when isin = 1 then dt end desc, case when isin = 0 then dt end)
),
c as
(
 select
  id, dt, isin, w, y,
  row_number() over (partition by id order by dt) as na,
  row_number() over (partition by id order by dt desc) as nd,
  row_number() over (partition by id, w, y order by dt) as nwa,
  row_number() over (partition by id, w, y order by dt desc) as nwd
 from
  b
),
d as
(
 select
  id, dt, isin, w, y
 from
  c

 union all

 select
  id, dt, isin ^ 1, w, y
 from
  c
 where
  (isin = 1 and nd = 1) or (isin = 0 and na = 1)

 union all

 select
  id, dateadd(ms, -3, cast(cast(dateadd(dd, -datepart(dw, dt) + 8, dt) as date) as datetime)), 0, w, y
 from
  c
 where
  isin = 1 and nwd = 1 and nd <> 1

 union all

 select
  id, dateadd(ms, -3, cast(cast(dateadd(dd, -datepart(dw, dt) + 1, dt) as date) as datetime)), 1, w, y
 from
  c
 where
  isin = 0 and nwa = 1 and na <> 1
)
select
 id, w, y,
 sum(case when isin = 1 then -1 else 1 end * datediff(n, '1900', dt)) / 60.
from
 d
group by
 id, w, y;
В нем не учтен вариант, когда работник зависает на работе больше недели
7 янв 13, 23:59    [13734307]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Алексей Куренков
Member [заблокирован]

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

Чем то мне эта задачка напомнинает на задание на собеседке в сбертех )))))).
Вы туда готовитесь? )))))))
8 янв 13, 14:50    [13736022]     Ответить | Цитировать Сообщить модератору
 Re: помогите с условием пожалуйста  [new]
Dimsssss
Member

Откуда:
Сообщений: 16
Алексей Куренков, возможно мой препод собирается - иначе бы не задал
8 янв 13, 19:10    [13737214]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить