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

Откуда:
Сообщений: 19
Уважаемые форумчане!

Помогите с построением запроса, так как испытываю сложности с форматом time.
Есть таблица (MS SQL server) в ней три поля:
- Date (тип (date)),
- FlightStart (тип time(7)),
- FlightEnd (тип time(7)).

В запросе, нужно показать:
- разницу (FlightTime) между FlightStart и FlightEnd в часах и минутах, причем если FlightStart = 23:30, а FlightEnd = 02:30, то разница FlightTime = FlightEnd - FlightStart должна быть 03:00, а не минусовое значение. Что то типа FlightTime = (FlightEnd +24 часа) - FlightStart
- нарастающий итог полученной разницы тоже в часах и минутах (сортировка по Date).

Результат нужен примерно такой:

К сообщению приложен файл. Размер - 104Kb
13 июл 14, 17:57    [16298011]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8880
Преобразовать date и time в datetime (в самом же select) и воспользоваться datediff(). Результат обратно кинуть в time.
PS Сам не пробовал...
13 июл 14, 22:05    [16298606]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8880
Как-то так:
declare @d1 date = '20140701'
       ,@t1 time = '23:30:00'
       ,@d2 date = '20140702'
       ,@t2 time = '03:35:00';
       
select  (cast(@d1 as datetime) + cast(@t1 as datetime)), (cast(@d2 as datetime) + cast(@t2 as datetime))
       ,(cast(@d2 as datetime) + cast(@t2 as datetime)) - (cast(@d1 as datetime) + cast(@t1 as datetime))
       ,CAST((cast(@d2 as datetime) + cast(@t2 as datetime)) - (cast(@d1 as datetime) + cast(@t1 as datetime)) as time);
       
-- Но чо делать если разница больше суток - на усмотрение заказчика       
declare @d21 date = '20140701'
       ,@t21 time = '23:30:00'
       ,@d22 date = '20140705'
       ,@t22 time = '03:35:00';

select  (cast(@d21 as datetime) + cast(@t21 as datetime)), (cast(@d22 as datetime) + cast(@t22 as datetime))
       ,(cast(@d22 as datetime) + cast(@t22 as datetime)) - (cast(@d21 as datetime) + cast(@t21 as datetime))
       ,CAST((cast(@d22 as datetime) + cast(@t22 as datetime)) - (cast(@d21 as datetime) + cast(@t21 as datetime)) as time)
       ,CAST((cast(@d22 as datetime) + cast(@t22 as datetime)) - (cast(@d21 as datetime) + cast(@t21 as datetime)) as datetime);

Во втором случае - разница больше 24-х часов. Но показаны только часы (т.к. тип TIME - понятно чо)...
13 июл 14, 22:18    [16298625]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
ac1-caesar
Member

Откуда:
Сообщений: 19
Уважаемый SIMPLicity_ , дату к вычислениям привязывать не надо. По полю дата, только сортировка для нарастающего итога.
Я делаю вот так:
SELECT  
f.FillingDate, f.FlightStart, f.FlightEnd, 
(case 
when f.FlightStart<f.FlightEnd then DATEDIFF(minute, f.FlightStart, f.FlightEnd)
when f.FlightStart>f.FlightEnd then DATEDIFF(minute, f.FlightStart, dateadd("MINUTE" , 1440, f.FlightEnd))
end),
RP.StartTTSN*60 + sum(case 
when f.FlightStart<f.FlightEnd then DATEDIFF(minute, f.FlightStart, f.FlightEnd)
when f.FlightStart>f.FlightEnd then DATEDIFF(minute, f.FlightStart, dateadd("MINUTE" , 1440, f.FlightEnd))
end) over (order by F.FillingDate, F.FlightStart, f.FlightEnd,f.ID_FLIGHT)

FROM Flight F JOIN RPlane RP
ON F.ID_RPlane = RP.ID_RPlane
WHERE F.ID_RPlane =1 AND F.FillingDate BETWEEN RP.StartDate AND GETDATE()


Здесь все вычисления в минутах (количество), но в отчет мне нужно показывать часы и минуты.
И еще недостаток в том, что при FlightStart = 21:21, а FlightEnd = 20:21, то разница получается -60 мин, хотя должно быть 23 часа.

если конвертировать в varchar, тогда корректно считает разницу, но невозможно делать суммирование и нарастающий итог.
(case 
when f.FlightStart<f.FlightEnd then (SELECT CONVERT(varchar(15), DATEADD(minute, DATEDIFF(minute, f.FlightStart, f.FlightEnd), 0), 108))
when f.FlightStart>f.FlightEnd then (SELECT CONVERT(varchar(15), DATEADD(minute, DATEDIFF(minute, f.FlightStart, dateadd("MINUTE" , 1440, f.FlightEnd)), 0), 108))
end)


