CREATE TABLE #TMPBUS (school_desc Char(60), school_trip_id INT)

CREATE TABLE #BUSPASS1(bp_stu_autoid int NULL,AM_StopSrv_Idx char(15) NULL,AM_stop_desc char(60) NULL,
  AM_RunSrv_TimeAtSrv char(8) NULL,AM_RunSrv_Run_Idx char(12) NULL,AM_Run_Desc char(60),AM_Rte_ID char(10) NULL, 
  AM_Rte_BusNumber char(10) NULL,AM_StopSrv_BellTime char(8) NULL,AM_school_desc char(60) NULL,
  AM_StuTrip_FreqDisplay char(7) NULL, AM_TripAsgn_DistToStop float NULL,AM_StopSrv_Stop_Idx char(12) NULL,
  AM_StopSrv_AsgnAvg int NULL,AM_StopSrv_HdcntAvg int NULL, 
  AM_StuTrip_Desc char(60) NULL,AM_Rte_Desc char(40) NULL,AM_Loc_Loc char(62) NULL)

CREATE TABLE #BUSPASS2(bp_stu_autoid int NULL,PM_StopSrv_Idx char(15) NULL,PM_stop_desc char(60) NULL, 
  PM_RunSrv_TimeAtSrv char(8) NULL,PM_RunSrv_Run_Idx char(12) NULL,PM_Run_Desc char(60),PM_Rte_ID char(10) NULL, 
  PM_Rte_BusNumber char(10) NULL,PM_StopSrv_BellTime char(8) NULL,PM_school_desc char(60) NULL,
  PM_StuTrip_FreqDisplay char(7) NULL, PM_TripAsgn_DistToStop float NULL,PM_StopSrv_Stop_Idx char(12) NULL,
  PM_StopSrv_AsgnAvg int NULL,PM_StopSrv_HdcntAvg int NULL, 
  PM_StuTrip_Desc char(60) NULL,PM_Rte_Desc char(40) NULL,PM_Loc_Loc char(62) NULL)

CREATE TABLE #buspassID (id int NULL)

