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

Откуда: Нижний Новгород
Сообщений: 14
Встала задача транслировать запрос из MySQL в MS SQL:

Запрос на мускуле:
select Param2, TranUser, sum(time_by_day) as time_by_dayinterval from 
   (select Param2, TranUser, max(trandatetime) - min(trandatetime) as time_by_day
    from translog
    where trandatetime between '$date1' and '$date2'
    group by param2, TranUser,trandatetime )v
group by param2,tranuser order by tranuser

На MS SQL не отрабатывает функиция sum(). Вложенный запрос отрабатывает нормально.

$date1,$date2 - в передлах одного года.
trandatetime в MS SQL - тип datetime (2008-01-04 00:00:00)

Суть запроса в том, что для каждого param2 вычисляется количество часов в день и затем суммируется за весь период.
На выходе должно получится суммированное количество часов и минут за весь период от $date1 до $date2.
На postgres и mysql все отлично отрабатывает.

Есть идеи как возможно реализовать это на MS SQL?
29 май 09, 15:36    [7245215]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
richard52
Встала задача транслировать запрос из MySQL в MS SQL:

Запрос на мускуле:
select Param2, TranUser, sum(time_by_day) as time_by_dayinterval from 
   (select Param2, TranUser, max(trandatetime) - min(trandatetime) as time_by_day
    from translog
    where trandatetime between '$date1' and '$date2'
    group by param2, TranUser,trandatetime )v
group by param2,tranuser order by tranuser

На MS SQL не отрабатывает функиция sum(). Вложенный запрос отрабатывает нормально.

$date1,$date2 - в передлах одного года.
trandatetime в MS SQL - тип datetime (2008-01-04 00:00:00)

Суть запроса в том, что для каждого param2 вычисляется количество часов в день и затем суммируется за весь период.
На выходе должно получится суммированное количество часов и минут за весь период от $date1 до $date2.
На postgres и mysql все отлично отрабатывает.

Есть идеи как возможно реализовать это на MS SQL?


Попробуй вместо вычитания дат использовать функцию datediff. Ибо обычное вычитание возвращается с типом datetime, а к нему sum() как-то стрёмно применять.
Например, datediff(day,min(trandatetime),max(trandatetime))
если в часах, то вместо day - hour.
29 май 09, 15:42    [7245268]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
richard52
Member

Откуда: Нижний Новгород
Сообщений: 14
C datedif пробовал вариант...
datediff(hour,min(trandatetime),max(trandatetime))

Фигня в том, что он тогда считает тупо часы, а нужно еще и минуты считать.
29 май 09, 15:47    [7245295]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
Glory
Member

Откуда:
Сообщений: 104760
richard52


На MS SQL не отрабатывает функиция sum(). Вложенный запрос отрабатывает нормально.

Так и пишет - "Не отрабатываю функицию sum()" ?
29 май 09, 15:47    [7245296]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
Glory
Member

Откуда:
Сообщений: 104760
richard52
C datedif пробовал вариант...
datediff(hour,min(trandatetime),max(trandatetime))

Фигня в том, что он тогда считает тупо часы, а нужно еще и минуты считать.

Вообще то разницу datetime сервер счивает в днях
А datediff(hour - как ни странно в часах
29 май 09, 15:48    [7245302]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
richard52
Member

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

Так и пишет - "Не отрабатываю функицию sum()" ?


По большому счету так и пишет.
Operand data type datetime is invalid for sum operator.


На mysql и pgsql типа даты date нормально отрабатывается sum()
29 май 09, 15:49    [7245311]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
Glory
Member

Откуда:
Сообщений: 104760
richard52
Glory

Так и пишет - "Не отрабатываю функицию sum()" ?


По большому счету так и пишет.
Operand data type datetime is invalid for sum operator.


Это значит, что вы передали в sum неправильный тип данных. А не то, что сервер не отрабатывает
29 май 09, 15:51    [7245315]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
richard52
C datedif пробовал вариант...
datediff(hour,min(trandatetime),max(trandatetime))

Фигня в том, что он тогда считает тупо часы, а нужно еще и минуты считать.

Кто мешает использовать
datediff(minute,...
?
29 май 09, 15:51    [7245317]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
richard52
Member

Откуда: Нижний Новгород
Сообщений: 14
Да в принципе никто, но как-то некрасиво получается... Т.е. результат потом в обратную сторону чемто внешним конвертить? Или есть возможность конвертнуть уже полученный просуммированный результат из intа в datetime?
29 май 09, 15:59    [7245367]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
Glory
Member

Откуда:
Сообщений: 104760
richard52
Да в принципе никто, но как-то некрасиво получается... Т.е. результат потом в обратную сторону чемто внешним конвертить? Или есть возможность конвертнуть уже полученный просуммированный результат из intа в datetime?

Просто интересно, а в какой результат должно по-вашему превратиться к примеру 10000 ?
29 май 09, 16:02    [7245387]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
проходящий.
Guest
richard52
Да в принципе никто, но как-то некрасиво получается... Т.е. результат потом в обратную сторону чемто внешним конвертить? Или есть возможность конвертнуть уже полученный просуммированный результат из intа в datetime?
А количество часов и минут - это datetime? Или таки это просто число?
29 май 09, 16:03    [7245394]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
richard52
Member

Откуда: Нижний Новгород
Сообщений: 14
Как я писал выше:
На выходе должно получится суммированное количество часов и минут за весь период от $date1 до $date2.


т.е. из 10000 должно получиться 166 часов и еще сколько-то минут...
29 май 09, 16:04    [7245415]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
Glory
Member

Откуда:
Сообщений: 104760
richard52
Как я писал выше:
На выходе должно получится суммированное количество часов и минут за весь период от $date1 до $date2.


т.е. из 10000 должно получиться 166 часов и еще сколько-то минут...

И какой же это будет datetime ? Где-то 05.01.1900 ? Или 05.01.2101 ?
29 май 09, 16:07    [7245435]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
richard52
Member

Откуда: Нижний Новгород
Сообщений: 14
проходящий.
А количество часов и минут - это datetime? Или таки это просто число?


В результате запроса с использованием datediff() мы получаем на выходе int.

Просто крайне не охота придумывать какие-то костыли, если есть возможность средствами transact sql конвертнуть... Вопрос в том, есть ли такая возможность.
29 май 09, 16:08    [7245448]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
проходящий.
Guest
richard52
проходящий.
А количество часов и минут - это datetime? Или таки это просто число?


В результате запроса с использованием datediff() мы получаем на выходе int.

Просто крайне не охота придумывать какие-то костыли, если есть возможность средствами transact sql конвертнуть... Вопрос в том, есть ли такая возможность.
А Вы сами верите в возможность однозначного конвертирования отрезка времени в точку на временнОй шкале?
29 май 09, 16:10    [7245458]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
richard52
Member

Откуда: Нижний Новгород
Сообщений: 14
Glory,
Да хоть бы и такой: 1900-01-01 13:44:33.000 - собственно данный формат получается на выходе из вложенного запроса
select Param2, TranUser, max(trandatetime) - min(trandatetime) as time_by_day
29 май 09, 16:10    [7245460]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
Glory
Member

Откуда:
Сообщений: 104760
richard52
проходящий.
А количество часов и минут - это datetime? Или таки это просто число?


В результате запроса с использованием datediff() мы получаем на выходе int.

Просто крайне не охота придумывать какие-то костыли, если есть возможность средствами transact sql конвертнуть... Вопрос в том, есть ли такая возможность.

Ну так может вы определитесь тогда, результат какого типа и вида вам нужен ?
29 май 09, 16:11    [7245468]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
проходящий.
Guest
richard52
Glory,
Да хоть бы и такой: 1900-01-01 13:44:33.000 - собственно данный формат получается на выходе из вложенного запроса
select Param2, TranUser, max(trandatetime) - min(trandatetime) as time_by_day
А если полученный результат будет превышать 28 суток? В каких месяцах будем считать?
29 май 09, 16:12    [7245474]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
Glory
Member

Откуда:
Сообщений: 104760
richard52
Glory,
Да хоть бы и такой: 1900-01-01 13:44:33.000 - собственно данный формат получается на выходе из вложенного запроса
select Param2, TranUser, max(trandatetime) - min(trandatetime) as time_by_day

"Хоть какой" - это не ответ. Нужен точный тип нужного вам результата
29 май 09, 16:12    [7245475]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
richard52
проходящий.
А количество часов и минут - это datetime? Или таки это просто число?


В результате запроса с использованием datediff() мы получаем на выходе int.

Просто крайне не охота придумывать какие-то костыли, если есть возможность средствами transact sql конвертнуть... Вопрос в том, есть ли такая возможность.

У тебя есть кол-во минут типа int... допустим, @mins. Вот количество часов:
@mins/60
А вот количество оставшихся минут:
@mins%60
29 май 09, 16:12    [7245479]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Как-то так?
29 май 09, 16:20    [7245534]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
richard52
Member

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

Да, работает... спасибо. Очень жалко, что нет более красивого решения, но и этого мне вполне хватит.

Итоговый запрос на Transact SQL
select Param2, TranUser, sum(time_by_day)/60 as hours,sum(time_by_day)%60 as minutes from(
select param2,tranuser,datediff(minute,min(trandatetime),max(trandatetime)) as time_by_day from translog 
where (trancode=64 or trancode=65)
and param2=9479981 
and trandatetime between '2008-01-04 00:00:00' and '2008-01-04 23:59:59'
and tranuser IS NOT NULL
group by param2,tranuser)v
group by param2,tranuser order by tranuser
29 май 09, 16:21    [7245542]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
richard52
Member

Откуда: Нижний Новгород
Сообщений: 14
Хм... после тестов оказалось, что при подсчете за период времени больше чем за день, подзапрос выбирает минимальное и максимальное время из всего диапазона... и соответственно время получается не сумма часов за каждый день, а сумма часов за весь период времени.
29 май 09, 17:19    [7245922]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
iljy
Guest
richard52
Хм... после тестов оказалось, что при подсчете за период времени больше чем за день, подзапрос выбирает минимальное и максимальное время из всего диапазона... и соответственно время получается не сумма часов за каждый день, а сумма часов за весь период времени.


что вам вернет вот такой запрос
select param2,tranuser, min(trandatetime),max(trandatetime) from translog 
where (trancode=64 or trancode=65)
and param2=9479981 
and trandatetime between @StartDate and @EndDate
and tranuser IS NOT NULL
group by param2,tranuser
если диапазон больше дня? хотите сказать min и max даты будут вне диапазона??
а вообще - запрос ваш странный... а исходный был еще страннее, при вот такой группировке
group by param2, TranUser,trandatetime
max(trandatetime) - min(trandatetime) всегда = 0
29 май 09, 17:35    [7245997]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием даты.  [new]
richard52
Member

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

не катит...
у меня в таблице нет минимального и максимального времени... их надо получать подзапросом...
29 май 09, 17:48    [7246067]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить