Convert SQL to a function

Question

I am new to SQL can somebody please help to convert the following SQL Statements into a function. This statement works, I just cant create a function out of this.

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

What would you like this function to return? What parameters would you like it to accept? Is there anything else we can do for you? You've posted incomplete SQL, and no specifications for what you're really looking for as output. Please edit your question and provide more details about what you're trying to do, and add the additional missing SQL. It's hard to tell from the partial post here what exactly you're trying to obtain. — Ken White
– Ken White, Commented Jul 25, 2012 at 2:58
Thanks for the quick reply. This SQL Statement takes Start date and End date and returns me no of Hours between them Excluding weekends. I copied the complete SQL, with two variables. So i would like this function to accept two Parameters, StartDate and EndDate and return me no of Hours. — D for Developer
– D for Developer, Commented Jul 25, 2012 at 3:02

Bert · Accepted Answer · 2012-07-25 03:16:49Z

Assuming 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

Example use:

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

Collectives™ on Stack Overflow

Convert SQL to a function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related