Подсказывайте, где что не так?
14 июл 14, 10:33    [16299884]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
o-o
Guest
я проходом и особо не вникая,
но по-мне разницу надо "в лоб" считать,
в минутах же нараст. итог считать, а потом минуты/часы выделить.
я выделяю в примере сразу только чтобы проверить, правильно ли разница посчиталась.
все, убегаю

declare @t table (FlightStart time(7), FlightEnd time(7));
insert into @t values('6:25', '12:02'), ('20:44', '2:19'), ('21:21', '20:21');

with cte as (
select *, datediff(MINUTE, FlightStart, FlightEnd) + case when FlightStart > FlightEnd then 24 * 60 else 0 end as dif
from @t)

select *, dif / 60 as hh, dif % 60 as mi
from cte
--------------------------
FlightStart	FlightEnd	dif	hh	mi
06:25:00.0000000	12:02:00.0000000	337	5	37
20:44:00.0000000	02:19:00.0000000	335	5	35
21:21:00.0000000	20:21:00.0000000	1380	23	0
14 июл 14, 11:21    [16300146]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
ac1-caesar
Member

Откуда:
Сообщений: 19
автор
я проходом и особо не вникая,
но по-мне разницу надо "в лоб" считать,
в минутах же нараст. итог считать, а потом минуты/часы выделить.
я выделяю в примере сразу только чтобы проверить, правильно ли разница посчиталась.
все, убегаю


Спасибо, значительно упрощенный вариант. Только пока не знаю как сделать чтобы нарастающий итог и сумма в таблице получалась как время т.е. что то типа 2357:20?
14 июл 14, 15:56    [16302077]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
o-o
Guest
ac1-caesar
как сделать чтобы нарастающий итог и сумма в таблице получалась как время т.е. что то типа 2357:20?


так это не время, а слепленная строка
declare @t table (FlightStart time(7), FlightEnd time(7));
insert into @t values('6:25', '12:02'), ('20:44', '2:19'), ('21:21', '20:21');

with cte as (
select *, datediff(MINUTE, FlightStart, FlightEnd) + case when FlightStart > FlightEnd then 24 * 60 else 0 end as dif
from @t)

select *, dif / 60 as hh, dif % 60 as mi,
       cast(dif / 60 as varchar(10)) + ':' + right('0' + cast(dif % 60 as varchar(2)), 2) as dif_str
from cte
------------------------------
FlightStart	FlightEnd	dif	hh	mi	dif_str
06:25:00.0000000	12:02:00.0000000	337	5	37	5:37
20:44:00.0000000	02:19:00.0000000	335	5	35	5:35
21:21:00.0000000	20:21:00.0000000	1380	23	0	23:00
14 июл 14, 16:35    [16302283]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
_human
Member

Откуда:
Сообщений: 569
ac1-caesar,

если версия позволяет.

SELECT custid, orderid, orderdate, val,
SUM(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS runningtotal
FROM Sales.OrderValues;


выделяете часы минуты ..
записываете как нужно, например

declare 
	@h int = 2357, @m int = 20
select convert(varchar(10), @h)+':'+convert(varchar(10), @m)
14 июл 14, 16:39    [16302317]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
ac1-caesar
Member

Откуда:
Сообщений: 19
Спасибо, для отчета как раз. Нарастающий итог заработал. Вот как выглядит:
with cte as (
select *, datediff(MINUTE, FlightStart, FlightEnd) + case when FlightStart > FlightEnd then 24 * 60 else 0 end as diffight
from Flight)

select 
cast(sum(diffight) over (order by FillingDate, FlightStart, FlightEnd,ID_FLIGHT) / 60 as varchar(10)) + ':' + right('0' + cast(sum(diffight) over (order by FillingDate, FlightStart, FlightEnd,ID_FLIGHT) % 60 as varchar(2)), 2) as dif_str
from cte


А не подскажите как в cte join-ить таблицу? что бы не просто from Flight, а:
FROM Flight F JOIN RPlane RP
ON F.ID_RPlane = RP.ID_RPlane
WHERE F.ID_RPlane =1 AND F.FillingDate BETWEEN RP.StartDate AND GETDATE()
14 июл 14, 16:51    [16302395]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
o-o
Guest
у меня в примере CTE только чтобы
datediff(MINUTE, FlightStart, FlightEnd) + case when FlightStart > FlightEnd then 24 * 60 else 0 end

обозвать dif и не таскать за собой такое длинное.

а джойнить -- прямо так и джойнить,
не забывайте только в CTE-е всем колонкам различные алиасы присвоить
14 июл 14, 17:01    [16302474]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
ac1-caesar
Member

Откуда:
Сообщений: 19
автор
не забывайте только в CTE-е всем колонкам различные алиасы присвоить

Вот и я о том, попробовал так и join-ить, так оно мне говорить что ID_RPlane был указан для cte несколько раз. Честно говоря не знаю что за "алиасы" и как их присваивать...
14 июл 14, 17:08    [16302503]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
ac1-caesar
Member

Откуда:
Сообщений: 19
Так не хочет работать...
with cte as (
select *, datediff(MINUTE, F.FlightStart, F.FlightEnd) + case when F.FlightStart > F.FlightEnd then 24 * 60 else 0 end as diffight
FROM Flight F JOIN RPlane RP
ON F.ID_RPlane = RP.ID_RPlane
WHERE F.ID_RPlane =1 AND F.FillingDate BETWEEN RP.StartDate AND GETDATE())

select 
cast(sum(diffight) over (order by F.FillingDate, F.FlightStart, F.FlightEnd, F.ID_FLIGHT) / 60 as varchar(10)) + ':' + right('0' + cast(sum(diffight) over (order by F.FillingDate, F.FlightStart, F.FlightEnd, F.ID_FLIGHT) % 60 as varchar(2)), 2) as dif_str
from cte
14 июл 14, 17:13    [16302528]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
o-o
Guest
вроде ж пишу: алиасы ВСЕМ КОЛОНКАМ РАЗЛИЧНЫЕ НАДО!!!
написав звезду в селекте, вы выбрали все колонки обеих таблиц,
а у них как минимум повторяется ID_RPlane
>>>F.ID_RPlane = RP.ID_RPlane
или выбирайте только нужные, или переименовывайте,
а в след. раз не запрос пишите, а ошибку, не видно же структуру таблиц на расстоянии
14 июл 14, 17:30    [16302624]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
ac1-caesar
Member

Откуда:
Сообщений: 19
При таком варианте:
with cte as (
select F.FillingDate, F.FlightStart, F.FlightEnd, datediff(MINUTE, F.FlightStart, F.FlightEnd) + case when F.FlightStart > F.FlightEnd then 24 * 60 else 0 end as diffight
FROM Flight F JOIN RPlane RP
ON F.ID_RPlane = RP.ID_RPlane
WHERE F.ID_RPlane =1 AND F.FillingDate BETWEEN RP.StartDate AND GETDATE())

select 
cast(sum(diffight) over (order by F.FillingDate, F.FlightStart, F.FlightEnd, F.ID_FLIGHT) / 60 as varchar(10)) + ':' + right('0' + cast(sum(diffight) over (order by F.FillingDate, F.FlightStart, F.FlightEnd, F.ID_FLIGHT) % 60 as varchar(2)), 2) as dif_str
from cte

Пишет такую ошибку:

Сообщение 4104, уровень 16, состояние 1, строка 8
Не удалось привязать составной идентификатор "F.FillingDate".
Сообщение 4104, уровень 16, состояние 1, строка 8
Не удалось привязать составной идентификатор "F.FlightStart".
Сообщение 4104, уровень 16, состояние 1, строка 8
Не удалось привязать составной идентификатор "F.FlightEnd".
Сообщение 4104, уровень 16, состояние 1, строка 8
Не удалось привязать составной идентификатор "F.ID_FLIGHT".
Сообщение 4104, уровень 16, состояние 1, строка 8
Не удалось привязать составной идентификатор "F.FillingDate".
Сообщение 4104, уровень 16, состояние 1, строка 8
Не удалось привязать составной идентификатор "F.FlightStart".
Сообщение 4104, уровень 16, состояние 1, строка 8
Не удалось привязать составной идентификатор "F.FlightEnd".
Сообщение 4104, уровень 16, состояние 1, строка 8
Не удалось привязать составной идентификатор "F.ID_FLIGHT".


Или не там джойню или не то....
14 июл 14, 18:02    [16302826]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
_human
Member

Откуда:
Сообщений: 569
ac1-caesar,

with cte as (
select F.FillingDate, F.FlightStart, F.FlightEnd, datediff(MINUTE, F.FlightStart, F.FlightEnd) + case when F.FlightStart > F.FlightEnd then 24 * 60 else 0 end as diffight
FROM Flight F JOIN RPlane RP
ON F.ID_RPlane = RP.ID_RPlane
WHERE F.ID_RPlane =1 AND F.FillingDate BETWEEN RP.StartDate AND GETDATE())

select 
cast(sum(diffight) over (order by F.FillingDate, F.FlightStart, F.FlightEnd, F.ID_FLIGHT) / 60 as varchar(10)) + ':' + right('0' + cast(sum(diffight) over (order by F.FillingDate, F.FlightStart, F.FlightEnd, F.ID_FLIGHT) % 60 as varchar(2)), 2) as dif_str
from cte as F
14 июл 14, 18:21    [16302922]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8880
Модератор: Если модераторы не трут весь неформат сходу, это не значит, что они его не видят.
Поэтому медленно возвращаемся в формат форума.


Сообщение было отредактировано: 15 июл 14, 02:05
15 июл 14, 01:46    [16304655]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
o-o
Guest
а для расширения кругозора можно товарищу обкуренному шпицу, наехавшему на 24 * 60, инфо подкинуть?

+
как правило, авиакомпании заправляют свои лайнеры минимальным количеством топлива.
Однако, так называемый "минимум горючего" не означает, что топлива хватает строго, чтобы выполнить перелёт из точки А в точку В.
Как прописывают международные правила авиации, на борту должно быть горючее для перелета из одного пункта в другой. Помимо основного, должно быть также "свободное" топливо, которое составляет примерно 3,5 процента от количества основного горючего.
Оно используется в экстренных случаях, например, при утечке основного топлива, или же, если по каким-то причинам, приземлиться быстро не удаётся. Запасного топлива хватает ещё на 30 минут полёта.
30 наиболее протяжённых регулярных авиарейсов
так что 2 суток в ...е -- из области фантастики
15 июл 14, 02:15    [16304689]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8880
Мой отец летал на Камчатку в совеццкое ещё время,- однажды (перелёт сам по себе неблизкий) были проблемы с самолётом. В общем, если под FlightStart и FlightEnd понимаются моменты старта из начальной точки маршрута и посадки в конечной (со всеми промежуточными причиндалами и хернёй), то почему бы и не неделю... (ну, утрирую, конечно, немного)
15 июл 14, 02:20    [16304693]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8880
SIMPLicity_
Модератор: Если модераторы не трут весь неформат сходу, это не значит, что они его не видят.
Поэтому медленно возвращаемся в формат форума.

.... въехал ....

--------------
А что нынче в трэнде для выражения крайнего неприятия предложенных решений (в разрезе исключительно MS SQL Server) $-)
15 июл 14, 02:24    [16304697]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
o-o
Guest
а при чем тут сервер?
если полеты "ненормальные" и вместо времени в полете подсчитываем, сколько вообще человек добирался,
то тогда и не хранят дату отдельно от времени, а используют старый добрый datetime

+
я тоже могу рассказать о прекрасном и вполне себе недавнем полете "через прагу", замененном на "через лондон", рейс все откладывали, а потом возмущенным пассажирам выдали: че вы жалуетесь, вас хоть и через ...у, но сегодня отправят,
а самолет из праги не прилетел и не прилетит, чехи чего-то там свердловску не заплатили, так что кольцово их не приняло,
самолет сел в самаре, а как они оттуда в екб будут добираться -- личное дело каждого
15 июл 14, 02:49    [16304719]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
ac1-caesar
Member

Откуда:
Сообщений: 19
Ребята, всем спасибо большое, очень помогли!!!

Ну а на счет полетов, то ни один гражданский самолет не летает 24 часа, на сколько я знаю максимум 14 - 15 часов в воздухе, ну а в среднем это 4-5 часов. И время полета - Flight Time считается с момента взлета (отрыва шасси от полосы) и до момента посадки (касания шасси полосы). Есть еще Block Time - это время с момента уборки колодок, до момента их установки (здесь, буксировка, руление, сам полет ну и в обратном порядке). Но это все лирика, для общего кругозору.
15 июл 14, 09:46    [16305087]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
ac1-caesar
Member

Откуда:
Сообщений: 19
Ребят, у меня возник вопрос относительно хранения общего налета для самолета.

Подскажите пожалуйста, в каком формате лучше хранить общий налет для самолета на момент начала эксплуатации? (пока сделал тпи int, но там минуты не покажешь, возможно лучше в decimal или money?) Спрашиваю профи, чтобы потом не мучиться в вычислениях.

К примеру для одного самолета "А" на момент покупки (поступления в компанию) общий налет составил 48634:25 часов (как его записать?).
Далее при помощи запроса ранее выстроенного можно сделать нарастающий итог общего налета, т.е. время полета будет приплюсовываться к общему налету.
15 июл 14, 12:40    [16306038]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с использованием формата времени  [new]
west74
Member

Откуда: Челябинск
Сообщений: 76
ac1-caesar,
а есть смысл хранить начальный налет отдельно ?
создай 0 полет на период с "[Дата поступления]-налет до покупки" до "[Дата поступления]"
проще суммировать общий ресурс будет. в запросах.


тоже самое с суммарным налетом , его всегда можно посчитать, сколько полетов 20-50 тыс на борт , ерунда для сервака.
17 июл 14, 13:08    [16318086]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить