>I need help to turn this SP into a view or a table that contains resulting
>records.
>Any help is greatly appreciated.
>
>Bill
>
>
>
>set ANSI_NULLS OFF
>set QUOTED_IDENTIFIER OFF
>GO
>ALTER proc [dbo].[up_rs_hours_of_service]
>as
>set nocount on
>set transaction isolation level read uncommitted
>declare
>  @begdate      varchar(19),
>  @enddate      varchar(19),
>  @driverkey    int,
>  @begdriverid  varchar(16),
>  @enddriverid  varchar(16),
>  @sitekey      varchar(4),
>  @filterclause varchar(1250)
>select @begdate  = '01/01/2005'
>select @enddate    = dateadd(second, 1, '12/31/2005')
>select @begdriverid = 1
>select @enddriverid =999
>select @sitekey = 1
>
>create table #sitetimes (
>  sitekey      integer,
>  adjstarttime datetime,
>  adjendtime   datetime
>)
>exec up_rs_site_times @sitekey, @begdate, @enddate
>select @begdate = convert(varchar(19), min(adjstarttime) - 7, 120),
>       @enddate = convert(varchar(19), max(adjendtime) + 7, 120)
>  from #sitetimes
>create table #events (
>  eventkey int not null ,
>  datetimestamp datetime not null,
>  eventnum int not null ,
>  sitekey int not null ,
>  driverkey int not null ,
>  intdata2 int null
>)
>create table #hoursofservice (
>  sitekey    int,
>  driverkey  int,
>  onduty     int,
>  offduty    int,
>  sleeper    int,
>  drive      int
>)
>exec( 'insert into #events '+
>      'select t1.eventkey, '+
>      '       t1.datetimestamp, '+
>      '       t1.eventnum, '+
>      '       t1.sitekey, '+
>      '       t1.driverkey, '+
>      '       t1.intdata2 '+
>      '  from t_eventslog t1 '+
>      '       inner join s_drivers s1 on t1.driverkey = s1.driverkey and
>s1.driverkey <> 0 '+
>      '       inner join #sitetimes s2 on s2.sitekey = t1.sitekey '+
>      ' where t1.datetimestamp between ''' + @begdate + ''' and ''' +
>@enddate + ''' and '+
>      '       s1.driverid between ''' + @begdriverid + ''' and ''' +
>@enddriverid + ''' and '+
>      '       t1.eventnum between 1101 and 1108')
>create index [intdata2_index] on #events([intdata2]) on [primary]
>insert into #events
>select t1.eventkey,
>       t1.datetimestamp,
>       t1.eventnum,
>       t1.sitekey,
>       t1.driverkey,
>       t1.intdata2
>  from t_eventslog t1
>       inner join #events e1 on e1.intdata2 = t1.eventkey
>       inner join #sitetimes s2 on s2.sitekey = t1.sitekey
> where t1.datetimestamp not between @begdate and @enddate and
>       t1.eventnum between 1101 and 1108
>create index [eventkey_index] on #events([eventkey]) on [primary]
>create index [eventnum_index] on #events([eventnum]) on [primary]
>insert into #hoursofservice
>select t1.sitekey, t1.driverkey,
>       case t1.eventnum
>         when 1101 then
>           sum(datediff(second, case
>                                  when t1.datetimestamp < s2.adjstarttime
>then s2.adjstarttime
>                                  else t1.datetimestamp
>                                end,
>                                case
>                                  when t2.datetimestamp > s2.adjendtime then
>s2.adjendtime
>                                  else t2.datetimestamp
>                                end))
>       end,
>       case t1.eventnum
>         when 1103 then
>           sum(datediff(second, case
>                                  when t1.datetimestamp < s2.adjstarttime
>then s2.adjstarttime
>                                  else t1.datetimestamp
>                                end,
>                                case
>                                  when t2.datetimestamp > s2.adjendtime then
>s2.adjendtime
>                                  else t2.datetimestamp
>                                end))
>       end,
>       case t1.eventnum
>         when 1105 then
>           sum(datediff(second, case
>                                  when t1.datetimestamp < s2.adjstarttime
>then s2.adjstarttime
>                                  else t1.datetimestamp
>                                end,
>                                case
>                                  when t2.datetimestamp > s2.adjendtime then
>s2.adjendtime
>                                  else t2.datetimestamp
>                                end))
>       end,
>       case t1.eventnum
>         when 1107 then
>           sum(datediff(second, case
>                                  when t1.datetimestamp < s2.adjstarttime
>then s2.adjstarttime
>                                  else t1.datetimestamp
>                                end,
>                                case
>                                  when t2.datetimestamp > s2.adjendtime then
>s2.adjendtime
>                                  else t2.datetimestamp
>                                end))
>       end
>  from #events t1
>       inner join #events t2 on t1.intdata2 = t2.eventkey
>         inner join #sitetimes s2 on s2.sitekey = t1.sitekey
> where t1.eventnum in (1101, 1103, 1105, 1107) and
>       (t1.datetimestamp between s2.adjstarttime and s2.adjendtime or
>        t2.datetimestamp between s2.adjstarttime and s2.adjendtime or
>        (t1.datetimestamp < s2.adjstarttime and
>         t2.datetimestamp > s2.adjendtime))
>group by t1.sitekey, t1.driverkey, t1.eventnum
>
>
> 
>

"Steve Kass" <sk***@drew.edu> wrote in message
news:OK8NGkciGHA.3816@TK2MSFTNGP02.phx.gbl...
> Bill,
>
> It's much easier to insert the results of the sp into a
> table, then use that.  You can just
>
> create table hours_of_service (
>  columns and types to match the sp result
> )
> insert into hours_of_service exec up_rs_hours_of_service
>
> While it's possible to make the result of this into a
> view, trust me, it's a horrible idea.  The only way I
> know to do it in T-SQL is to use a loopback linked server and
> select from an OPENQUERY statement, and even then, you
> have to jump through a few hoops to make it "work".
> -- Steve Kass
> -- Drew University
> -- http://www.stevekass.com
>
> Bill Nguyen wrote:
>
>>I need help to turn this SP into a view or a table that contains resulting
>>records.
>>Any help is greatly appreciated.
>>
>>Bill
>>
>>
>>
>>set ANSI_NULLS OFF
>>set QUOTED_IDENTIFIER OFF
>>GO
>>ALTER proc [dbo].[up_rs_hours_of_service]
>>as
>>set nocount on
>>set transaction isolation level read uncommitted
>>declare
>>  @begdate      varchar(19),
>>  @enddate      varchar(19),
>>  @driverkey    int,
>>  @begdriverid  varchar(16),
>>  @enddriverid  varchar(16),
>>  @sitekey      varchar(4),
>>  @filterclause varchar(1250)
>>select @begdate  = '01/01/2005'
>>select @enddate    = dateadd(second, 1, '12/31/2005')
>>select @begdriverid = 1
>>select @enddriverid =999
>>select @sitekey = 1
>>
>>create table #sitetimes (
>>  sitekey      integer,
>>  adjstarttime datetime,
>>  adjendtime   datetime
>>)
>>exec up_rs_site_times @sitekey, @begdate, @enddate
>>select @begdate = convert(varchar(19), min(adjstarttime) - 7, 120),
>>       @enddate = convert(varchar(19), max(adjendtime) + 7, 120)
>>  from #sitetimes
>>create table #events (
>>  eventkey int not null ,
>>  datetimestamp datetime not null,
>>  eventnum int not null ,
>>  sitekey int not null ,
>>  driverkey int not null ,
>>  intdata2 int null
>>)
>>create table #hoursofservice (
>>  sitekey    int,
>>  driverkey  int,
>>  onduty     int,
>>  offduty    int,
>>  sleeper    int,
>>  drive      int
>>)
>>exec( 'insert into #events '+
>>      'select t1.eventkey, '+
>>      '       t1.datetimestamp, '+
>>      '       t1.eventnum, '+
>>      '       t1.sitekey, '+
>>      '       t1.driverkey, '+
>>      '       t1.intdata2 '+
>>      '  from t_eventslog t1 '+
>>      '       inner join s_drivers s1 on t1.driverkey = s1.driverkey and
>> s1.driverkey <> 0 '+
>>      '       inner join #sitetimes s2 on s2.sitekey = t1.sitekey '+
>>      ' where t1.datetimestamp between ''' + @begdate + ''' and ''' +
>> @enddate + ''' and '+
>>      '       s1.driverid between ''' + @begdriverid + ''' and ''' +
>> @enddriverid + ''' and '+
>>      '       t1.eventnum between 1101 and 1108')
>>create index [intdata2_index] on #events([intdata2]) on [primary]
>>insert into #events
>>select t1.eventkey,
>>       t1.datetimestamp,
>>       t1.eventnum,
>>       t1.sitekey,
>>       t1.driverkey,
>>       t1.intdata2
>>  from t_eventslog t1
>>       inner join #events e1 on e1.intdata2 = t1.eventkey
>>       inner join #sitetimes s2 on s2.sitekey = t1.sitekey
>> where t1.datetimestamp not between @begdate and @enddate and
>>       t1.eventnum between 1101 and 1108
>>create index [eventkey_index] on #events([eventkey]) on [primary]
>>create index [eventnum_index] on #events([eventnum]) on [primary]
>>insert into #hoursofservice
>>select t1.sitekey, t1.driverkey,
>>       case t1.eventnum
>>         when 1101 then
>>           sum(datediff(second, case
>>                                  when t1.datetimestamp < s2.adjstarttime
>> then s2.adjstarttime
>>                                  else t1.datetimestamp
>>                                end,
>>                                case
>>                                  when t2.datetimestamp > s2.adjendtime
>> then s2.adjendtime
>>                                  else t2.datetimestamp
>>                                end))
>>       end,
>>       case t1.eventnum
>>         when 1103 then
>>           sum(datediff(second, case
>>                                  when t1.datetimestamp < s2.adjstarttime
>> then s2.adjstarttime
>>                                  else t1.datetimestamp
>>                                end,
>>                                case
>>                                  when t2.datetimestamp > s2.adjendtime
>> then s2.adjendtime
>>                                  else t2.datetimestamp
>>                                end))
>>       end,
>>       case t1.eventnum
>>         when 1105 then
>>           sum(datediff(second, case
>>                                  when t1.datetimestamp < s2.adjstarttime
>> then s2.adjstarttime
>>                                  else t1.datetimestamp
>>                                end,
>>                                case
>>                                  when t2.datetimestamp > s2.adjendtime
>> then s2.adjendtime
>>                                  else t2.datetimestamp
>>                                end))
>>       end,
>>       case t1.eventnum
>>         when 1107 then
>>           sum(datediff(second, case
>>                                  when t1.datetimestamp < s2.adjstarttime
>> then s2.adjstarttime
>>                                  else t1.datetimestamp
>>                                end,
>>                                case
>>                                  when t2.datetimestamp > s2.adjendtime
>> then s2.adjendtime
>>                                  else t2.datetimestamp
>>                                end))
>>       end
>>  from #events t1
>>       inner join #events t2 on t1.intdata2 = t2.eventkey
>>         inner join #sitetimes s2 on s2.sitekey = t1.sitekey
>> where t1.eventnum in (1101, 1103, 1105, 1107) and
>>       (t1.datetimestamp between s2.adjstarttime and s2.adjendtime or
>>        t2.datetimestamp between s2.adjstarttime and s2.adjendtime or
>>        (t1.datetimestamp < s2.adjstarttime and
>>         t2.datetimestamp > s2.adjendtime))
>>group by t1.sitekey, t1.driverkey, t1.eventnum
>>
>>
>>

>Thanks Steve;
>
>I'll try your solution then.
>
>Bill
>
>"Steve Kass" <sk***@drew.edu> wrote in message
>news:OK8NGkciGHA.3816@TK2MSFTNGP02.phx.gbl...
> 
>
>>Bill,
>>
>>It's much easier to insert the results of the sp into a
>>table, then use that.  You can just
>>
>>create table hours_of_service (
>> columns and types to match the sp result
>>)
>>insert into hours_of_service exec up_rs_hours_of_service
>>
>>While it's possible to make the result of this into a
>>view, trust me, it's a horrible idea.  The only way I
>>know to do it in T-SQL is to use a loopback linked server and
>>select from an OPENQUERY statement, and even then, you
>>have to jump through a few hoops to make it "work".
>>-- Steve Kass
>>-- Drew University
>>-- http://www.stevekass.com
>>
>>Bill Nguyen wrote:
>>
>>   
>>
>>>I need help to turn this SP into a view or a table that contains resulting
>>>records.
>>>Any help is greatly appreciated.
>>>
>>>Bill
>>>
>>>
>>>
>>>set ANSI_NULLS OFF
>>>set QUOTED_IDENTIFIER OFF
>>>GO
>>>ALTER proc [dbo].[up_rs_hours_of_service]
>>>as
>>>set nocount on
>>>set transaction isolation level read uncommitted
>>>declare
>>> @begdate      varchar(19),
>>> @enddate      varchar(19),
>>> @driverkey    int,
>>> @begdriverid  varchar(16),
>>> @enddriverid  varchar(16),
>>> @sitekey      varchar(4),
>>> @filterclause varchar(1250)
>>>select @begdate  = '01/01/2005'
>>>select @enddate    = dateadd(second, 1, '12/31/2005')
>>>select @begdriverid = 1
>>>select @enddriverid =999
>>>select @sitekey = 1
>>>
>>>create table #sitetimes (
>>> sitekey      integer,
>>> adjstarttime datetime,
>>> adjendtime   datetime
>>>)
>>>exec up_rs_site_times @sitekey, @begdate, @enddate
>>>select @begdate = convert(varchar(19), min(adjstarttime) - 7, 120),
>>>      @enddate = convert(varchar(19), max(adjendtime) + 7, 120)
>>> from #sitetimes
>>>create table #events (
>>> eventkey int not null ,
>>> datetimestamp datetime not null,
>>> eventnum int not null ,
>>> sitekey int not null ,
>>> driverkey int not null ,
>>> intdata2 int null
>>>)
>>>create table #hoursofservice (
>>> sitekey    int,
>>> driverkey  int,
>>> onduty     int,
>>> offduty    int,
>>> sleeper    int,
>>> drive      int
>>>)
>>>exec( 'insert into #events '+
>>>     'select t1.eventkey, '+
>>>     '       t1.datetimestamp, '+
>>>     '       t1.eventnum, '+
>>>     '       t1.sitekey, '+
>>>     '       t1.driverkey, '+
>>>     '       t1.intdata2 '+
>>>     '  from t_eventslog t1 '+
>>>     '       inner join s_drivers s1 on t1.driverkey = s1.driverkey and
>>>s1.driverkey <> 0 '+
>>>     '       inner join #sitetimes s2 on s2.sitekey = t1.sitekey '+
>>>     ' where t1.datetimestamp between ''' + @begdate + ''' and ''' +
>>>@enddate + ''' and '+
>>>     '       s1.driverid between ''' + @begdriverid + ''' and ''' +
>>>@enddriverid + ''' and '+
>>>     '       t1.eventnum between 1101 and 1108')
>>>create index [intdata2_index] on #events([intdata2]) on [primary]
>>>insert into #events
>>>select t1.eventkey,
>>>      t1.datetimestamp,
>>>      t1.eventnum,
>>>      t1.sitekey,
>>>      t1.driverkey,
>>>      t1.intdata2
>>> from t_eventslog t1
>>>      inner join #events e1 on e1.intdata2 = t1.eventkey
>>>      inner join #sitetimes s2 on s2.sitekey = t1.sitekey
>>>where t1.datetimestamp not between @begdate and @enddate and
>>>      t1.eventnum between 1101 and 1108
>>>create index [eventkey_index] on #events([eventkey]) on [primary]
>>>create index [eventnum_index] on #events([eventnum]) on [primary]
>>>insert into #hoursofservice
>>>select t1.sitekey, t1.driverkey,
>>>      case t1.eventnum
>>>        when 1101 then
>>>          sum(datediff(second, case
>>>                                 when t1.datetimestamp < s2.adjstarttime
>>>then s2.adjstarttime
>>>                                 else t1.datetimestamp
>>>                               end,
>>>                               case
>>>                                 when t2.datetimestamp > s2.adjendtime
>>>then s2.adjendtime
>>>                                 else t2.datetimestamp
>>>                               end))
>>>      end,
>>>      case t1.eventnum
>>>        when 1103 then
>>>          sum(datediff(second, case
>>>                                 when t1.datetimestamp < s2.adjstarttime
>>>then s2.adjstarttime
>>>                                 else t1.datetimestamp
>>>                               end,
>>>                               case
>>>                                 when t2.datetimestamp > s2.adjendtime
>>>then s2.adjendtime
>>>                                 else t2.datetimestamp
>>>                               end))
>>>      end,
>>>      case t1.eventnum
>>>        when 1105 then
>>>          sum(datediff(second, case
>>>                                 when t1.datetimestamp < s2.adjstarttime
>>>then s2.adjstarttime
>>>                                 else t1.datetimestamp
>>>                               end,
>>>                               case
>>>                                 when t2.datetimestamp > s2.adjendtime
>>>then s2.adjendtime
>>>                                 else t2.datetimestamp
>>>                               end))
>>>      end,
>>>      case t1.eventnum
>>>        when 1107 then
>>>          sum(datediff(second, case
>>>                                 when t1.datetimestamp < s2.adjstarttime
>>>then s2.adjstarttime
>>>                                 else t1.datetimestamp
>>>                               end,
>>>                               case
>>>                                 when t2.datetimestamp > s2.adjendtime
>>>then s2.adjendtime
>>>                                 else t2.datetimestamp
>>>                               end))
>>>      end
>>> from #events t1
>>>      inner join #events t2 on t1.intdata2 = t2.eventkey
>>>        inner join #sitetimes s2 on s2.sitekey = t1.sitekey
>>>where t1.eventnum in (1101, 1103, 1105, 1107) and
>>>      (t1.datetimestamp between s2.adjstarttime and s2.adjendtime or
>>>       t2.datetimestamp between s2.adjstarttime and s2.adjendtime or
>>>       (t1.datetimestamp < s2.adjstarttime and
>>>        t2.datetimestamp > s2.adjendtime))
>>>group by t1.sitekey, t1.driverkey, t1.eventnum
>>>
>>>
>>>
>>>     
>>>
>
>
> 
>

"Bill Nguyen" <billn_nospam_please@jaco.com> wrote in message
news:%23HW56MciGHA.2208@TK2MSFTNGP05.phx.gbl...
> I need help to turn this SP into a view or a table that contains resulting
> records.
> Any help is greatly appreciated.
>
> Bill
>
>
>
> set ANSI_NULLS OFF
> set QUOTED_IDENTIFIER OFF
> GO
> ALTER proc [dbo].[up_rs_hours_of_service]
> as
> set nocount on
> set transaction isolation level read uncommitted
> declare
>   @begdate      varchar(19),
>   @enddate      varchar(19),
>   @driverkey    int,
>   @begdriverid  varchar(16),
>   @enddriverid  varchar(16),
>   @sitekey      varchar(4),
>   @filterclause varchar(1250)
> select @begdate  = '01/01/2005'
> select @enddate    = dateadd(second, 1, '12/31/2005')
> select @begdriverid = 1
> select @enddriverid =999
> select @sitekey = 1
>
> create table #sitetimes (
>   sitekey      integer,
>   adjstarttime datetime,
>   adjendtime   datetime
> )
> exec up_rs_site_times @sitekey, @begdate, @enddate
> select @begdate = convert(varchar(19), min(adjstarttime) - 7, 120),
>        @enddate = convert(varchar(19), max(adjendtime) + 7, 120)
>   from #sitetimes
> create table #events (
>   eventkey int not null ,
>   datetimestamp datetime not null,
>   eventnum int not null ,
>   sitekey int not null ,
>   driverkey int not null ,
>   intdata2 int null
> )
> create table #hoursofservice (
>   sitekey    int,
>   driverkey  int,
>   onduty     int,
>   offduty    int,
>   sleeper    int,
>   drive      int
> )
> exec( 'insert into #events '+
>       'select t1.eventkey, '+
>       '       t1.datetimestamp, '+
>       '       t1.eventnum, '+
>       '       t1.sitekey, '+
>       '       t1.driverkey, '+
>       '       t1.intdata2 '+
>       '  from t_eventslog t1 '+
>       '       inner join s_drivers s1 on t1.driverkey = s1.driverkey and
> s1.driverkey <> 0 '+
>       '       inner join #sitetimes s2 on s2.sitekey = t1.sitekey '+
>       ' where t1.datetimestamp between ''' + @begdate + ''' and ''' +
> @enddate + ''' and '+
>       '       s1.driverid between ''' + @begdriverid + ''' and ''' +
> @enddriverid + ''' and '+
>       '       t1.eventnum between 1101 and 1108')
> create index [intdata2_index] on #events([intdata2]) on [primary]
> insert into #events
> select t1.eventkey,
>        t1.datetimestamp,
>        t1.eventnum,
>        t1.sitekey,
>        t1.driverkey,
>        t1.intdata2
>   from t_eventslog t1
>        inner join #events e1 on e1.intdata2 = t1.eventkey
>        inner join #sitetimes s2 on s2.sitekey = t1.sitekey
>  where t1.datetimestamp not between @begdate and @enddate and
>        t1.eventnum between 1101 and 1108
> create index [eventkey_index] on #events([eventkey]) on [primary]
> create index [eventnum_index] on #events([eventnum]) on [primary]
> insert into #hoursofservice
> select t1.sitekey, t1.driverkey,
>        case t1.eventnum
>          when 1101 then
>            sum(datediff(second, case
>                                   when t1.datetimestamp < s2.adjstarttime
> then s2.adjstarttime
>                                   else t1.datetimestamp
>                                 end,
>                                 case
>                                   when t2.datetimestamp > s2.adjendtime
then
> s2.adjendtime
>                                   else t2.datetimestamp
>                                 end))
>        end,
>        case t1.eventnum
>          when 1103 then
>            sum(datediff(second, case
>                                   when t1.datetimestamp < s2.adjstarttime
> then s2.adjstarttime
>                                   else t1.datetimestamp
>                                 end,
>                                 case
>                                   when t2.datetimestamp > s2.adjendtime
then
> s2.adjendtime
>                                   else t2.datetimestamp
>                                 end))
>        end,
>        case t1.eventnum
>          when 1105 then
>            sum(datediff(second, case
>                                   when t1.datetimestamp < s2.adjstarttime
> then s2.adjstarttime
>                                   else t1.datetimestamp
>                                 end,
>                                 case
>                                   when t2.datetimestamp > s2.adjendtime
then
> s2.adjendtime
>                                   else t2.datetimestamp
>                                 end))
>        end,
>        case t1.eventnum
>          when 1107 then
>            sum(datediff(second, case
>                                   when t1.datetimestamp < s2.adjstarttime
> then s2.adjstarttime
>                                   else t1.datetimestamp
>                                 end,
>                                 case
>                                   when t2.datetimestamp > s2.adjendtime
then
> s2.adjendtime
>                                   else t2.datetimestamp
>                                 end))
>        end
>   from #events t1
>        inner join #events t2 on t1.intdata2 = t2.eventkey
>          inner join #sitetimes s2 on s2.sitekey = t1.sitekey
>  where t1.eventnum in (1101, 1103, 1105, 1107) and
>        (t1.datetimestamp between s2.adjstarttime and s2.adjendtime or
>         t2.datetimestamp between s2.adjstarttime and s2.adjendtime or
>         (t1.datetimestamp < s2.adjstarttime and
>          t2.datetimestamp > s2.adjendtime))
> group by t1.sitekey, t1.driverkey, t1.eventnum
>
>

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:ekKGNQniGHA.1324@TK2MSFTNGP04.phx.gbl...
> Are you really using static values for your variables in this stored
> procedure?  Why are you using dynamic SQL with EXEC?  Won't static SQL
work
> here?
>
> For starters, does this code return the same results?
>
> ALTER PROC [DBO].[UP_RS_HOURS_OF_SERVICE]
> AS
>  SET NOCOUNT  ON
>  SET TRANSACTION  ISOLATION  LEVEL  READ  UNCOMMITTED
>
>  DECLARE
>   @begdate      VARCHAR(19),
>   @enddate      VARCHAR(19),
>   @driverkey    INT,
>   @begdriverid  VARCHAR(16),
>   @enddriverid  VARCHAR(16),
>   @sitekey      VARCHAR(4),
>   @filterclause VARCHAR(1250)
>
>  set @begdate = '01/01/2005'
>  set @enddate = DATEADD(SECOND,1,'12/31/2005')
>  set @begdriverid = 1
>  set @enddriverid = 999
>  set @sitekey = 1
>
>  CREATE TABLE #SITETIMES (
>   SITEKEY      INTEGER,
>   ADJSTARTTIME DATETIME,
>   ADJENDTIME   DATETIME)
>
>  EXEC UP_RS_SITE_TIMES  @sitekey , @begdate , @enddate
>
>  SELECT @begdate = CONVERT(VARCHAR(19),MIN(ADJSTARTTIME) - 7,120),
>   @enddate = CONVERT(VARCHAR(19),MAX(ADJENDTIME) + 7,120)
>  FROM   #SITETIMES
>  CREATE TABLE #EVENTS (
>   EVENTKEY      INT NOT NULL,
>   DATETIMESTAMP DATETIME NOT NULL,
>   EVENTNUM      INT NOT NULL,
>   SITEKEY       INT NOT NULL,
>   DRIVERKEY     INT NOT NULL,
>   INTDATA2      INT NULL)
>  CREATE TABLE #HOURSOFSERVICE (
>   SITEKEY   INT,
>   DRIVERKEY INT,
>   ONDUTY    INT,
>   OFFDUTY   INT,
>   SLEEPER   INT,
>   DRIVE     INT)
>
> insert into #events
> select t1.eventkey,
>  t1.datetimestamp,
>  t1.eventnum,
>  t1.sitekey,
>  t1.driverkey,
>  t1.intdata2
> from t_eventslog t1
> inner join s_drivers s1
>  on t1.driverkey = s1.driverkey
>  and s1.driverkey <> 0
> inner join #sitetimes s2
>  on s2.sitekey = t1.sitekey
>  where t1.datetimestamp between @begdate and @enddate
>  and s1.driverid between @begdriverid and @enddriverid
>  and t1.eventnum between 1101 and 1108
>
> CREATE INDEX [INTDATA2_INDEX] ON #EVENTS (
>  [INTDATA2]) ON [PRIMARY]
>
> INSERT INTO #EVENTS
> SELECT T1.EVENTKEY,
>  T1.DATETIMESTAMP,
>  T1.EVENTNUM,
>  T1.SITEKEY,
>  T1.DRIVERKEY,
>  T1.INTDATA2
> FROM T_EVENTSLOG T1
> INNER JOIN #EVENTS E1
>  ON E1.INTDATA2 = T1.EVENTKEY
> INNER JOIN #SITETIMES S2
>  ON S2.SITEKEY = T1.SITEKEY
> WHERE  T1.DATETIMESTAMP NOT BETWEEN @begdate AND @enddate
> AND T1.EVENTNUM BETWEEN 1101 AND 1108
>
> CREATE INDEX [EVENTKEY_INDEX] ON #EVENTS (
>   [EVENTKEY]) ON [PRIMARY]
>
> CREATE INDEX [EVENTNUM_INDEX] ON #EVENTS (
>   [EVENTNUM]) ON [PRIMARY]
>
> INSERT INTO #HOURSOFSERVICE
> SELECT   T1.SITEKEY,
>  T1.DRIVERKEY,
>  CASE T1.EVENTNUM
>  WHEN 1101 THEN SUM(DATEDIFF(SECOND,CASE
>  WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
>  ELSE T1.DATETIMESTAMP
>  END,CASE
>  WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
>  ELSE T2.DATETIMESTAMP
>  END))
>  END,
>  CASE T1.EVENTNUM
>  WHEN 1103 THEN SUM(DATEDIFF(SECOND,CASE
>  WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
>  ELSE T1.DATETIMESTAMP
>  END,CASE
>  WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
>  ELSE T2.DATETIMESTAMP
>  END))
>  END,
>  CASE T1.EVENTNUM
>  WHEN 1105 THEN SUM(DATEDIFF(SECOND,CASE
>  WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
>  ELSE T1.DATETIMESTAMP
>  END,CASE
>  WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
>  ELSE T2.DATETIMESTAMP
>  END))
>  END,
>  CASE T1.EVENTNUM
>  WHEN 1107 THEN SUM(DATEDIFF(SECOND,CASE
>  WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
>  ELSE T1.DATETIMESTAMP
>  END,CASE
>  WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
>  ELSE T2.DATETIMESTAMP
>  END))
>  END
> FROM #EVENTS T1
> INNER JOIN #EVENTS T2
>  ON T1.INTDATA2 = T2.EVENTKEY
> INNER JOIN #SITETIMES S2
>  ON S2.SITEKEY = T1.SITEKEY
> WHERE    T1.EVENTNUM IN (1101, 1103, 1105, 1107)
> AND (T1.DATETIMESTAMP BETWEEN S2.ADJSTARTTIME AND S2.ADJENDTIME
>      OR T2.DATETIMESTAMP BETWEEN S2.ADJSTARTTIME AND S2.ADJENDTIME
>      OR (T1.DATETIMESTAMP < S2.ADJSTARTTIME
>          AND T2.DATETIMESTAMP > S2.ADJENDTIME))
> GROUP BY T1.SITEKEY,
> T1.DRIVERKEY,
> T1.EVENTNUM
>
>
>
> "Bill Nguyen" <billn_nospam_please@jaco.com> wrote in message
> news:%23HW56MciGHA.2208@TK2MSFTNGP05.phx.gbl...
> > I need help to turn this SP into a view or a table that contains
resulting
> > records.
> > Any help is greatly appreciated.
> >
> > Bill
> >
> >
> >
> > set ANSI_NULLS OFF
> > set QUOTED_IDENTIFIER OFF
> > GO
> > ALTER proc [dbo].[up_rs_hours_of_service]
> > as
> > set nocount on
> > set transaction isolation level read uncommitted
> > declare
> >   @begdate      varchar(19),
> >   @enddate      varchar(19),
> >   @driverkey    int,
> >   @begdriverid  varchar(16),
> >   @enddriverid  varchar(16),
> >   @sitekey      varchar(4),
> >   @filterclause varchar(1250)
> > select @begdate  = '01/01/2005'
> > select @enddate    = dateadd(second, 1, '12/31/2005')
> > select @begdriverid = 1
> > select @enddriverid =999
> > select @sitekey = 1
> >
> > create table #sitetimes (
> >   sitekey      integer,
> >   adjstarttime datetime,
> >   adjendtime   datetime
> > )
> > exec up_rs_site_times @sitekey, @begdate, @enddate
> > select @begdate = convert(varchar(19), min(adjstarttime) - 7, 120),
> >        @enddate = convert(varchar(19), max(adjendtime) + 7, 120)
> >   from #sitetimes
> > create table #events (
> >   eventkey int not null ,
> >   datetimestamp datetime not null,
> >   eventnum int not null ,
> >   sitekey int not null ,
> >   driverkey int not null ,
> >   intdata2 int null
> > )
> > create table #hoursofservice (
> >   sitekey    int,
> >   driverkey  int,
> >   onduty     int,
> >   offduty    int,
> >   sleeper    int,
> >   drive      int
> > )
> > exec( 'insert into #events '+
> >       'select t1.eventkey, '+
> >       '       t1.datetimestamp, '+
> >       '       t1.eventnum, '+
> >       '       t1.sitekey, '+
> >       '       t1.driverkey, '+
> >       '       t1.intdata2 '+
> >       '  from t_eventslog t1 '+
> >       '       inner join s_drivers s1 on t1.driverkey = s1.driverkey and
> > s1.driverkey <> 0 '+
> >       '       inner join #sitetimes s2 on s2.sitekey = t1.sitekey '+
> >       ' where t1.datetimestamp between ''' + @begdate + ''' and ''' +
> > @enddate + ''' and '+
> >       '       s1.driverid between ''' + @begdriverid + ''' and ''' +
> > @enddriverid + ''' and '+
> >       '       t1.eventnum between 1101 and 1108')
> > create index [intdata2_index] on #events([intdata2]) on [primary]
> > insert into #events
> > select t1.eventkey,
> >        t1.datetimestamp,
> >        t1.eventnum,
> >        t1.sitekey,
> >        t1.driverkey,
> >        t1.intdata2
> >   from t_eventslog t1
> >        inner join #events e1 on e1.intdata2 = t1.eventkey
> >        inner join #sitetimes s2 on s2.sitekey = t1.sitekey
> >  where t1.datetimestamp not between @begdate and @enddate and
> >        t1.eventnum between 1101 and 1108
> > create index [eventkey_index] on #events([eventkey]) on [primary]
> > create index [eventnum_index] on #events([eventnum]) on [primary]
> > insert into #hoursofservice
> > select t1.sitekey, t1.driverkey,
> >        case t1.eventnum
> >          when 1101 then
> >            sum(datediff(second, case
> >                                   when t1.datetimestamp <
s2.adjstarttime
> > then s2.adjstarttime
> >                                   else t1.datetimestamp
> >                                 end,
> >                                 case
> >                                   when t2.datetimestamp > s2.adjendtime
> then
> > s2.adjendtime
> >                                   else t2.datetimestamp
> >                                 end))
> >        end,
> >        case t1.eventnum
> >          when 1103 then
> >            sum(datediff(second, case
> >                                   when t1.datetimestamp <
s2.adjstarttime
> > then s2.adjstarttime
> >                                   else t1.datetimestamp
> >                                 end,
> >                                 case
> >                                   when t2.datetimestamp > s2.adjendtime
> then
> > s2.adjendtime
> >                                   else t2.datetimestamp
> >                                 end))
> >        end,
> >        case t1.eventnum
> >          when 1105 then
> >            sum(datediff(second, case
> >                                   when t1.datetimestamp <
s2.adjstarttime
> > then s2.adjstarttime
> >                                   else t1.datetimestamp
> >                                 end,
> >                                 case
> >                                   when t2.datetimestamp > s2.adjendtime
> then
> > s2.adjendtime
> >                                   else t2.datetimestamp
> >                                 end))
> >        end,
> >        case t1.eventnum
> >          when 1107 then
> >            sum(datediff(second, case
> >                                   when t1.datetimestamp <
s2.adjstarttime
> > then s2.adjstarttime
> >                                   else t1.datetimestamp
> >                                 end,
> >                                 case
> >                                   when t2.datetimestamp > s2.adjendtime
> then
> > s2.adjendtime
> >                                   else t2.datetimestamp
> >                                 end))
> >        end
> >   from #events t1
> >        inner join #events t2 on t1.intdata2 = t2.eventkey
> >          inner join #sitetimes s2 on s2.sitekey = t1.sitekey
> >  where t1.eventnum in (1101, 1103, 1105, 1107) and
> >        (t1.datetimestamp between s2.adjstarttime and s2.adjendtime or
> >         t2.datetimestamp between s2.adjstarttime and s2.adjendtime or
> >         (t1.datetimestamp < s2.adjstarttime and
> >          t2.datetimestamp > s2.adjendtime))
> > group by t1.sitekey, t1.driverkey, t1.eventnum
> >
> >
>
>

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:ekKGNQniGHA.1324@TK2MSFTNGP04.phx.gbl...
> Are you really using static values for your variables in this stored
> procedure?  Why are you using dynamic SQL with EXEC?  Won't static SQL
> work
> here?
>
> For starters, does this code return the same results?
>
> ALTER PROC [DBO].[UP_RS_HOURS_OF_SERVICE]
> AS
> SET NOCOUNT  ON
> SET TRANSACTION  ISOLATION  LEVEL  READ  UNCOMMITTED
>
> DECLARE
>  @begdate      VARCHAR(19),
>  @enddate      VARCHAR(19),
>  @driverkey    INT,
>  @begdriverid  VARCHAR(16),
>  @enddriverid  VARCHAR(16),
>  @sitekey      VARCHAR(4),
>  @filterclause VARCHAR(1250)
>
> set @begdate = '01/01/2005'
> set @enddate = DATEADD(SECOND,1,'12/31/2005')
> set @begdriverid = 1
> set @enddriverid = 999
> set @sitekey = 1
>
> CREATE TABLE #SITETIMES (
>  SITEKEY      INTEGER,
>  ADJSTARTTIME DATETIME,
>  ADJENDTIME   DATETIME)
>
> EXEC UP_RS_SITE_TIMES  @sitekey , @begdate , @enddate
>
> SELECT @begdate = CONVERT(VARCHAR(19),MIN(ADJSTARTTIME) - 7,120),
>  @enddate = CONVERT(VARCHAR(19),MAX(ADJENDTIME) + 7,120)
> FROM   #SITETIMES
> CREATE TABLE #EVENTS (
>  EVENTKEY      INT NOT NULL,
>  DATETIMESTAMP DATETIME NOT NULL,
>  EVENTNUM      INT NOT NULL,
>  SITEKEY       INT NOT NULL,
>  DRIVERKEY     INT NOT NULL,
>  INTDATA2      INT NULL)
> CREATE TABLE #HOURSOFSERVICE (
>  SITEKEY   INT,
>  DRIVERKEY INT,
>  ONDUTY    INT,
>  OFFDUTY   INT,
>  SLEEPER   INT,
>  DRIVE     INT)
>
> insert into #events
> select t1.eventkey,
> t1.datetimestamp,
> t1.eventnum,
> t1.sitekey,
> t1.driverkey,
> t1.intdata2
> from t_eventslog t1
> inner join s_drivers s1
> on t1.driverkey = s1.driverkey
> and s1.driverkey <> 0
> inner join #sitetimes s2
> on s2.sitekey = t1.sitekey
> where t1.datetimestamp between @begdate and @enddate
> and s1.driverid between @begdriverid and @enddriverid
> and t1.eventnum between 1101 and 1108
>
> CREATE INDEX [INTDATA2_INDEX] ON #EVENTS (
> [INTDATA2]) ON [PRIMARY]
>
> INSERT INTO #EVENTS
> SELECT T1.EVENTKEY,
> T1.DATETIMESTAMP,
> T1.EVENTNUM,
> T1.SITEKEY,
> T1.DRIVERKEY,
> T1.INTDATA2
> FROM T_EVENTSLOG T1
> INNER JOIN #EVENTS E1
> ON E1.INTDATA2 = T1.EVENTKEY
> INNER JOIN #SITETIMES S2
> ON S2.SITEKEY = T1.SITEKEY
> WHERE  T1.DATETIMESTAMP NOT BETWEEN @begdate AND @enddate
> AND T1.EVENTNUM BETWEEN 1101 AND 1108
>
> CREATE INDEX [EVENTKEY_INDEX] ON #EVENTS (
>  [EVENTKEY]) ON [PRIMARY]
>
> CREATE INDEX [EVENTNUM_INDEX] ON #EVENTS (
>  [EVENTNUM]) ON [PRIMARY]
>
> INSERT INTO #HOURSOFSERVICE
> SELECT   T1.SITEKEY,
> T1.DRIVERKEY,
> CASE T1.EVENTNUM
> WHEN 1101 THEN SUM(DATEDIFF(SECOND,CASE
> WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
> ELSE T1.DATETIMESTAMP
> END,CASE
> WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
> ELSE T2.DATETIMESTAMP
> END))
> END,
> CASE T1.EVENTNUM
> WHEN 1103 THEN SUM(DATEDIFF(SECOND,CASE
> WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
> ELSE T1.DATETIMESTAMP
> END,CASE
> WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
> ELSE T2.DATETIMESTAMP
> END))
> END,
> CASE T1.EVENTNUM
> WHEN 1105 THEN SUM(DATEDIFF(SECOND,CASE
> WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
> ELSE T1.DATETIMESTAMP
> END,CASE
> WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
> ELSE T2.DATETIMESTAMP
> END))
> END,
> CASE T1.EVENTNUM
> WHEN 1107 THEN SUM(DATEDIFF(SECOND,CASE
> WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
> ELSE T1.DATETIMESTAMP
> END,CASE
> WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
> ELSE T2.DATETIMESTAMP
> END))
> END
> FROM #EVENTS T1
> INNER JOIN #EVENTS T2
> ON T1.INTDATA2 = T2.EVENTKEY
> INNER JOIN #SITETIMES S2
> ON S2.SITEKEY = T1.SITEKEY
> WHERE    T1.EVENTNUM IN (1101, 1103, 1105, 1107)
> AND (T1.DATETIMESTAMP BETWEEN S2.ADJSTARTTIME AND S2.ADJENDTIME
>     OR T2.DATETIMESTAMP BETWEEN S2.ADJSTARTTIME AND S2.ADJENDTIME
>     OR (T1.DATETIMESTAMP < S2.ADJSTARTTIME
>         AND T2.DATETIMESTAMP > S2.ADJENDTIME))
> GROUP BY T1.SITEKEY,
> T1.DRIVERKEY,
> T1.EVENTNUM
>
>
>
> "Bill Nguyen" <billn_nospam_please@jaco.com> wrote in message
> news:%23HW56MciGHA.2208@TK2MSFTNGP05.phx.gbl...
>> I need help to turn this SP into a view or a table that contains
>> resulting
>> records.
>> Any help is greatly appreciated.
>>
>> Bill
>>
>>
>>
>> set ANSI_NULLS OFF
>> set QUOTED_IDENTIFIER OFF
>> GO
>> ALTER proc [dbo].[up_rs_hours_of_service]
>> as
>> set nocount on
>> set transaction isolation level read uncommitted
>> declare
>>   @begdate      varchar(19),
>>   @enddate      varchar(19),
>>   @driverkey    int,
>>   @begdriverid  varchar(16),
>>   @enddriverid  varchar(16),
>>   @sitekey      varchar(4),
>>   @filterclause varchar(1250)
>> select @begdate  = '01/01/2005'
>> select @enddate    = dateadd(second, 1, '12/31/2005')
>> select @begdriverid = 1
>> select @enddriverid =999
>> select @sitekey = 1
>>
>> create table #sitetimes (
>>   sitekey      integer,
>>   adjstarttime datetime,
>>   adjendtime   datetime
>> )
>> exec up_rs_site_times @sitekey, @begdate, @enddate
>> select @begdate = convert(varchar(19), min(adjstarttime) - 7, 120),
>>        @enddate = convert(varchar(19), max(adjendtime) + 7, 120)
>>   from #sitetimes
>> create table #events (
>>   eventkey int not null ,
>>   datetimestamp datetime not null,
>>   eventnum int not null ,
>>   sitekey int not null ,
>>   driverkey int not null ,
>>   intdata2 int null
>> )
>> create table #hoursofservice (
>>   sitekey    int,
>>   driverkey  int,
>>   onduty     int,
>>   offduty    int,
>>   sleeper    int,
>>   drive      int
>> )
>> exec( 'insert into #events '+
>>       'select t1.eventkey, '+
>>       '       t1.datetimestamp, '+
>>       '       t1.eventnum, '+
>>       '       t1.sitekey, '+
>>       '       t1.driverkey, '+
>>       '       t1.intdata2 '+
>>       '  from t_eventslog t1 '+
>>       '       inner join s_drivers s1 on t1.driverkey = s1.driverkey and
>> s1.driverkey <> 0 '+
>>       '       inner join #sitetimes s2 on s2.sitekey = t1.sitekey '+
>>       ' where t1.datetimestamp between ''' + @begdate + ''' and ''' +
>> @enddate + ''' and '+
>>       '       s1.driverid between ''' + @begdriverid + ''' and ''' +
>> @enddriverid + ''' and '+
>>       '       t1.eventnum between 1101 and 1108')
>> create index [intdata2_index] on #events([intdata2]) on [primary]
>> insert into #events
>> select t1.eventkey,
>>        t1.datetimestamp,
>>        t1.eventnum,
>>        t1.sitekey,
>>        t1.driverkey,
>>        t1.intdata2
>>   from t_eventslog t1
>>        inner join #events e1 on e1.intdata2 = t1.eventkey
>>        inner join #sitetimes s2 on s2.sitekey = t1.sitekey
>>  where t1.datetimestamp not between @begdate and @enddate and
>>        t1.eventnum between 1101 and 1108
>> create index [eventkey_index] on #events([eventkey]) on [primary]
>> create index [eventnum_index] on #events([eventnum]) on [primary]
>> insert into #hoursofservice
>> select t1.sitekey, t1.driverkey,
>>        case t1.eventnum
>>          when 1101 then
>>            sum(datediff(second, case
>>                                   when t1.datetimestamp < s2.adjstarttime
>> then s2.adjstarttime
>>                                   else t1.datetimestamp
>>                                 end,
>>                                 case
>>                                   when t2.datetimestamp > s2.adjendtime
> then
>> s2.adjendtime
>>                                   else t2.datetimestamp
>>                                 end))
>>        end,
>>        case t1.eventnum
>>          when 1103 then
>>            sum(datediff(second, case
>>                                   when t1.datetimestamp < s2.adjstarttime
>> then s2.adjstarttime
>>                                   else t1.datetimestamp
>>                                 end,
>>                                 case
>>                                   when t2.datetimestamp > s2.adjendtime
> then
>> s2.adjendtime
>>                                   else t2.datetimestamp
>>                                 end))
>>        end,
>>        case t1.eventnum
>>          when 1105 then
>>            sum(datediff(second, case
>>                                   when t1.datetimestamp < s2.adjstarttime
>> then s2.adjstarttime
>>                                   else t1.datetimestamp
>>                                 end,
>>                                 case
>>                                   when t2.datetimestamp > s2.adjendtime
> then
>> s2.adjendtime
>>                                   else t2.datetimestamp
>>                                 end))
>>        end,
>>        case t1.eventnum
>>          when 1107 then
>>            sum(datediff(second, case
>>                                   when t1.datetimestamp < s2.adjstarttime
>> then s2.adjstarttime
>>                                   else t1.datetimestamp
>>                                 end,
>>                                 case
>>                                   when t2.datetimestamp > s2.adjendtime
> then
>> s2.adjendtime
>>                                   else t2.datetimestamp
>>                                 end))
>>        end
>>   from #events t1
>>        inner join #events t2 on t1.intdata2 = t2.eventkey
>>          inner join #sitetimes s2 on s2.sitekey = t1.sitekey
>>  where t1.eventnum in (1101, 1103, 1105, 1107) and
>>        (t1.datetimestamp between s2.adjstarttime and s2.adjendtime or
>>         t2.datetimestamp between s2.adjstarttime and s2.adjendtime or
>>         (t1.datetimestamp < s2.adjstarttime and
>>          t2.datetimestamp > s2.adjendtime))
>> group by t1.sitekey, t1.driverkey, t1.eventnum
>>
>>
>
>

"Bill Nguyen" <billn_nospam_please@jaco.com> wrote in message
news:uSx9NQpiGHA.1208@TK2MSFTNGP02.phx.gbl...
> Jim;
>
> No, I just tried to make it simple. This is an SP from our software
vendor's
> app, I just want to use it without having to modify it.
>
> I'll see if you can dump the results into a table first. Next thing, I'll
> run the SP from a .NET 2.0 app instead.
> Regarding UP_RS_SITE_TIMES  , it is used to calculate the time intervals
or
> something.
>
> Thanks
>
> Bill
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:ekKGNQniGHA.1324@TK2MSFTNGP04.phx.gbl...
> > Are you really using static values for your variables in this stored
> > procedure?  Why are you using dynamic SQL with EXEC?  Won't static SQL
> > work
> > here?
> >
> > For starters, does this code return the same results?
> >
> > ALTER PROC [DBO].[UP_RS_HOURS_OF_SERVICE]
> > AS
> > SET NOCOUNT  ON
> > SET TRANSACTION  ISOLATION  LEVEL  READ  UNCOMMITTED
> >
> > DECLARE
> >  @begdate      VARCHAR(19),
> >  @enddate      VARCHAR(19),
> >  @driverkey    INT,
> >  @begdriverid  VARCHAR(16),
> >  @enddriverid  VARCHAR(16),
> >  @sitekey      VARCHAR(4),
> >  @filterclause VARCHAR(1250)
> >
> > set @begdate = '01/01/2005'
> > set @enddate = DATEADD(SECOND,1,'12/31/2005')
> > set @begdriverid = 1
> > set @enddriverid = 999
> > set @sitekey = 1
> >
> > CREATE TABLE #SITETIMES (
> >  SITEKEY      INTEGER,
> >  ADJSTARTTIME DATETIME,
> >  ADJENDTIME   DATETIME)
> >
> > EXEC UP_RS_SITE_TIMES  @sitekey , @begdate , @enddate
> >
> > SELECT @begdate = CONVERT(VARCHAR(19),MIN(ADJSTARTTIME) - 7,120),
> >  @enddate = CONVERT(VARCHAR(19),MAX(ADJENDTIME) + 7,120)
> > FROM   #SITETIMES
> > CREATE TABLE #EVENTS (
> >  EVENTKEY      INT NOT NULL,
> >  DATETIMESTAMP DATETIME NOT NULL,
> >  EVENTNUM      INT NOT NULL,
> >  SITEKEY       INT NOT NULL,
> >  DRIVERKEY     INT NOT NULL,
> >  INTDATA2      INT NULL)
> > CREATE TABLE #HOURSOFSERVICE (
> >  SITEKEY   INT,
> >  DRIVERKEY INT,
> >  ONDUTY    INT,
> >  OFFDUTY   INT,
> >  SLEEPER   INT,
> >  DRIVE     INT)
> >
> > insert into #events
> > select t1.eventkey,
> > t1.datetimestamp,
> > t1.eventnum,
> > t1.sitekey,
> > t1.driverkey,
> > t1.intdata2
> > from t_eventslog t1
> > inner join s_drivers s1
> > on t1.driverkey = s1.driverkey
> > and s1.driverkey <> 0
> > inner join #sitetimes s2
> > on s2.sitekey = t1.sitekey
> > where t1.datetimestamp between @begdate and @enddate
> > and s1.driverid between @begdriverid and @enddriverid
> > and t1.eventnum between 1101 and 1108
> >
> > CREATE INDEX [INTDATA2_INDEX] ON #EVENTS (
> > [INTDATA2]) ON [PRIMARY]
> >
> > INSERT INTO #EVENTS
> > SELECT T1.EVENTKEY,
> > T1.DATETIMESTAMP,
> > T1.EVENTNUM,
> > T1.SITEKEY,
> > T1.DRIVERKEY,
> > T1.INTDATA2
> > FROM T_EVENTSLOG T1
> > INNER JOIN #EVENTS E1
> > ON E1.INTDATA2 = T1.EVENTKEY
> > INNER JOIN #SITETIMES S2
> > ON S2.SITEKEY = T1.SITEKEY
> > WHERE  T1.DATETIMESTAMP NOT BETWEEN @begdate AND @enddate
> > AND T1.EVENTNUM BETWEEN 1101 AND 1108
> >
> > CREATE INDEX [EVENTKEY_INDEX] ON #EVENTS (
> >  [EVENTKEY]) ON [PRIMARY]
> >
> > CREATE INDEX [EVENTNUM_INDEX] ON #EVENTS (
> >  [EVENTNUM]) ON [PRIMARY]
> >
> > INSERT INTO #HOURSOFSERVICE
> > SELECT   T1.SITEKEY,
> > T1.DRIVERKEY,
> > CASE T1.EVENTNUM
> > WHEN 1101 THEN SUM(DATEDIFF(SECOND,CASE
> > WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
> > ELSE T1.DATETIMESTAMP
> > END,CASE
> > WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
> > ELSE T2.DATETIMESTAMP
> > END))
> > END,
> > CASE T1.EVENTNUM
> > WHEN 1103 THEN SUM(DATEDIFF(SECOND,CASE
> > WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
> > ELSE T1.DATETIMESTAMP
> > END,CASE
> > WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
> > ELSE T2.DATETIMESTAMP
> > END))
> > END,
> > CASE T1.EVENTNUM
> > WHEN 1105 THEN SUM(DATEDIFF(SECOND,CASE
> > WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
> > ELSE T1.DATETIMESTAMP
> > END,CASE
> > WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
> > ELSE T2.DATETIMESTAMP
> > END))
> > END,
> > CASE T1.EVENTNUM
> > WHEN 1107 THEN SUM(DATEDIFF(SECOND,CASE
> > WHEN T1.DATETIMESTAMP < S2.ADJSTARTTIME THEN S2.ADJSTARTTIME
> > ELSE T1.DATETIMESTAMP
> > END,CASE
> > WHEN T2.DATETIMESTAMP > S2.ADJENDTIME THEN S2.ADJENDTIME
> > ELSE T2.DATETIMESTAMP
> > END))
> > END
> > FROM #EVENTS T1
> > INNER JOIN #EVENTS T2
> > ON T1.INTDATA2 = T2.EVENTKEY
> > INNER JOIN #SITETIMES S2
> > ON S2.SITEKEY = T1.SITEKEY
> > WHERE    T1.EVENTNUM IN (1101, 1103, 1105, 1107)
> > AND (T1.DATETIMESTAMP BETWEEN S2.ADJSTARTTIME AND S2.ADJENDTIME
> >     OR T2.DATETIMESTAMP BETWEEN S2.ADJSTARTTIME AND S2.ADJENDTIME
> >     OR (T1.DATETIMESTAMP < S2.ADJSTARTTIME
> >         AND T2.DATETIMESTAMP > S2.ADJENDTIME))
> > GROUP BY T1.SITEKEY,
> > T1.DRIVERKEY,
> > T1.EVENTNUM
> >
> >
> >
> > "Bill Nguyen" <billn_nospam_please@jaco.com> wrote in message
> > news:%23HW56MciGHA.2208@TK2MSFTNGP05.phx.gbl...
> >> I need help to turn this SP into a view or a table that contains
> >> resulting
> >> records.
> >> Any help is greatly appreciated.
> >>
> >> Bill
> >>
> >>
> >>
> >> set ANSI_NULLS OFF
> >> set QUOTED_IDENTIFIER OFF
> >> GO
> >> ALTER proc [dbo].[up_rs_hours_of_service]
> >> as
> >> set nocount on
> >> set transaction isolation level read uncommitted
> >> declare
> >>   @begdate      varchar(19),
> >>   @enddate      varchar(19),
> >>   @driverkey    int,
> >>   @begdriverid  varchar(16),
> >>   @enddriverid  varchar(16),
> >>   @sitekey      varchar(4),
> >>   @filterclause varchar(1250)
> >> select @begdate  = '01/01/2005'
> >> select @enddate    = dateadd(second, 1, '12/31/2005')
> >> select @begdriverid = 1
> >> select @enddriverid =999
> >> select @sitekey = 1
> >>
> >> create table #sitetimes (
> >>   sitekey      integer,
> >>   adjstarttime datetime,
> >>   adjendtime   datetime
> >> )
> >> exec up_rs_site_times @sitekey, @begdate, @enddate
> >> select @begdate = convert(varchar(19), min(adjstarttime) - 7, 120),
> >>        @enddate = convert(varchar(19), max(adjendtime) + 7, 120)
> >>   from #sitetimes
> >> create table #events (
> >>   eventkey int not null ,
> >>   datetimestamp datetime not null,
> >>   eventnum int not null ,
> >>   sitekey int not null ,
> >>   driverkey int not null ,
> >>   intdata2 int null
> >> )
> >> create table #hoursofservice (
> >>   sitekey    int,
> >>   driverkey  int,
> >>   onduty     int,
> >>   offduty    int,
> >>   sleeper    int,
> >>   drive      int
> >> )
> >> exec( 'insert into #events '+
> >>       'select t1.eventkey, '+
> >>       '       t1.datetimestamp, '+
> >>       '       t1.eventnum, '+
> >>       '       t1.sitekey, '+
> >>       '       t1.driverkey, '+
> >>       '       t1.intdata2 '+
> >>       '  from t_eventslog t1 '+
> >>       '       inner join s_drivers s1 on t1.driverkey = s1.driverkey
and
> >> s1.driverkey <> 0 '+
> >>       '       inner join #sitetimes s2 on s2.sitekey = t1.sitekey '+
> >>       ' where t1.datetimestamp between ''' + @begdate + ''' and ''' +
> >> @enddate + ''' and '+
> >>       '       s1.driverid between ''' + @begdriverid + ''' and ''' +
> >> @enddriverid + ''' and '+
> >>       '       t1.eventnum between 1101 and 1108')
> >> create index [intdata2_index] on #events([intdata2]) on [primary]
> >> insert into #events
> >> select t1.eventkey,
> >>        t1.datetimestamp,
> >>        t1.eventnum,
> >>        t1.sitekey,
> >>        t1.driverkey,
> >>        t1.intdata2
> >>   from t_eventslog t1
> >>        inner join #events e1 on e1.intdata2 = t1.eventkey
> >>        inner join #sitetimes s2 on s2.sitekey = t1.sitekey
> >>  where t1.datetimestamp not between @begdate and @enddate and
> >>        t1.eventnum between 1101 and 1108
> >> create index [eventkey_index] on #events([eventkey]) on [primary]
> >> create index [eventnum_index] on #events([eventnum]) on [primary]
> >> insert into #hoursofservice
> >> select t1.sitekey, t1.driverkey,
> >>        case t1.eventnum
> >>          when 1101 then
> >>            sum(datediff(second, case
> >>                                   when t1.datetimestamp <
s2.adjstarttime
> >> then s2.adjstarttime
> >>                                   else t1.datetimestamp
> >>                                 end,
> >>                                 case
> >>                                   when t2.datetimestamp > s2.adjendtime
> > then
> >> s2.adjendtime
> >>                                   else t2.datetimestamp
> >>                                 end))
> >>        end,
> >>        case t1.eventnum
> >>          when 1103 then
> >>            sum(datediff(second, case
> >>                                   when t1.datetimestamp <
s2.adjstarttime
> >> then s2.adjstarttime
> >>                                   else t1.datetimestamp
> >>                                 end,
> >>                                 case
> >>                                   when t2.datetimestamp > s2.adjendtime
> > then
> >> s2.adjendtime
> >>                                   else t2.datetimestamp
> >>                                 end))
> >>        end,
> >>        case t1.eventnum
> >>          when 1105 then
> >>            sum(datediff(second, case
> >>                                   when t1.datetimestamp <
s2.adjstarttime
> >> then s2.adjstarttime
> >>                                   else t1.datetimestamp
> >>                                 end,
> >>                                 case
> >>                                   when t2.datetimestamp > s2.adjendtime
> > then
> >> s2.adjendtime
> >>                                   else t2.datetimestamp
> >>                                 end))
> >>        end,
> >>        case t1.eventnum
> >>          when 1107 then
> >>            sum(datediff(second, case
> >>                                   when t1.datetimestamp <
s2.adjstarttime
> >> then s2.adjstarttime
> >>                                   else t1.datetimestamp
> >>                                 end,
> >>                                 case
> >>                                   when t2.datetimestamp > s2.adjendtime
> > then
> >> s2.adjendtime
> >>                                   else t2.datetimestamp
> >>                                 end))
> >>        end
> >>   from #events t1
> >>        inner join #events t2 on t1.intdata2 = t2.eventkey
> >>          inner join #sitetimes s2 on s2.sitekey = t1.sitekey
> >>  where t1.eventnum in (1101, 1103, 1105, 1107) and
> >>        (t1.datetimestamp between s2.adjstarttime and s2.adjendtime or
> >>         t2.datetimestamp between s2.adjstarttime and s2.adjendtime or
> >>         (t1.datetimestamp < s2.adjstarttime and
> >>          t2.datetimestamp > s2.adjendtime))
> >> group by t1.sitekey, t1.driverkey, t1.eventnum
> >>
> >>
> >
> >
>
>