CREATE TABLE #BUSPASS( bp_stu_autoid int NULL,AM_StopSrv_Idx char(15)  DEFAULT (' '),
  AM_stop_desc char(60)  DEFAULT (' '),AM_RunSrv_TimeAtSrv char(8)  DEFAULT (' '),
  AM_RunSrv_Run_Idx char(12)  DEFAULT (' '),AM_Run_Desc char(60)  DEFAULT (' '),
  AM_Rte_ID char(10)  DEFAULT (' '),AM_Rte_BusNumber char(10)  DEFAULT (' '),
  AM_StopSrv_BellTime char(8)  DEFAULT (' '),AM_school_desc char(60)  DEFAULT (' '),
  AM_StuTrip_FreqDisplay char(7)  DEFAULT (' '),AM_TripAsgn_DistToStop float NULL  DEFAULT ((0)),
  AM_StopSrv_Stop_Idx char(12)  DEFAULT (' '),AM_StopSrv_AsgnAvg int NULL  DEFAULT ((0)),
  AM_StopSrv_HdcntAvg int NULL  DEFAULT ((0)),AM_StuTrip_Desc char(60) DEFAULT (' '),
  AM_Rte_Desc char(40)  DEFAULT (' '),AM_Loc_Loc char(62)  DEFAULT (' '),
  AM1_StopSrv_Idx char(15)  DEFAULT (' '),AM1_stop_desc char(60)  DEFAULT (' '),
  AM1_RunSrv_TimeAtSrv char(8)  DEFAULT (' '),AM1_RunSrv_Run_Idx char(12)  DEFAULT (' '),
  AM1_Run_Desc char(60)  DEFAULT (' '),AM1_Rte_ID char(10)  DEFAULT (' '),
  AM1_Rte_BusNumber char(10)  DEFAULT (' '),AM1_StopSrv_BellTime char(8)  DEFAULT (' '),
  AM1_school_desc char(60)  DEFAULT (' '),AM1_StuTrip_FreqDisplay char(7)  DEFAULT (' '),
  AM1_TripAsgn_DistToStop float NULL  DEFAULT ((0)),AM1_StopSrv_Stop_Idx char(12)  DEFAULT (' '),
  AM1_StopSrv_AsgnAvg int NULL  DEFAULT ((0)),AM1_StopSrv_HdcntAvg int NULL  DEFAULT ((0)),
  AM1_StuTrip_Desc char(60)  DEFAULT (' '),AM1_Rte_Desc char(40)  DEFAULT (' '),
  AM1_Loc_Loc char(62)  DEFAULT (' '),AM2_StopSrv_Idx char(15)  DEFAULT (' '),
  AM2_stop_desc char(60) DEFAULT (' '),AM2_RunSrv_TimeAtSrv char(8) DEFAULT (' '),
  AM2_RunSrv_Run_Idx char(12)  DEFAULT (' '),AM2_Run_Desc char(60)  DEFAULT (' '),
  AM2_Rte_ID char(10) DEFAULT (' '),AM2_Rte_BusNumber char(10) DEFAULT (' '),
  AM2_StopSrv_BellTime char(8)  DEFAULT (' '),AM2_school_desc char(60) DEFAULT (' '),
  AM2_StuTrip_FreqDisplay char(7) DEFAULT (' '),AM2_TripAsgn_DistToStop float NULL DEFAULT ((0)),
  AM2_StopSrv_Stop_Idx char(12) DEFAULT (' '),AM2_StopSrv_AsgnAvg int NULL  DEFAULT ((0)),
  AM2_StopSrv_HdcntAvg int NULL  DEFAULT ((0)),AM2_StuTrip_Desc char(60) DEFAULT (' '),
  AM2_Rte_Desc char(40) DEFAULT (' '),AM2_Loc_Loc char(62)  DEFAULT (' '),
  AM3_StopSrv_Idx char(15) DEFAULT (' '),AM3_stop_desc char(60) DEFAULT (' '),
  AM3_RunSrv_TimeAtSrv char(8) DEFAULT (' '),AM3_RunSrv_Run_Idx char(12) DEFAULT (' '),
  AM3_Run_Desc char(60)  DEFAULT (' '),AM3_Rte_ID char(10)  DEFAULT (' '),
  AM3_Rte_BusNumber char(10)  DEFAULT (' '),AM3_StopSrv_BellTime char(8) DEFAULT (' '),
  AM3_school_desc char(60)  DEFAULT (' '),AM3_StuTrip_FreqDisplay char(7) DEFAULT (' '),
  AM3_TripAsgn_DistToStop float NULL  DEFAULT ((0)),AM3_StopSrv_Stop_Idx char(12)  DEFAULT (' '),
  AM3_StopSrv_AsgnAvg int NULL  DEFAULT ((0)),AM3_StopSrv_HdcntAvg int NULL  DEFAULT ((0)),
  AM3_StuTrip_Desc char(60) DEFAULT (' '),AM3_Rte_Desc char(40)  DEFAULT (' '),
  AM3_Loc_Loc char(62) DEFAULT (' '),AM4_StopSrv_Idx char(15)  DEFAULT (' '),
  AM4_stop_desc char(60)  DEFAULT (' '),AM4_RunSrv_TimeAtSrv char(8)  DEFAULT (' '),
  AM4_RunSrv_Run_Idx char(12)  DEFAULT (' '),AM4_Run_Desc char(60)  DEFAULT (' '),
  AM4_Rte_ID char(10)  DEFAULT (' '),AM4_Rte_BusNumber char(10)  DEFAULT (' '),
  AM4_StopSrv_BellTime char(8)  DEFAULT (' '),AM4_school_desc char(60)  DEFAULT (' '),
  AM4_StuTrip_FreqDisplay char(7)  DEFAULT (' '),AM4_TripAsgn_DistToStop float NULL  DEFAULT ((0)),
  AM4_StopSrv_Stop_Idx char(12)  DEFAULT (' '),AM4_StopSrv_AsgnAvg int NULL  DEFAULT ((0)),
  AM4_StopSrv_HdcntAvg int NULL  DEFAULT ((0)),AM4_StuTrip_Desc char(60)  DEFAULT (' '),
  AM4_Rte_Desc char(40)  DEFAULT (' '),AM4_Loc_Loc char(62)  DEFAULT (' '),
  AM5_StopSrv_Idx char(15)  DEFAULT (' '),AM5_stop_desc char(60)  DEFAULT (' '),
  AM5_RunSrv_TimeAtSrv char(8)  DEFAULT (' '),AM5_RunSrv_Run_Idx char(12)  DEFAULT (' '),
  AM5_Run_Desc char(60)  DEFAULT (' '),AM5_Rte_ID char(10)  DEFAULT (' '),
  AM5_Rte_BusNumber char(10)  DEFAULT (' '),AM5_StopSrv_BellTime char(8) DEFAULT (' '),
  AM5_school_desc char(60)  DEFAULT (' '),AM5_StuTrip_FreqDisplay char(7)  DEFAULT (' '),
  AM5_TripAsgn_DistToStop float NULL DEFAULT ((0)),AM5_StopSrv_Stop_Idx char(12) DEFAULT (' '),
  AM5_StopSrv_AsgnAvg int NULL DEFAULT ((0)),AM5_StopSrv_HdcntAvg int NULL DEFAULT ((0)),
  AM5_StuTrip_Desc char(60) DEFAULT (' '),AM5_Rte_Desc char(40) DEFAULT (' '),
  AM5_Loc_Loc char(62) DEFAULT (' '),PM_StopSrv_Idx char(15) DEFAULT (' '),
  PM_stop_desc char(60) DEFAULT (' '),PM_RunSrv_TimeAtSrv char(8) DEFAULT (' '),
  PM_RunSrv_Run_Idx char(12) DEFAULT (' '),PM_Run_Desc char(60) DEFAULT (' '),
  PM_Rte_ID char(10) DEFAULT (' '),PM_Rte_BusNumber char(10) DEFAULT (' '),
  PM_StopSrv_BellTime char(8) DEFAULT (' '),PM_school_desc char(60) DEFAULT (' '),
  PM_StuTrip_FreqDisplay char(7) DEFAULT (' '),PM_TripAsgn_DistToStop float NULL DEFAULT ((0)),
  PM_StopSrv_Stop_Idx char(12) DEFAULT (' '),PM_StopSrv_AsgnAvg int NULL DEFAULT ((0)),
  PM_StopSrv_HdcntAvg int NULL DEFAULT ((0)),PM_StuTrip_Desc char(60) DEFAULT (' '),
  PM_Rte_Desc char(40) DEFAULT (' '),PM_Loc_Loc char(62) DEFAULT (' '),
  PM1_StopSrv_Idx char(15) DEFAULT (' '),PM1_stop_desc char(60) DEFAULT (' '),
  PM1_RunSrv_TimeAtSrv char(8) DEFAULT (' '),PM1_RunSrv_Run_Idx char(12) DEFAULT (' '),
  PM1_Run_Desc char(60) DEFAULT (' '),PM1_Rte_ID char(10) DEFAULT (' '),
  PM1_Rte_BusNumber char(10) DEFAULT (' '),PM1_StopSrv_BellTime char(8) DEFAULT (' '),
  PM1_school_desc char(60) DEFAULT (' '),PM1_StuTrip_FreqDisplay char(7) DEFAULT (' '),
  PM1_TripAsgn_DistToStop float NULL DEFAULT ((0)),PM1_StopSrv_Stop_Idx char(12) DEFAULT (' '),
  PM1_StopSrv_AsgnAvg int NULL DEFAULT ((0)),PM1_StopSrv_HdcntAvg int NULL DEFAULT ((0)),
  PM1_StuTrip_Desc char(60) DEFAULT (' '),PM1_Rte_Desc char(40) DEFAULT (' '),
  PM1_Loc_Loc char(62) DEFAULT (' '),PM2_StopSrv_Idx char(15) DEFAULT (' '),
  PM2_stop_desc char(60) DEFAULT (' '),PM2_RunSrv_TimeAtSrv char(8) DEFAULT (' '),
  PM2_RunSrv_Run_Idx char(12) DEFAULT (' '),PM2_Run_Desc char(60) DEFAULT (' '),
  PM2_Rte_ID char(10) DEFAULT (' '),PM2_Rte_BusNumber char(10) DEFAULT (' '),
  PM2_StopSrv_BellTime char(8) DEFAULT (' '),PM2_school_desc char(60) DEFAULT (' '),
  PM2_StuTrip_FreqDisplay char(7) DEFAULT (' '),PM2_TripAsgn_DistToStop float NULL DEFAULT ((0)),
  PM2_StopSrv_Stop_Idx char(12) DEFAULT (' '),PM2_StopSrv_AsgnAvg int NULL DEFAULT ((0)),
  PM2_StopSrv_HdcntAvg int NULL DEFAULT ((0)),PM2_StuTrip_Desc char(60) DEFAULT (' '),
  PM2_Rte_Desc char(40) DEFAULT (' '),PM2_Loc_Loc char(62) DEFAULT (' '),
  PM3_StopSrv_Idx char(15) DEFAULT (' '),PM3_stop_desc char(60) DEFAULT (' '),
  PM3_RunSrv_TimeAtSrv char(8) DEFAULT (' '),PM3_RunSrv_Run_Idx char(12) DEFAULT (' '),
  PM3_Run_Desc char(60) DEFAULT (' '),PM3_Rte_ID char(10) DEFAULT (' '),
  PM3_Rte_BusNumber char(10) DEFAULT (' '),PM3_StopSrv_BellTime char(8) DEFAULT (' '),
  PM3_school_desc char(60) DEFAULT (' '),PM3_StuTrip_FreqDisplay char(7) DEFAULT (' '),
  PM3_TripAsgn_DistToStop float NULL DEFAULT ((0)),PM3_StopSrv_Stop_Idx char(12) DEFAULT (' '),
  PM3_StopSrv_AsgnAvg int NULL DEFAULT ((0)),PM3_StopSrv_HdcntAvg int NULL DEFAULT ((0)),
  PM3_StuTrip_Desc char(60) DEFAULT (' '),PM3_Rte_Desc char(40) DEFAULT (' '),
  PM3_Loc_Loc char(62) DEFAULT (' '),PM4_StopSrv_Idx char(15) DEFAULT (' '),
  PM4_stop_desc char(60) DEFAULT (' '),PM4_RunSrv_TimeAtSrv char(8) DEFAULT (' '),
  PM4_RunSrv_Run_Idx char(12) DEFAULT (' '),PM4_Run_Desc char(60) DEFAULT (' '),
  PM4_Rte_ID char(10) DEFAULT (' '),PM4_Rte_BusNumber char(10) DEFAULT (' '),
  PM4_StopSrv_BellTime char(8) DEFAULT (' '),PM4_school_desc char(60) DEFAULT (' '),
  PM4_StuTrip_FreqDisplay char(7) DEFAULT (' '),PM4_TripAsgn_DistToStop float NULL DEFAULT ((0)),
  PM4_StopSrv_Stop_Idx char(12) DEFAULT (' '),PM4_StopSrv_AsgnAvg int NULL DEFAULT ((0)),
  PM4_StopSrv_HdcntAvg int NULL DEFAULT ((0)),PM4_StuTrip_Desc char(60) DEFAULT (' '),
  PM4_Rte_Desc char(40) DEFAULT (' '),PM4_Loc_Loc char(62) DEFAULT (' '),
  PM5_StopSrv_Idx char(15) DEFAULT (' '),PM5_stop_desc char(60) DEFAULT (' '),
  PM5_RunSrv_TimeAtSrv char(8) DEFAULT (' '),PM5_RunSrv_Run_Idx char(12) DEFAULT (' '),
  PM5_Run_Desc char(60) DEFAULT (' '),PM5_Rte_ID char(10) DEFAULT (' '),
  PM5_Rte_BusNumber char(10) DEFAULT (' '),PM5_StopSrv_BellTime char(8) DEFAULT (' '),
  PM5_school_desc char(60) DEFAULT (' '),PM5_StuTrip_FreqDisplay char(7) DEFAULT (' '),
  PM5_TripAsgn_DistToStop float NULL DEFAULT ((0)),PM5_StopSrv_Stop_Idx char(12) DEFAULT (' '),
  PM5_StopSrv_AsgnAvg int NULL DEFAULT ((0)),PM5_StopSrv_HdcntAvg int NULL DEFAULT ((0)),
  PM5_StuTrip_Desc char(60) DEFAULT (' '),PM5_Rte_Desc char(40) DEFAULT (' '),
  PM5_Loc_Loc char(62) DEFAULT (' '))
go
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Elizabeth Walsh>
-- Create date: <29 August 2007>
-- Description:	<Procedure to fill temptable buspass>
-- =============================================
CREATE PROCEDURE bpsp_fillbuspass 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here
insert into #TMPBUS SELECT DISTINCT stop.stop_desc AS school_desc, 
  TRIPASSIGNMENT.TRIPASGN_STUTRIP_AUTOID AS school_trip_id
  FROM  TRIPASSIGNMENT INNER JOIN STOPSERVICE 
    ON STOPSERVICE.STOPSRV_AUTOID = TRIPASSIGNMENT.TRIPASGN_STOPSRV_AUTOID AND 
    TRIPASSIGNMENT.TRIPASGN_SCH = 'True' 
  INNER JOIN STOP ON STOP.STOP_AUTOID = STOPSERVICE.STOPSRV_STOP_AUTOID 
  WHERE TRIPASSIGNMENT.TRIPASGN_STUTRIP_AUTOID > 0  
  ORDER BY TRIPASSIGNMENT.TRIPASGN_STUTRIP_AUTOID

Insert into #BUSPASS1 SELECT DISTINCT studenttrip.stutrip_stu_autoid as bp_stu_autoid, 
  isnull(stopservice.StopSrv_Idx,' ') as AM_StopSrv_Idx,isnull(stop.stop_desc,' ') as AM_stop_desc, 
   CAST(case when ( DATEPART(hh,runservice.RunSrv_TimeAtSrv) - 10 < 0 ) or 
     ((DATEPART(hh,runservice.RunSrv_TimeAtSrv) > 12  
     and DATEPART(hh,runservice.RunSrv_TimeAtSrv)<22 )) then '0' else '' end  
     + ISNULL(REPLACE(REPLACE( LTRIM((SUBSTRING(convert(char(19) , runservice.RunSrv_TimeAtSrv ,100),13,19))),'AM',' AM'),'PM',' PM'),' ') 
   AS VARCHAR(8)) as AM_RunSrv_TimeAtSrv , 
  isnull(runservice.RunSrv_Run_Idx,' ') as AM_RunSrv_Run_Idx,isnull(run.Run_Desc,' ') as AM_Run_Desc, 
  isnull(route.Rte_ID,' ') as AM_Rte_ID,isnull(route.Rte_BusNumber,' ') as AM_Rte_BusNumber, 
  CAST(case when ( DATEPART(hh,StopSrv_BellTime ) - 10 < 0 ) or ((DATEPART(hh,StopSrv_BellTime ) > 12 
    and DATEPART(hh,StopSrv_BellTime )<22 )) then '0' else '' end  
    + ISNULL(REPLACE(REPLACE(LTRIM((SUBSTRING(convert(char(19) , StopSrv_BellTime ,100),13,19))),'AM',' AM'),'PM',' PM'),' ')  
   AS VARCHAR(8)) as AM_StopSrv_BellTime,#tmpbus.school_desc as AM_school_desc, 
   studenttrip.StuTrip_FreqDisplay as AM_StuTrip_FreqDisplay,
   isnull( round(tripassignment.TripAsgn_DistToStop,2), 0) as AM_TripAsgn_DistToStop , 
   isnull(stopservice.StopSrv_Stop_Idx, ' ') as AM_StopSrv_Stop_Idx , 
   isnull(stopservice.StopSrv_AsgnAvg, ' ') as AM_StopSrv_AsgnAvg, 
   isnull(stopservice.StopSrv_HdcntAvg,' ') as AM_StopSrv_HdcntAvg,
   isnull(StudentTrip.StuTrip_Desc,' ') as AM_StuTrip_Desc,
   isnull(route.Rte_Desc,' ') as AM_Rte_Desc,isnull(Location.Loc_Loc,' ') as AM_Loc_Loc
  FROM STUDENTTRIP INNER JOIN TRIPASSIGNMENT 
    ON STUDENTTRIP.STUTRIP_AUTOID = TRIPASSIGNMENT.TRIPASGN_STUTRIP_AUTOID 
   LEFT OUTER JOIN #tmpbus 
     ON STUDENTTRIP.STUTRIP_AUTOID = #tmpbus.school_trip_id 
   INNER JOIN STOPSERVICE 
     ON STOPSERVICE.STOPSRV_AUTOID = TRIPASSIGNMENT.TRIPASGN_STOPSRV_AUTOID AND  
     TRIPASSIGNMENT.TRIPASGN_SCH <> 'True'  
   LEFT OUTER JOIN STOP 
     ON STOP.STOP_AUTOID = STOPSERVICE.STOPSRV_STOP_AUTOID 
   LEFT OUTER JOIN LOCATION 
     ON STOP.STOP_LOC_AUTOID = LOCATION.LOC_AUTOID 
   LEFT OUTER JOIN RUNSERVICE 
     ON RUNSERVICE.RUNSRV_STOPSRV_AUTOID = STOPSERVICE.STOPSRV_AUTOID AND
      runservice.RunSrv_Dh = 'False' 
   LEFT OUTER JOIN RUNROUTE 
     ON RUNSERVICE.RUNSRV_RUNRTE_AUTOID = RUNROUTE.RUNRTE_AUTOID 
   LEFT OUTER JOIN RUN ON RUNROUTE.RUNRTE_RUN_AUTOID = RUN.RUN_AUTOID 
   LEFT OUTER JOIN ROUTE ON RUNROUTE.RUNRTE_RTE_AUTOID = ROUTE.RTE_AUTOID 
  WHERE studenttrip.stutrip_stu_autoid > 0 and tripassignment.TripAsgn_PickUp=1 
  ORDER BY studenttrip.stutrip_stu_autoid, AM_RunSrv_TimeAtSrv 

Insert into #BUSPASS2 SELECT DISTINCT studenttrip.stutrip_stu_autoid as bp_stu_autoid, 
  isnull(stopservice.StopSrv_Idx,' ') as PM_StopSrv_Idx,isnull(stop.stop_desc,' ') as PM_stop_desc, 
  CAST(case when ( DATEPART(hh,runservice.RunSrv_TimeAtSrv) - 10 < 0 ) or 
    ((DATEPART(hh,runservice.RunSrv_TimeAtSrv) > 12 and DATEPART(hh,runservice.RunSrv_TimeAtSrv)<22 )) then '0' else '' end  
    + ISNULL(REPLACE(REPLACE( LTRIM((SUBSTRING(convert(char(19) , runservice.RunSrv_TimeAtSrv ,100),13,19))),'AM',' AM'),'PM',' PM'),' ')  
   AS CHAR(8)) as PM_RunSrv_TimeAtSrv , 
  isnull(runservice.RunSrv_Run_Idx,' ') as PM_RunSrv_Run_Idx,isnull(run.Run_Desc,' ') as PM_Run_Desc, 
  isnull(route.Rte_ID,' ') as PM_Rte_ID,isnull(route.Rte_BusNumber,' ') as PM_Rte_BusNumber, 
  CAST(case when ( DATEPART(hh,StopSrv_BellTime ) - 10 < 0 ) or ((DATEPART(hh,StopSrv_BellTime ) > 12 and 
    DATEPART(hh,StopSrv_BellTime )<22 )) then '0' else '' end  
    + ISNULL(REPLACE(REPLACE( LTRIM((SUBSTRING(convert(char(19) , StopSrv_BellTime ,100),13,19))),'AM',' AM'),'PM',' PM'),' ') 
   AS VARCHAR(8)) as PM_StopSrv_BellTime,#tmpbus.school_desc as PM_school_desc, 
   studenttrip.StuTrip_FreqDisplay as PM_StuTrip_FreqDisplay,
   isnull(round(tripassignment.TripAsgn_DistToStop,2),0) as PM_TripAsgn_DistToStop , 
   isnull(stopservice.StopSrv_Stop_Idx, ' ') as PM_StopSrv_Stop_Idx , 
   isnull(stopservice.StopSrv_AsgnAvg, ' ') as PM_StopSrv_AsgnAvg, 
   isnull(stopservice.StopSrv_HdcntAvg,' ') as PM_StopSrv_HdcntAvg,
   isnull(StudentTrip.StuTrip_Desc,' ') as PM_StuTrip_Desc,
   isnull(route.Rte_Desc,' ') as PM_Rte_Desc,isnull(Location.Loc_Loc,' ') as PM_Loc_Loc  
  FROM STUDENTTRIP INNER JOIN TRIPASSIGNMENT 
    ON STUDENTTRIP.STUTRIP_AUTOID = TRIPASSIGNMENT.TRIPASGN_STUTRIP_AUTOID 
   LEFT OUTER JOIN #tmpbus 
    ON STUDENTTRIP.STUTRIP_AUTOID = #tmpbus.school_trip_id 
   INNER JOIN STOPSERVICE 
    ON STOPSERVICE.STOPSRV_AUTOID = TRIPASSIGNMENT.TRIPASGN_STOPSRV_AUTOID AND 
      TRIPASSIGNMENT.TRIPASGN_SCH <> 'True'  
   LEFT OUTER JOIN STOP 
    ON STOP.STOP_AUTOID = STOPSERVICE.STOPSRV_STOP_AUTOID 
   LEFT OUTER JOIN LOCATION 
    ON STOP.STOP_LOC_AUTOID = LOCATION.LOC_AUTOID 
   LEFT OUTER JOIN RUNSERVICE 
    ON RUNSERVICE.RUNSRV_STOPSRV_AUTOID = STOPSERVICE.STOPSRV_AUTOID AND 
      runservice.RunSrv_Dh = 'False' 
   LEFT OUTER JOIN RUNROUTE 
    ON RUNSERVICE.RUNSRV_RUNRTE_AUTOID = RUNROUTE.RUNRTE_AUTOID 
   LEFT OUTER JOIN RUN ON RUNROUTE.RUNRTE_RUN_AUTOID = RUN.RUN_AUTOID 
   LEFT OUTER JOIN ROUTE ON RUNROUTE.RUNRTE_RTE_AUTOID = ROUTE.RTE_AUTOID 
  WHERE studenttrip.stutrip_stu_autoid > 0 and tripassignment.TripAsgn_PickUp=0 
  ORDER BY studenttrip.stutrip_stu_autoid, PM_RunSrv_TimeAtSrv

insert into #BUSPASS(bp_stu_autoid,AM_StopSrv_Idx,AM_stop_desc,AM_RunSrv_TimeAtSrv,
   AM_RunSrv_Run_Idx,AM_Run_Desc,AM_Rte_ID,AM_Rte_BusNumber,AM_StopSrv_BellTime, 
   AM_school_desc, AM_StuTrip_FreqDisplay, AM_TripAsgn_DistToStop, 
   AM_StopSrv_Stop_Idx,AM_StopSrv_AsgnAvg, AM_StopSrv_HdcntAvg,AM_StuTrip_Desc, 
   AM_Rte_Desc,AM_Loc_Loc)  
  select * from #buspass1 
   where bp_stu_autoid not in(Select bp_stu_autoid from #buspass2 )

insert into #BUSPASS(bp_stu_autoid, PM_StopSrv_Idx, PM_stop_desc, 
   PM_RunSrv_TimeAtSrv, PM_RunSrv_Run_Idx, PM_Run_Desc,PM_Rte_ID,
   PM_Rte_BusNumber, PM_StopSrv_BellTime, PM_school_desc, PM_StuTrip_FreqDisplay, 
   PM_TripAsgn_DistToStop, PM_StopSrv_Stop_Idx, PM_StopSrv_AsgnAvg, 
   PM_StopSrv_HdcntAvg,PM_StuTrip_Desc,PM_Rte_Desc,PM_Loc_Loc)  
  select #buspass2.* from #buspass2
     LEFT JOIN #buspass1 ON #buspass2.bp_stu_autoid = #buspass1.bp_stu_autoid
 WHERE #buspass1.bp_stu_autoid IS NULL

insert into #buspassID select bp_stu_autoid from #buspass1 group by bp_stu_autoid having count(bp_stu_autoid)>1 
union select bp_stu_autoid from #buspass2 group by bp_stu_autoid having count(bp_stu_autoid)>1

delete from #buspass1 where bp_stu_autoid in (select id from #buspassID)

delete from #buspass2 where bp_stu_autoid in (select id from #buspassID)

insert into #BUSPASS(bp_stu_autoid,AM_StopSrv_Idx,AM_stop_desc,AM_RunSrv_TimeAtSrv,AM_RunSrv_Run_Idx,AM_Run_Desc,AM_Rte_ID, 
   AM_Rte_BusNumber,AM_StopSrv_BellTime, AM_school_desc, 
  AM_StuTrip_FreqDisplay, AM_TripAsgn_DistToStop, AM_StopSrv_Stop_Idx,AM_StopSrv_AsgnAvg,  
  AM_StopSrv_HdcntAvg,AM_StuTrip_Desc,AM_Rte_Desc,    
  AM_Loc_Loc,PM_StopSrv_Idx,PM_stop_desc,PM_RunSrv_TimeAtSrv,PM_RunSrv_Run_Idx,PM_Run_Desc,PM_Rte_ID,
  PM_Rte_BusNumber,PM_StopSrv_BellTime,PM_school_desc, 
  PM_StuTrip_FreqDisplay, PM_TripAsgn_DistToStop, PM_StopSrv_Stop_Idx,PM_StopSrv_AsgnAvg,   
  PM_StopSrv_HdcntAvg,PM_StuTrip_Desc,PM_Rte_Desc,PM_Loc_Loc) select #buspass1.*, 
  PM_StopSrv_Idx,PM_stop_desc,PM_RunSrv_TimeAtSrv,PM_RunSrv_Run_Idx,   
  PM_Run_Desc,PM_Rte_ID,PM_Rte_BusNumber,PM_StopSrv_BellTime, PM_school_desc, PM_StuTrip_FreqDisplay,  
  PM_TripAsgn_DistToStop, PM_StopSrv_Stop_Idx,PM_StopSrv_AsgnAvg,  
  PM_StopSrv_HdcntAvg,PM_StuTrip_Desc,PM_Rte_Desc,PM_Loc_Loc 
 from #buspass1,#buspass2 where #buspass1.bp_stu_autoid=#buspass2.bp_stu_autoid