将SQL转换为函数

Question

我是SQL的新手，有没有人可以帮我把下面的SQL语句转换成函数。这个语句起作用了，我只是不能用它来创建函数。

declare @start datetime, @end datetime
set @start = '2012/07/25 09:00:00'
set @end = '2012/07/25 12:55:00';

with weeks as (
  select @start as WeekStart
    , dateadd(hh, 24, dateadd(dd, 0, datediff(dd, 0, dateadd(dd, 6 - (@@datefirst + datepart(dw, @start)) % 7, @start)))) as WeekEnd
union all
  select dateadd(hh, 48, WeekEnd)
    , dateadd(hh, 24, dateadd(dd, 0, datediff(dd, 0, dateadd(dd, 13 - (@@datefirst + datepart(dw, WeekEnd)) % 7, WeekEnd)))) as WeekEnd
  from weeks
  where dateadd(hh, 48, WeekEnd) <= @end
)
select Seconds / (60 * 60) as Hours
from (
  select sum(datediff(ss, WeekStart, case when @end < WeekEnd then @end else WeekEnd end)) as Seconds
  from weeks) x

Answer 1

假设SQL Server

create function CalculateHours
(
    @Start datetime,
    @End datetime
) returns int
as
begin
    declare @Hours int
    ;with weeks as (
      select @start as WeekStart
        , dateadd(hh, 24, dateadd(dd, 0, datediff(dd, 0, dateadd(dd, 6 - (@@datefirst + datepart(dw, @start)) % 7, @start)))) as WeekEnd
    union all
      select dateadd(hh, 48, WeekEnd)
        , dateadd(hh, 24, dateadd(dd, 0, datediff(dd, 0, dateadd(dd, 13 - (@@datefirst + datepart(dw, WeekEnd)) % 7, WeekEnd)))) as WeekEnd
      from weeks
      where dateadd(hh, 48, WeekEnd) <= @end
    )
    select @Hours = Seconds / (60 * 60)
    from (
      select sum(datediff(ss, WeekStart, case when @end < WeekEnd then @end else WeekEnd end)) as Seconds
      from weeks) x

      return @Hours
end

示例用法：

select dbo.CalculateHours('2012/07/25 09:00:00','2012/07/25 12:55:00')