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

Откуда:
Сообщений: 466
Добрый день.

Понимаю, что вопрос простой, но не могу с ним разобраться

Есть вот такая конструкция
DECLARE
@A DATETIME,
@B DATETIME
SET @A = '2013-10-30 06:01:00.000'
SET @B = '1900-01-01 06:00:00.000'

SELECT DATEDIFF(MINUTE,DATEPART(MINUTE,@A),DATEPART(MINUTE,@B))

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

Подскажите, плиз, что делаю не так.

Заранее благодарен.
30 окт 13, 17:10    [15054422]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
ИльдарSR,

у datediff второй и третий параметры - datetime. а datepart возвращает int.
30 окт 13, 17:13    [15054474]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
Просто у меня есть таблица, где хранятся расписания, там именно в формате таком
SET @B = '1900-01-01 06:00:00.000'

И логи захода в систему
SET @A = '2013-10-30 06:01:00.000'

Мне надо смотреть на расписание, и если человек опаздал на 20 минту, то логировать это
30 окт 13, 17:15    [15054491]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
Гость333
Member

Откуда:
Сообщений: 3683
ИльдарSR,

Версия SQL Server у вас какая?
30 окт 13, 17:16    [15054496]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
o-o
Guest
ну так вы посчитали, сколько минут в сутках.

выполните вот это, должно стать понятно:

DECLARE
@A DATETIME,
@B DATETIME
SET @A = '2013-10-30 06:01:00.000'
SET @B = '1900-01-01 06:00:00.000'

SELECT DATEDIFF(MINUTE,DATEPART(MINUTE,@A),DATEPART(MINUTE,@B)), DATEDIFF(MINUTE,1,0), 24 * 60 
select cast(1 as datetime), cast(0 as datetime)
30 окт 13, 17:16    [15054498]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
sql server 2012
30 окт 13, 17:17    [15054509]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
o-o
Guest
ИльдарSR
sql server 2012


DECLARE
@A DATETIME,
@B DATETIME
SET @A = '2013-10-30 06:01:00.000'
SET @B = '1900-01-01 06:00:00.000'

SELECT DATEDIFF(MINUTE,cast(@B as time),cast(@A as time))
30 окт 13, 17:21    [15054555]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
Огромное спасибо, получилось
30 окт 13, 17:23    [15054589]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
o-o
Guest
т.е. надо брать разницу в минутах между временнОй частью, преобразовав явно во время.
либо, в более ранних версиях, привести и вторую дату к формату 1900-01-01 <время>, затем уже DATEDIFF.

то, что Вы сделали, выглядело так: выделим минуты из дат, это 1 и 0.
приведем 1 и 0 к типу DATETIME(это то, что ожидает DATEDIFF), получаем 1900-01-02 00:00:00.000 и и 1900-01-01 00:00:00.000
ну и считаем между соседними днями разницу в минутах, как раз 1440
30 окт 13, 17:28    [15054624]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
спасибо за объяснение
30 окт 13, 17:31    [15054642]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
Чтобы не плодить топик, хотел уточнить кое-что

Есть таблицы
Расписание
Table1(Id,StartDate,EndDate,DayOfWeek)
Логи
Table2(IdOffice,BeginDate,EndDate)

Когда пользователь приходит в офис, он логиниться, то мы заносим запись в Table2 и смотрим из Table1 поздно пришел или рано)
Написал вот такой запрос
SELECT b.Id ,
CA.CassId ,
CA.Address ,
b.PhoneNumberBranch
FROM Office b
CROSS APPLY(
SELECT CassId ,
Address
FROM Office b1
INNER JOIN Table1 ss ON(b1.Id = ss.OfficeID)
INNER JOIN Table2 os ON(b1.Id = os.OfficeID)
WHERE DayOfWeek = (DATEPART(DW, GETDATE()) + @@DATEFIRST - 1) % 7
AND DATEDIFF(MINUTE,CAST(BeginDate AS TIME),cast(os.StartDate AS TIME))>10
AND CONVERT(VARCHAR(10),os.StartDate,121) = CONVERT(VARCHAR(10),GETDATE(),121)
AND b.Id = b1.ParentId
) AS CA

Но проблема у меня в том, что, если сотрудник не появился в офисе, то условие
INNER JOIN Table2 os ON(b1.Id = os.OfficeID) не работает

Т.е запись не отмечается в Table2, как бы ухитриться поставить проверку в данный запрос, чтобы одним запросом отслеживать и тех и других?
Подскажите, пожалуйста.
30 окт 13, 18:43    [15055100]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
BestiA
Member

Откуда: Саратов
Сообщений: 44
Так что-ли?

DECLARE @Office TABLE (Id Int identity ,ParentId Int,PhoneNumberBranch Varchar(12),Address Varchar(255))
-- расписание 
DECLARE @Table1 TABLE (Id Int identity,OfficeID Int,BeginDate DateTime,EndDate DateTime,DayOfWeek tinyInt) 
-- логи
DECLARE @Table2 TABLE (OfficeID Int,CassId Int,StartDate DateTime,EndDate DateTime)

Declare @dw Int
		,@MinDelay Int
		,@ct DateTime  

SET @MinDelay = 10 -- 
SET @ct = GETDATE()
SET @dw = (DATEPART(DW, @ct) + @@DATEFIRST - 1) % 7 -- день недели

SELECT 	of0.Id ,
		CA.CassId,
		CA.Address,
		of0.PhoneNumberBranch
FROM @Office of0
CROSS APPLY(
	SELECT 
		os.CassId,
		of1.Address
	FROM @Office of1
	INNER JOIN @Table1 ss ON (of1.Id = ss.OfficeID)
	LEFT JOIN @Table2 os ON (of1.Id = os.OfficeID)
	WHERE DayOfWeek = @dw
	AND DATEDIFF(MINUTE,ss.BeginDate,ISNULL(os.StartDate,ss.EndDate)) > @MinDelay
	AND (os.StartDate IS NULL OR DATEDIFF(DD,os.StartDate,@ct)=0)
AND of0.Id = of1.ParentId
) AS CA
30 окт 13, 19:47    [15055307]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
Добрый день.

Да, точно, спасибо большое
31 окт 13, 10:00    [15056728]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
Обнаружил одну проблемку

SELECT DISTINCT
b.PhoneNumberBranch ,
CA.Address ,
CA.Title
FROM Office b
CROSS APPLY(
SELECT
b1.ID AS ParentId ,
Address AS Address ,
b1.Title AS Title
FROM Office b1
INNER JOIN Schedules ss ON(b1.Id = ss.OfficeID)
LEFT JOIN LogSystem os ON(b1.Id = os.OfficeID)
WHERE DayOfWeek = @DayOfWeek
AND DATEDIFF(MINUTE,CAST(OpeningTime AS TIME),CAST(ISNULL(os.StartDate,GETDATE()) AS TIME)) > @Threshold
AND (os.StartDate IS NULL OR DATEDIFF(DD,os.StartDate,@CURRENT_DATETIME)=0)
AND b.OfficeID = b1.ParentId
) AS CA

Если в таблице LogSystem присутсвутют записи за прошлый период, то условие не выполнится((
AND (os.StartDate IS NULL OR DATEDIFF(DD,os.StartDate,@CURRENT_DATETIME) = 0)
так как os.StartDate не будет null значением и DATEDIFF(DD,os.StartDate,@CURRENT_DATETIME) = 0) не будет равно нулю, хотя в моем случае прошлый период не надо рассматриват, по идее мне надо чтобы выполнилось условие os.StartDate is null
Целый день уже никак не могу понять как вписать данное условие, подскажите, пожалуйста.
31 окт 13, 18:23    [15060472]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
Попробовал в условие что-то вроде такого написать
AND (
os.StartDate IS NULL OR case
when DATEDIFF(DD,os.StartDate,GETDATE()) = 0 then DATEDIFF(DD,os.StartDate,GETDATE()
when DATEDIFF(DD,os.StartDate,GETDATE()) < 0 then null end

Но пока выдает ошибку((
А как сделать это без case никак не соображу((
31 окт 13, 18:28    [15060509]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
Извиняюсь за спам
Сделать данное условие удалось, но оно не работает
AND (os.StartDate IS NULL OR DATEDIFF(DD,os.StartDate,GETDATE()) = case
when DATEDIFF(DD,os.StartDate,GETDATE()) = 0 then 0
when DATEDIFF(DD,GETDATE(),os.StartDate) < 0 then null end )

Подскажите, пожалуйста, как заставить запрос смотреть именно логи, которые находятся от сегодняшней даты и выше)т.е чтобы он за прошлый период не смотрел, и если таки х нет, то должно выполниться условие StartDate is null и будет у меня счастье)
31 окт 13, 18:40    [15060567]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
((((
Помогите плизз (( никак додумать не могу
31 окт 13, 23:09    [15061733]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
prog882
Guest
ИльдарSR,
Приведи данные, типа:
declare @office table (id int....)
insert into @office
select 1 .... 

тогда быстрее решим задачу
1 ноя 13, 08:10    [15062314]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
BestiA
Member

Откуда: Саратов
Сообщений: 44
На самом деле. Может в базе LogSystem есть записи, где StartDate не заполнены? (IS NULL)
Возьмите за образец мой недавний пример и вставьте в него несколько тестовых записей во временные таблицы, для моделирования вышеописанной проблемы. Я же не знаю, какие данные хранятся в таблицах, на которых выполняется запрос. Раз уж был вставлен DISTINCT - возможно есть дублирующиеся записи.
1 ноя 13, 09:47    [15062579]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
DECLARE @Office TABLE (Id Int identity ,ParentId Int,PhoneNumberBranch Varchar(12),Address Varchar(255))

-- расписание 
DECLARE @Schedules TABLE (Id Int identity,OfficeID Int,OpeningTime DateTime,EndDate DateTime,DayOfWeek tinyInt) 
-- логи
DECLARE @LogSystem TABLE (OfficeID Int,CassId Int,StartDate DateTime,EndDate DateTime)

INSERT INTO @Office
VALUES(1,'8955555','TEST ADDRESS')

INSERT INTO @Schedules(OfficeID ,OpeningTime ,EndDate ,DayOfWeek ) 
VALUES(1,'1900-01-01 10:00:00.000','1900-01-01 23:00:00.000',5)

INSERT INTO @LogSystem 
VALUES(1,1,'2013-10-01 02:00:00','2013-11-01 18:00:00')

SELECT DISTINCT
	b.PhoneNumberBranch , 
	CA.Address 
	FROM @Office b
	
	CROSS APPLY(
	SELECT
	b1.ID AS ParentId ,
	Address AS Address 
	
	FROM @Office b1
		INNER JOIN @Schedules ss ON(b1.Id = ss.OfficeID)
		LEFT JOIN @LogSystem os ON(b1.Id = os.OfficeID)
	WHERE DayOfWeek = 5
		AND DATEDIFF(MINUTE,CAST(OpeningTime AS TIME),CAST(ISNULL(os.StartDate,GETDATE()) AS TIME)) > 0 
		AND (os.StartDate IS NULL OR DATEDIFF(DD,os.StartDate,GETDATE())=0)
		AND b.ParentId = b1.ParentId
	) AS CA



Т.е здесь время за прошедший период
INSERT INTO @LogSystem
VALUES(1,1,'2013-10-01 02:00:00','2013-11-01 18:00:00')
Но резалсет не выдал((Хотя получается, что офис должен был открыться, но резалсет пустой
1 ноя 13, 10:30    [15062793]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
BestiA
Member

Откуда: Саратов
Сообщений: 44
ИльдарSR, может так сойдёт? ;-)

	SELECT DISTINCT
	b.PhoneNumberBranch
	,CA.Address 
	FROM @Office b
	CROSS APPLY(
		SELECT
		b1.ID AS ParentId ,
		Address AS Address 
		FROM @Office b1
			INNER JOIN @Schedules ss ON(b1.Id = ss.OfficeID)
			LEFT JOIN @LogSystem os ON(b1.Id = os.OfficeID) AND DATEDIFF(DD,os.StartDate,GETDATE())=0
		WHERE DayOfWeek = 5
			AND DATEDIFF(MINUTE,OpeningTime,ISNULL(os.StartDate,GETDATE())) > 0 
			AND b.ParentId = b1.ParentId
	) AS CA
1 ноя 13, 11:15    [15063098]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
Гость333
Member

Откуда:
Сообщений: 3683
ИльдарSR,

Я вот совсем не понял, что за "прошлый период", какие условия вы пытаетесь вписать и т.д.
Лучше расскажу, как можно отлаживать подобные запросы.
Если какой-то набор условий не срабатывает так, как вам нужно, то некоторые из этих условий неправильно написаны. Начинаем по очереди их убирать в комментарии, пока нужные записи не появятся в выходном результате запроса. При этом нужно постараться оставить незакомментированными максимальное количество условий. У меня получилось так:
SELECT DISTINCT
	b.PhoneNumberBranch , 
	CA.Address 
	FROM @Office b
	
	CROSS APPLY(
	SELECT
	b1.ID AS ParentId ,
	Address AS Address 
	
	FROM @Office b1
		INNER JOIN @Schedules ss ON(b1.Id = ss.OfficeID)
		LEFT JOIN @LogSystem os ON(b1.Id = os.OfficeID)
	WHERE DayOfWeek = 5
		--AND DATEDIFF(MINUTE,CAST(OpeningTime AS TIME),CAST(ISNULL(os.StartDate,GETDATE()) AS TIME)) > 0 
		--AND (os.StartDate IS NULL OR DATEDIFF(DD,os.StartDate,GETDATE())=0)
		AND b.ParentId = b1.ParentId
	) AS CA

Далее, нужно посмотреть, чему равны значения в закомментированных условиях, это поможет понять, почему условия не выполняются. Для этого нужно вывести эти значения в SELECT'е.
SELECT DISTINCT
	b.PhoneNumberBranch , 
	CA.* 
	FROM @Office b
	
	CROSS APPLY(
	SELECT
	b1.ID AS ParentId ,
	Address AS Address,
	X = DATEDIFF(MINUTE,CAST(OpeningTime AS TIME),CAST(ISNULL(os.StartDate,GETDATE()) AS TIME)),
	Y = os.StartDate,
	Z = DATEDIFF(DD,os.StartDate,GETDATE())
	
	FROM @Office b1
		INNER JOIN @Schedules ss ON(b1.Id = ss.OfficeID)
		LEFT JOIN @LogSystem os ON(b1.Id = os.OfficeID)
	WHERE DayOfWeek = 5
		--AND DATEDIFF(MINUTE,CAST(OpeningTime AS TIME),CAST(ISNULL(os.StartDate,GETDATE()) AS TIME)) > 0 
		--AND (os.StartDate IS NULL OR DATEDIFF(DD,os.StartDate,GETDATE())=0)
		AND b.ParentId = b1.ParentId
	) AS CA

Результат:
PhoneNumberBranch ParentId Address X Y Z
8955555 1 TEST ADDRESS -480 2013-10-01 02:00:00.000 31

При этом закомментированные условия выглядят так:
AND X > 0
AND (Y IS NULL OR Z = 0)

Подставив X, Y, Z из таблицы результатов, видим, что ни одно условие не выполняется.

Ну а дальше нужно модифицировать условия, модифицировать вывод значений в SELECT'е, добиваться правильной работы запроса.
1 ноя 13, 11:17    [15063112]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
BestiA
Member

Откуда: Саратов
Сообщений: 44
Там загвоздка была в самом принципе выполнения LEFT JOIN.
В первую очередь при объединении присоединяются те строки, что подпадают под условие, а уж если их нет, то оставляются пустые поля. Вот и получается, что вместо записи из лога за текущий день (которой нет) присоединяется запись за прошлый период. В этом и была проблема. Надо просто в условие LEFT JOIN. помимо условия сопоставления строк добавить ещё и ограничение на дату, что я и сделал в последнем примере.
1 ноя 13, 12:47    [15064064]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по datediff  [new]
ИльдарSR
Member

Откуда:
Сообщений: 466
BestiA.
Круто, теперь все работает, спасибо большое за помощь
1 ноя 13, 13:24    [15064403]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить