status change "active" to "expiry" based on time in sqltable

here is my database: USE [master] GO /****** Object: Database [MyBus] Script Date: 5/9/2014 5:01:10 PM ******/ CREATE DATABASE [MyBus] ON PRIMARY ( NAME = N'MyBus', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyBus.mdf' , SIZE = 9216KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'MyBus_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyBus_log.ldf' , SIZE = 57664KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [MyBus] SET COMPATIBILITY_LEVEL = 100 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [MyBus].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [MyBus] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [MyBus] SET ANSI_NULLS OFF GO ALTER DATABASE [MyBus] SET ANSI_PADDING OFF GO ALTER DATABASE [MyBus] SET ANSI_WARNINGS OFF GO ALTER DATABASE [MyBus] SET ARITHABORT OFF GO ALTER DATABASE [MyBus] SET AUTO_CLOSE ON GO ALTER DATABASE [MyBus] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [MyBus] SET AUTO_SHRINK OFF GO ALTER DATABASE [MyBus] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [MyBus] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [MyBus] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [MyBus] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [MyBus] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [MyBus] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [MyBus] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [MyBus] SET DISABLE_BROKER GO ALTER DATABASE [MyBus] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [MyBus] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [MyBus] SET TRUSTWORTHY OFF GO ALTER DATABASE [MyBus] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [MyBus] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [MyBus] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [MyBus] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [MyBus] SET RECOVERY SIMPLE GO ALTER DATABASE [MyBus] SET MULTI_USER GO ALTER DATABASE [MyBus] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [MyBus] SET DB_CHAINING OFF GO USE [MyBus] GO /****** Object: User [NTAUTHORITYMSSQLSERVER] Script Date: 5/9/2014 5:01:10 PM ******/ CREATE USER [NTAUTHORITYMSSQLSERVER] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo] GO ALTER ROLE [db_owner] ADD MEMBER [NTAUTHORITYMSSQLSERVER] GO /****** Object: StoredProcedure [dbo].[Add_bus] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[Add_bus] (@Bus_Name nvarchar(max),@Dep_Time datetime,@Arr_Time datetime,@Seat_Details int,@Bus_type varchar(200),@Seat_Arrangement varchar(100),@start nvarchar(max),@journey_date datetime,@available int,@booked int,@bus_ref int) as begin INSERT INTO [MyBus].[dbo].[Bus_Detail] ([Bus_Name] ,[Dep_Time] ,[Arr_Time] ,[Seat_Details] ,[Bus_Type] ,[Seat_Arrangement] ,[Start] ,[Journey_Date] ,[Available_Seats] ,[booked_seats],[bus_ref],[BusStatus]) VALUES (@Bus_Name,@Dep_Time,@Arr_Time,@Seat_Details,@Bus_type,@Seat_Arrangement,@start,@journey_date,@available,@booked,@bus_ref,'Active') end GO /****** Object: StoredProcedure [dbo].[add_new] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[add_new] (@Day varchar(100),@Heading varchar(500),@Content varchar(max)) as begin insert into News values(@Day,@Heading,@Content) end GO /****** Object: StoredProcedure [dbo].[bus_detail_report] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[bus_detail_report] as begin SELECT Bus_Detail.Bus_Name, Bus_Detail.Dep_Time, Bus_Detail.Arr_Time, Bus_Detail.Seat_Details, Bus_Detail.Bus_Type, Bus_Detail.Seat_Arrangement, Bus_Detail.Start, Bus_Detail.Journey_Date, Bus_Detail.Available_Seats, Bus_Detail.booked_seats, Bus_Detail.Bus_Ref, Destination.Destination AS Destinations FROM Bus_Detail INNER JOIN Destination ON Bus_Detail.Bus_Ref = Destination.Bus_ref end GO /****** Object: StoredProcedure [dbo].[clearcart] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[clearcart] As Begin DECLARE @AccountID varchar(20) DECLARE @getAccountID CURSOR SET @getAccountID = CURSOR FOR select cast(Bus_Sno as int) from Bus_Detail where BusStatus ='Active' OPEN @getAccountID FETCH NEXT FROM @getAccountID INTO @AccountID WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @AccountID1 varchar(20),@SQL1 nvarchar(max),@SQL2 nvarchar(max) DECLARE @getAccountID1 CURSOR SET @getAccountID1 = CURSOR FOR select seat from Seat_cart where seat not in (select seat from orders where BusDetail = @AccountID)and Bus_sno = @AccountID OPEN @getAccountID1 FETCH NEXT FROM @getAccountID1 INTO @AccountID1 WHILE @@FETCH_STATUS = 0 BEGIN set @SQL1 = 'Update seats_layout set '+ @AccountID1 +' = ''1'' where Bus_Sno = '+ @AccountID +' ' set @SQL2 ='Delete from seat_cart where bus_sno = '''+ @AccountID + ''' and seat = '''+ @AccountID1 +''' ' /*print @sql1 print @sql2*/ exec sp_executesql @sql1 exec sp_executesql @sql2 FETCH NEXT FROM @getAccountID1 INTO @AccountID1 END CLOSE @getAccountID1 DEALLOCATE @getAccountID1 FETCH NEXT FROM @getAccountID INTO @AccountID END CLOSE @getAccountID DEALLOCATE @getAccountID End GO /****** Object: StoredProcedure [dbo].[customer_report] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[customer_report] as begin SELECT DISTINCT OrderDetails.emailid, orders.Passangername, orders.BusDetail, orders.ContactNo, orders.StartPlace, orders.Destination, OrderDetails.Pnr FROM OrderDetails INNER JOIN orders ON OrderDetails.Pnr = orders.Pnr order by Pnr end GO /****** Object: StoredProcedure [dbo].[delete_agent] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[delete_agent] (@agent_id varchar(300)) as begin delete from AgentBasicInfo where Agent_id=@agent_id end GO /****** Object: StoredProcedure [dbo].[delete_bus] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[delete_bus] (@bus_id varchar(100)) as begin delete from Bus_Detail where Bus_Sno=@bus_id end GO /****** Object: StoredProcedure [dbo].[dummy] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[dummy] as begin select * from Bus_Detail end GO /****** Object: StoredProcedure [dbo].[insert_into_agent] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[insert_into_agent] (@Agent_id varchar(200),@Agent_name varchar(200),@Agent_fname varchar(150),@Agent_shop_name nvarchar(200),@Agent_shop_add nvarchar(250),@Agent_shop_city varchar(150),@Agent_phone_number numeric(18,0),@Agent_mobile_number varchar(200) ,@Agent_current_bal money,@Agent_pass varchar(200),@Agent_Type varchar(50),@Question nvarchar(max),@Answer nvarchar(max)) as begin INSERT INTO [MyBus].[dbo].[AgentBasicInfo] ([Agent_id] ,[Agent_name] ,[Agent_fname] ,[Agent_shop_name] ,[Agent_shop_add] ,[Agent_shop_city] ,[Agent_phone_number] ,[Agent_mobile_number] ,[Agent_current_bal] ,[Agent_pass] ,[Agent_Type] ,[Question] ,[Answer]) VALUES (@Agent_id,@Agent_name,@Agent_fname,@Agent_shop_name,@Agent_shop_add,@Agent_shop_city,@Agent_phone_number,@Agent_mobile_number,@Agent_current_bal,@Agent_pass,@Agent_Type,@Question,@Answer) end GO /****** Object: UserDefinedFunction [dbo].[UFN_HourMinuteSecond] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE Function [dbo].[UFN_HourMinuteSecond] ( @StartDateTimes DateTime, @startTime Time, @EndDateTime DateTime ) Returns Varchar(10) As Begin Declare @Seconds Int, @Minute Int, @Hour Int, @Elapsed Varchar(10), @StartDateTime DateTime select @StartDateTime = @startDateTimes + @StartTime Select @Seconds = ABS(DateDiff(SECOND ,@StartDateTime,@EndDateTime)) If @Seconds >= 60 Begin select @Minute = @Seconds/60 select @Seconds = @Seconds%60 If @Minute >= 60 begin select @hour = @Minute/60 select @Minute = @Minute%60 end Else Goto Final End Final: Select @Hour = Isnull(@Hour,0), @Minute = IsNull(@Minute,0), @Seconds = IsNull(@Seconds,0) select @Elapsed = Cast(@Hour as Varchar) + ':' + Cast(@Minute as Varchar) + ':' + Cast(@Seconds as Varchar) Return (@Elapsed) End GO /****** Object: Table [dbo].[AgentBasicInfo] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[AgentBasicInfo]( [Agent_id] [varchar](50) NULL, [Agent_name] [char](15) NULL, [Agent_fname] [char](15) NULL, [Agent_shop_name] [nchar](20) NULL, [Agent_shop_add] [nvarchar](50) NULL, [Agent_shop_city] [nchar](15) NULL, [Agent_phone_number] [numeric](18, 0) NULL, [Agent_mobile_number] [varchar](200) NULL, [Agent_current_bal] [money] NULL, [Agent_pass] [varchar](200) NULL, [Agent_Type] [varchar](50) NULL, [Question] [nvarchar](max) NULL, [Answer] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Bus_Detail] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Bus_Detail]( [Bus_Sno] [int] IDENTITY(1,1) NOT NULL, [Bus_Name] [varchar](50) NULL, [Dep_Time] [time](7) NULL, [Arr_Time] [time](7) NULL, [Seat_Details] [int] NULL, [Fare] [money] NULL, [Bus_Type] [varchar](100) NULL, [Seat_Arrangement] [nvarchar](50) NULL, [Start] [nvarchar](50) NULL, [Destination] [nvarchar](50) NULL, [Journey_Date] [date] NULL, [Available_Seats] [int] NULL, [booked_seats] [int] NULL, [Bus_Ref] [int] NULL, [BusStatus] [nvarchar](50) NULL, CONSTRAINT [PK_Bus_Detail] PRIMARY KEY CLUSTERED ( [Bus_Sno] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[load_requests] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[load_requests]( [PNR] [numeric](18, 0) NULL, [hours_difference] [numeric](18, 0) NULL, [Date_of_Request] [datetime] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[master_transactions] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[master_transactions]( [Bus_Sno] [int] NULL, [Seats] [nchar](10) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[News] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[News]( [id] [int] IDENTITY(1,1) NOT NULL, [Head] [nvarchar](max) NULL, [Body] [nvarchar](max) NULL, [content] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[NON_AC_37] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[NON_AC_37]( [Bus_Sno] [int] NULL, [DOJ] [datetime] NULL, [s1] [varchar](10) NULL, [s2] [varchar](10) NULL, [s3] [varchar](10) NULL, [s4] [varchar](10) NULL, [s5] [varchar](10) NULL, [s6] [varchar](10) NULL, [s7] [varchar](10) NULL, [s8] [varchar](10) NULL, [s9] [varchar](10) NULL, [s10] [varchar](10) NULL, [s11] [varchar](10) NULL, [s12] [varchar](10) NULL, [s13] [varchar](10) NULL, [s14] [varchar](10) NULL, [s15] [varchar](10) NULL, [s16] [varchar](10) NULL, [s17] [varchar](10) NULL, [s18] [varchar](10) NULL, [s19] [varchar](10) NULL, [s20] [varchar](10) NULL, [s21] [varchar](10) NULL, [s22] [varchar](10) NULL, [s23] [varchar](10) NULL, [s24] [varchar](10) NULL, [s25] [varchar](10) NULL, [s26] [varchar](10) NULL, [s27] [varchar](10) NULL, [s28] [varchar](10) NULL, [s29] [varchar](10) NULL, [s30] [varchar](10) NULL, [s31] [varchar](10) NULL, [s32] [varchar](10) NULL, [s33] [varchar](10) NULL, [s34] [varchar](10) NULL, [s35] [varchar](10) NULL, [s36] [varchar](10) NULL, [s37] [varchar](10) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[seats_layout] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[seats_layout]( [Bus_Sno] [int] NULL, [DOJ] [datetime] NULL, [s1] [varchar](10) NULL, [s2] [varchar](10) NULL, [s3] [varchar](10) NULL, [s4] [varchar](10) NULL, [s5] [varchar](10) NULL, [s6] [varchar](10) NULL, [s7] [varchar](10) NULL, [s8] [varchar](10) NULL, [s9] [varchar](10) NULL, [s10] [varchar](10) NULL, [s11] [varchar](10) NULL, [s12] [varchar](10) NULL, [s13] [varchar](10) NULL, [s14] [varchar](10) NULL, [s15] [varchar](10) NULL, [s16] [varchar](10) NULL, [s17] [varchar](10) NULL, [s18] [varchar](10) NULL, [s19] [varchar](10) NULL, [s20] [varchar](10) NULL, [s21] [varchar](10) NULL, [s22] [varchar](10) NULL, [s23] [varchar](10) NULL, [s24] [varchar](10) NULL, [s25] [varchar](10) NULL, [s26] [varchar](10) NULL, [s27] [varchar](10) NULL, [s28] [varchar](10) NULL, [s29] [varchar](10) NULL, [s30] [varchar](10) NULL, [s31] [varchar](10) NULL, [s32] [varchar](10) NULL, [s33] [varchar](10) NULL, [s34] [varchar](10) NULL, [s35] [varchar](10) NULL, [s36] [varchar](10) NULL, [s37] [varchar](10) NULL, [s38] [varchar](10) NULL, [s39] [varchar](10) NULL, [s40] [varchar](10) NULL, [s41] [varchar](10) NULL, [s42] [varchar](10) NULL, [s43] [varchar](10) NULL, [s44] [varchar](10) NULL, [s45] [varchar](10) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[States] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[States]( [Date] [varchar](50) NULL, [Time] [varchar](50) NULL, [Station] [varchar](50) NULL, [s1] [char](1) NULL, [s2] [char](1) NULL, [s3] [char](1) NULL, [s4] [char](1) NULL, [s5] [char](1) NULL, [s6] [char](1) NULL, [s7] [char](1) NULL, [s8] [char](1) NULL, [s9] [char](1) NULL, [s10] [char](1) NULL, [s11] [char](1) NULL, [s12] [char](1) NULL, [s13] [char](1) NULL, [s14] [char](1) NULL, [s15] [char](1) NULL, [s16] [char](1) NULL, [s17] [char](1) NULL, [s18] [char](1) NULL, [s19] [char](1) NULL, [s20] [char](1) NULL, [s21] [char](1) NULL, [s22] [char](1) NULL, [s23] [char](1) NULL, [s24] [char](1) NULL, [s25] [char](1) NULL, [s26] [char](1) NULL, [s27] [char](1) NULL, [s28] [char](1) NULL, [s29] [char](1) NULL, [s30] [char](1) NULL, [s31] [char](1) NULL, [Bus_Sno] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Time_list] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Time_list]( [Sno] [numeric](5, 0) NULL, [Station_name] [nchar](15) NULL, [Rate_per_seat] [numeric](5, 0) NULL, [Time] [text] NULL, [Reach_time] [text] NULL, [Bus_number] [nvarchar](50) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[transaction] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[transaction]( [Transid] [int] IDENTITY(1,1) NOT NULL, [Bus_Seat] [int] NOT NULL, [timestamp] [time](7) NOT NULL, CONSTRAINT [PK_transaction] PRIMARY KEY CLUSTERED ( [Bus_Seat] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[transactions_table] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[transactions_table]( [Trans_id] [int] IDENTITY(1,1) NOT NULL, [Bus_Seat] [nchar](10) NOT NULL, [TimeStamp] [time](7) NOT NULL, CONSTRAINT [PK_transactions_table] PRIMARY KEY CLUSTERED ( [Bus_Seat] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_Applications] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_Applications]( [ApplicationName] [nvarchar](256) NOT NULL, [LoweredApplicationName] [nvarchar](256) NOT NULL, [ApplicationId] [uniqueidentifier] NOT NULL, [Description] [nvarchar](256) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_MembershipUsers] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_MembershipUsers]( [UserId] [uniqueidentifier] NOT NULL, [PasswordFormat] [int] NOT NULL, [MobilePIN] [nvarchar](16) NULL, [Email] [nvarchar](256) NULL, [LoweredEmail] [nvarchar](256) NULL, [PasswordQuestion] [nvarchar](256) NULL, [PasswordAnswer] [nvarchar](128) NULL, [IsApproved] [bit] NOT NULL, [IsLockedOut] [bit] NOT NULL, [CreateDate] [datetime] NOT NULL, [LastLoginDate] [datetime] NOT NULL, [LastPasswordChangedDate] [datetime] NOT NULL, [LastLockoutDate] [datetime] NOT NULL, [FailedPasswordAttemptCount] [int] NOT NULL, [FailedPasswordAttemptWindowStart] [datetime] NOT NULL, [FailedPasswordAnswerAttemptCount] [int] NOT NULL, [FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL, [Comment] [ntext] NULL, [ApplicationId] [uniqueidentifier] NOT NULL, [UserName] [nvarchar](256) NOT NULL, [MobileAlias] [nvarchar](16) NULL, [IsAnonymous] [bit] NOT NULL, [LastActivityDate] [datetime] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_Profiles] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_Profiles]( [UserId] [uniqueidentifier] NOT NULL, [LastUpdatedDate] [datetime] NOT NULL, [DataSize] [int] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_Roles] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_Roles]( [ApplicationId] [uniqueidentifier] NOT NULL, [RoleId] [uniqueidentifier] NOT NULL, [RoleName] [nvarchar](256) NOT NULL, [LoweredRoleName] [nvarchar](256) NOT NULL, [Description] [nvarchar](256) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_Users] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_Users]( [ApplicationId] [uniqueidentifier] NOT NULL, [UserId] [uniqueidentifier] NOT NULL, [UserName] [nvarchar](256) NOT NULL, [LoweredUserName] [nvarchar](256) NOT NULL, [MobileAlias] [nvarchar](16) NULL, [IsAnonymous] [bit] NOT NULL, [LastActivityDate] [datetime] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_UsersInRoles] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_UsersInRoles]( [UserId] [uniqueidentifier] NOT NULL, [RoleId] [uniqueidentifier] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_WebPartState_Paths] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_WebPartState_Paths]( [ApplicationId] [uniqueidentifier] NOT NULL, [PathId] [uniqueidentifier] NOT NULL, [Path] [nvarchar](256) NOT NULL, [LoweredPath] [nvarchar](256) NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_WebPartState_Shared] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_WebPartState_Shared]( [PathId] [uniqueidentifier] NOT NULL, [DataSize] [int] NULL, [LastUpdatedDate] [datetime] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_WebPartState_User] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_WebPartState_User]( [PathId] [uniqueidentifier] NULL, [UserId] [uniqueidentifier] NULL, [DataSize] [int] NULL, [LastUpdatedDate] [datetime] NOT NULL ) ON [PRIMARY] GO USE [master] GO ALTER DATABASE [MyBus] SET READ_WRITE GO

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

rinku baruah12y ago
i am not able to create the job. please help or create the job for me. this is my humble request to you.
Khan Abrar Ahmed12y ago
Hi Please refer the below links
http://www.c-sharpcorner.com/UploadFile/0fca19/step-by-step-procedure-to-create-a-job-task-in-sql-server-20/
http://www.c-sharpcorner.com/UploadFile/0f68f2/delete-and-create-files-with-a-sql-job-in-sql-server-2008/
rinku baruah12y ago
how can i create a sql job for that please help me?
Khan Abrar Ahmed12y ago
Hi, I think you want to update the bus status to expiry when its depature time is passed.??
if so then create a sql job which will look the table and system datatime if time is passed then update the table with status as expiry.
rinku baruah12y ago
my .aspx code id is-

    } protected void add_bus()
{
cmd = new SqlCommand("Add_bus", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("Bus_Name", System.Data.SqlDbType.VarChar).Value = Bus_Name.Text;
cmd.Parameters.Add("Dep_Time", System.Data.SqlDbType.DateTime).Value = Dep_Time.Text;
cmd.Parameters.Add("Arr_Time", System.Data.SqlDbType.DateTime).Value = Arr_Time.Text;
cmd.Parameters.Add("Seat_Details", System.Data.SqlDbType.Int).Value = check_seat_details(Bus_type.Text);
//cmd.Parameters.Add("Fare", System.Data.SqlDbType.Money).Value = Convert.ToDouble(Fare.Text);
cmd.Parameters.Add("Bus_type", System.Data.SqlDbType.VarChar).Value = Bus_type.Text;
cmd.Parameters.Add("Seat_Arrangement", System.Data.SqlDbType.VarChar).Value = "NIL";
cmd.Parameters.Add("start", System.Data.SqlDbType.VarChar).Value = Start_Place.Text;
// cmd.Parameters.Add("destination", System.Data.SqlDbType.VarChar).Value = Dest_Place.Text;
cmd.Parameters.Add("journey_date", System.Data.SqlDbType.DateTime).Value = Convert.ToDateTime(Date.Text);
cmd.Parameters.Add("available ", System.Data.SqlDbType.Int).Value = check_seat_details(Bus_type.Text);
cmd.Parameters.Add("booked", System.Data.SqlDbType.Int).Value = Convert.ToInt16("0");
cmd.Parameters.Add("bus_ref", System.Data.SqlDbType.Int).Value = get_bus_ref();
cmd.ExecuteNonQuery();
add_destinations();
}

and my sql table is -

/****** Object: StoredProcedure [dbo].[Add_bus]    Script Date: 5/9/2014 5:01:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[Add_bus]
(@Bus_Name nvarchar(max),@Dep_Time datetime,@Arr_Time datetime,@Seat_Details int,@Bus_type varchar(200),@Seat_Arrangement varchar(100),@start nvarchar(max),@journey_date datetime,@available int,@booked int,@bus_ref int)
as
begin
INSERT INTO [MyBus].[dbo].[Bus_Detail]
           ([Bus_Name]
           ,[Dep_Time]
           ,[Arr_Time]
           ,[Seat_Details]

           ,[Bus_Type]
           ,[Seat_Arrangement]
           ,[Start]
           ,[Journey_Date]
           ,[Available_Seats]
           ,[booked_seats],[bus_ref],[BusStatus])
     VALUES
           (@Bus_Name,@Dep_Time,@Arr_Time,@Seat_Details,@Bus_type,@Seat_Arrangement,@start,@journey_date,@available,@booked,@bus_ref,'Active')

end

/****** Object: Table [dbo].[Bus_Detail] Script Date: 5/9/2014 5:01:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Bus_Detail](
[Bus_Sno] [int] IDENTITY(1,1) NOT NULL,
[Bus_Name] [varchar](50) NULL,
[Dep_Time] [time](7) NULL,
[Arr_Time] [time](7) NULL,
[Seat_Details] [int] NULL,
[Fare] [money] NULL,
[Bus_Type] [varchar](100) NULL,
[Seat_Arrangement] [nvarchar](50) NULL,
[Start] [nvarchar](50) NULL,
[Destination] [nvarchar](50) NULL,
[Journey_Date] [date] NULL,
[Available_Seats] [int] NULL,
[booked_seats] [int] NULL,
[Bus_Ref] [int] NULL,
[BusStatus] [nvarchar](50) NULL,
CONSTRAINT [PK_Bus_Detail] PRIMARY KEY CLUSTERED
(
[Bus_Sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
Akhil Mittal12y ago
make use of "as" keyword
Khan Abrar Ahmed12y ago
Dear Rinku,
please elobrate you question and send only the script where you are having a issue instead of full db objects.
rinku baruah12y ago
here is my database: USE [master] GO /****** Object: Database [MyBus] Script Date: 5/9/2014 5:01:10 PM ******/ CREATE DATABASE [MyBus] ON PRIMARY ( NAME = N'MyBus', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyBus.mdf' , SIZE = 9216KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'MyBus_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyBus_log.ldf' , SIZE = 57664KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [MyBus] SET COMPATIBILITY_LEVEL = 100 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [MyBus].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [MyBus] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [MyBus] SET ANSI_NULLS OFF GO ALTER DATABASE [MyBus] SET ANSI_PADDING OFF GO ALTER DATABASE [MyBus] SET ANSI_WARNINGS OFF GO ALTER DATABASE [MyBus] SET ARITHABORT OFF GO ALTER DATABASE [MyBus] SET AUTO_CLOSE ON GO ALTER DATABASE [MyBus] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [MyBus] SET AUTO_SHRINK OFF GO ALTER DATABASE [MyBus] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [MyBus] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [MyBus] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [MyBus] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [MyBus] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [MyBus] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [MyBus] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [MyBus] SET DISABLE_BROKER GO ALTER DATABASE [MyBus] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [MyBus] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [MyBus] SET TRUSTWORTHY OFF GO ALTER DATABASE [MyBus] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [MyBus] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [MyBus] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [MyBus] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [MyBus] SET RECOVERY SIMPLE GO ALTER DATABASE [MyBus] SET MULTI_USER GO ALTER DATABASE [MyBus] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [MyBus] SET DB_CHAINING OFF GO USE [MyBus] GO /****** Object: User [NTAUTHORITYMSSQLSERVER] Script Date: 5/9/2014 5:01:10 PM ******/ CREATE USER [NTAUTHORITYMSSQLSERVER] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo] GO ALTER ROLE [db_owner] ADD MEMBER [NTAUTHORITYMSSQLSERVER] GO /****** Object: StoredProcedure [dbo].[Add_bus] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[Add_bus] (@Bus_Name nvarchar(max),@Dep_Time datetime,@Arr_Time datetime,@Seat_Details int,@Bus_type varchar(200),@Seat_Arrangement varchar(100),@start nvarchar(max),@journey_date datetime,@available int,@booked int,@bus_ref int) as begin INSERT INTO [MyBus].[dbo].[Bus_Detail] ([Bus_Name] ,[Dep_Time] ,[Arr_Time] ,[Seat_Details] ,[Bus_Type] ,[Seat_Arrangement] ,[Start] ,[Journey_Date] ,[Available_Seats] ,[booked_seats],[bus_ref],[BusStatus]) VALUES (@Bus_Name,@Dep_Time,@Arr_Time,@Seat_Details,@Bus_type,@Seat_Arrangement,@start,@journey_date,@available,@booked,@bus_ref,'Active') end GO /****** Object: StoredProcedure [dbo].[add_new] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[add_new] (@Day varchar(100),@Heading varchar(500),@Content varchar(max)) as begin insert into News values(@Day,@Heading,@Content) end GO /****** Object: StoredProcedure [dbo].[bus_detail_report] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[bus_detail_report] as begin SELECT Bus_Detail.Bus_Name, Bus_Detail.Dep_Time, Bus_Detail.Arr_Time, Bus_Detail.Seat_Details, Bus_Detail.Bus_Type, Bus_Detail.Seat_Arrangement, Bus_Detail.Start, Bus_Detail.Journey_Date, Bus_Detail.Available_Seats, Bus_Detail.booked_seats, Bus_Detail.Bus_Ref, Destination.Destination AS Destinations FROM Bus_Detail INNER JOIN Destination ON Bus_Detail.Bus_Ref = Destination.Bus_ref end GO /****** Object: StoredProcedure [dbo].[clearcart] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[clearcart] As Begin DECLARE @AccountID varchar(20) DECLARE @getAccountID CURSOR SET @getAccountID = CURSOR FOR select cast(Bus_Sno as int) from Bus_Detail where BusStatus ='Active' OPEN @getAccountID FETCH NEXT FROM @getAccountID INTO @AccountID WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @AccountID1 varchar(20),@SQL1 nvarchar(max),@SQL2 nvarchar(max) DECLARE @getAccountID1 CURSOR SET @getAccountID1 = CURSOR FOR select seat from Seat_cart where seat not in (select seat from orders where BusDetail = @AccountID)and Bus_sno = @AccountID OPEN @getAccountID1 FETCH NEXT FROM @getAccountID1 INTO @AccountID1 WHILE @@FETCH_STATUS = 0 BEGIN set @SQL1 = 'Update seats_layout set '+ @AccountID1 +' = ''1'' where Bus_Sno = '+ @AccountID +' ' set @SQL2 ='Delete from seat_cart where bus_sno = '''+ @AccountID + ''' and seat = '''+ @AccountID1 +''' ' /*print @sql1 print @sql2*/ exec sp_executesql @sql1 exec sp_executesql @sql2 FETCH NEXT FROM @getAccountID1 INTO @AccountID1 END CLOSE @getAccountID1 DEALLOCATE @getAccountID1 FETCH NEXT FROM @getAccountID INTO @AccountID END CLOSE @getAccountID DEALLOCATE @getAccountID End GO /****** Object: StoredProcedure [dbo].[customer_report] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[customer_report] as begin SELECT DISTINCT OrderDetails.emailid, orders.Passangername, orders.BusDetail, orders.ContactNo, orders.StartPlace, orders.Destination, OrderDetails.Pnr FROM OrderDetails INNER JOIN orders ON OrderDetails.Pnr = orders.Pnr order by Pnr end GO /****** Object: StoredProcedure [dbo].[delete_agent] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[delete_agent] (@agent_id varchar(300)) as begin delete from AgentBasicInfo where Agent_id=@agent_id end GO /****** Object: StoredProcedure [dbo].[delete_bus] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[delete_bus] (@bus_id varchar(100)) as begin delete from Bus_Detail where Bus_Sno=@bus_id end GO /****** Object: StoredProcedure [dbo].[dummy] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[dummy] as begin select * from Bus_Detail end GO /****** Object: StoredProcedure [dbo].[insert_into_agent] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[insert_into_agent] (@Agent_id varchar(200),@Agent_name varchar(200),@Agent_fname varchar(150),@Agent_shop_name nvarchar(200),@Agent_shop_add nvarchar(250),@Agent_shop_city varchar(150),@Agent_phone_number numeric(18,0),@Agent_mobile_number varchar(200) ,@Agent_current_bal money,@Agent_pass varchar(200),@Agent_Type varchar(50),@Question nvarchar(max),@Answer nvarchar(max)) as begin INSERT INTO [MyBus].[dbo].[AgentBasicInfo] ([Agent_id] ,[Agent_name] ,[Agent_fname] ,[Agent_shop_name] ,[Agent_shop_add] ,[Agent_shop_city] ,[Agent_phone_number] ,[Agent_mobile_number] ,[Agent_current_bal] ,[Agent_pass] ,[Agent_Type] ,[Question] ,[Answer]) VALUES (@Agent_id,@Agent_name,@Agent_fname,@Agent_shop_name,@Agent_shop_add,@Agent_shop_city,@Agent_phone_number,@Agent_mobile_number,@Agent_current_bal,@Agent_pass,@Agent_Type,@Question,@Answer) end GO /****** Object: UserDefinedFunction [dbo].[UFN_HourMinuteSecond] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE Function [dbo].[UFN_HourMinuteSecond] ( @StartDateTimes DateTime, @startTime Time, @EndDateTime DateTime ) Returns Varchar(10) As Begin Declare @Seconds Int, @Minute Int, @Hour Int, @Elapsed Varchar(10), @StartDateTime DateTime select @StartDateTime = @startDateTimes + @StartTime Select @Seconds = ABS(DateDiff(SECOND ,@StartDateTime,@EndDateTime)) If @Seconds >= 60 Begin select @Minute = @Seconds/60 select @Seconds = @Seconds%60 If @Minute >= 60 begin select @hour = @Minute/60 select @Minute = @Minute%60 end Else Goto Final End Final: Select @Hour = Isnull(@Hour,0), @Minute = IsNull(@Minute,0), @Seconds = IsNull(@Seconds,0) select @Elapsed = Cast(@Hour as Varchar) + ':' + Cast(@Minute as Varchar) + ':' + Cast(@Seconds as Varchar) Return (@Elapsed) End GO /****** Object: Table [dbo].[AgentBasicInfo] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[AgentBasicInfo]( [Agent_id] [varchar](50) NULL, [Agent_name] [char](15) NULL, [Agent_fname] [char](15) NULL, [Agent_shop_name] [nchar](20) NULL, [Agent_shop_add] [nvarchar](50) NULL, [Agent_shop_city] [nchar](15) NULL, [Agent_phone_number] [numeric](18, 0) NULL, [Agent_mobile_number] [varchar](200) NULL, [Agent_current_bal] [money] NULL, [Agent_pass] [varchar](200) NULL, [Agent_Type] [varchar](50) NULL, [Question] [nvarchar](max) NULL, [Answer] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Bus_Detail] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Bus_Detail]( [Bus_Sno] [int] IDENTITY(1,1) NOT NULL, [Bus_Name] [varchar](50) NULL, [Dep_Time] [time](7) NULL, [Arr_Time] [time](7) NULL, [Seat_Details] [int] NULL, [Fare] [money] NULL, [Bus_Type] [varchar](100) NULL, [Seat_Arrangement] [nvarchar](50) NULL, [Start] [nvarchar](50) NULL, [Destination] [nvarchar](50) NULL, [Journey_Date] [date] NULL, [Available_Seats] [int] NULL, [booked_seats] [int] NULL, [Bus_Ref] [int] NULL, [BusStatus] [nvarchar](50) NULL, CONSTRAINT [PK_Bus_Detail] PRIMARY KEY CLUSTERED ( [Bus_Sno] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[load_requests] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[load_requests]( [PNR] [numeric](18, 0) NULL, [hours_difference] [numeric](18, 0) NULL, [Date_of_Request] [datetime] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[master_transactions] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[master_transactions]( [Bus_Sno] [int] NULL, [Seats] [nchar](10) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[News] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[News]( [id] [int] IDENTITY(1,1) NOT NULL, [Head] [nvarchar](max) NULL, [Body] [nvarchar](max) NULL, [content] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[NON_AC_37] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[NON_AC_37]( [Bus_Sno] [int] NULL, [DOJ] [datetime] NULL, [s1] [varchar](10) NULL, [s2] [varchar](10) NULL, [s3] [varchar](10) NULL, [s4] [varchar](10) NULL, [s5] [varchar](10) NULL, [s6] [varchar](10) NULL, [s7] [varchar](10) NULL, [s8] [varchar](10) NULL, [s9] [varchar](10) NULL, [s10] [varchar](10) NULL, [s11] [varchar](10) NULL, [s12] [varchar](10) NULL, [s13] [varchar](10) NULL, [s14] [varchar](10) NULL, [s15] [varchar](10) NULL, [s16] [varchar](10) NULL, [s17] [varchar](10) NULL, [s18] [varchar](10) NULL, [s19] [varchar](10) NULL, [s20] [varchar](10) NULL, [s21] [varchar](10) NULL, [s22] [varchar](10) NULL, [s23] [varchar](10) NULL, [s24] [varchar](10) NULL, [s25] [varchar](10) NULL, [s26] [varchar](10) NULL, [s27] [varchar](10) NULL, [s28] [varchar](10) NULL, [s29] [varchar](10) NULL, [s30] [varchar](10) NULL, [s31] [varchar](10) NULL, [s32] [varchar](10) NULL, [s33] [varchar](10) NULL, [s34] [varchar](10) NULL, [s35] [varchar](10) NULL, [s36] [varchar](10) NULL, [s37] [varchar](10) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[seats_layout] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[seats_layout]( [Bus_Sno] [int] NULL, [DOJ] [datetime] NULL, [s1] [varchar](10) NULL, [s2] [varchar](10) NULL, [s3] [varchar](10) NULL, [s4] [varchar](10) NULL, [s5] [varchar](10) NULL, [s6] [varchar](10) NULL, [s7] [varchar](10) NULL, [s8] [varchar](10) NULL, [s9] [varchar](10) NULL, [s10] [varchar](10) NULL, [s11] [varchar](10) NULL, [s12] [varchar](10) NULL, [s13] [varchar](10) NULL, [s14] [varchar](10) NULL, [s15] [varchar](10) NULL, [s16] [varchar](10) NULL, [s17] [varchar](10) NULL, [s18] [varchar](10) NULL, [s19] [varchar](10) NULL, [s20] [varchar](10) NULL, [s21] [varchar](10) NULL, [s22] [varchar](10) NULL, [s23] [varchar](10) NULL, [s24] [varchar](10) NULL, [s25] [varchar](10) NULL, [s26] [varchar](10) NULL, [s27] [varchar](10) NULL, [s28] [varchar](10) NULL, [s29] [varchar](10) NULL, [s30] [varchar](10) NULL, [s31] [varchar](10) NULL, [s32] [varchar](10) NULL, [s33] [varchar](10) NULL, [s34] [varchar](10) NULL, [s35] [varchar](10) NULL, [s36] [varchar](10) NULL, [s37] [varchar](10) NULL, [s38] [varchar](10) NULL, [s39] [varchar](10) NULL, [s40] [varchar](10) NULL, [s41] [varchar](10) NULL, [s42] [varchar](10) NULL, [s43] [varchar](10) NULL, [s44] [varchar](10) NULL, [s45] [varchar](10) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[States] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[States]( [Date] [varchar](50) NULL, [Time] [varchar](50) NULL, [Station] [varchar](50) NULL, [s1] [char](1) NULL, [s2] [char](1) NULL, [s3] [char](1) NULL, [s4] [char](1) NULL, [s5] [char](1) NULL, [s6] [char](1) NULL, [s7] [char](1) NULL, [s8] [char](1) NULL, [s9] [char](1) NULL, [s10] [char](1) NULL, [s11] [char](1) NULL, [s12] [char](1) NULL, [s13] [char](1) NULL, [s14] [char](1) NULL, [s15] [char](1) NULL, [s16] [char](1) NULL, [s17] [char](1) NULL, [s18] [char](1) NULL, [s19] [char](1) NULL, [s20] [char](1) NULL, [s21] [char](1) NULL, [s22] [char](1) NULL, [s23] [char](1) NULL, [s24] [char](1) NULL, [s25] [char](1) NULL, [s26] [char](1) NULL, [s27] [char](1) NULL, [s28] [char](1) NULL, [s29] [char](1) NULL, [s30] [char](1) NULL, [s31] [char](1) NULL, [Bus_Sno] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Time_list] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Time_list]( [Sno] [numeric](5, 0) NULL, [Station_name] [nchar](15) NULL, [Rate_per_seat] [numeric](5, 0) NULL, [Time] [text] NULL, [Reach_time] [text] NULL, [Bus_number] [nvarchar](50) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[transaction] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[transaction]( [Transid] [int] IDENTITY(1,1) NOT NULL, [Bus_Seat] [int] NOT NULL, [timestamp] [time](7) NOT NULL, CONSTRAINT [PK_transaction] PRIMARY KEY CLUSTERED ( [Bus_Seat] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[transactions_table] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[transactions_table]( [Trans_id] [int] IDENTITY(1,1) NOT NULL, [Bus_Seat] [nchar](10) NOT NULL, [TimeStamp] [time](7) NOT NULL, CONSTRAINT [PK_transactions_table] PRIMARY KEY CLUSTERED ( [Bus_Seat] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_Applications] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_Applications]( [ApplicationName] [nvarchar](256) NOT NULL, [LoweredApplicationName] [nvarchar](256) NOT NULL, [ApplicationId] [uniqueidentifier] NOT NULL, [Description] [nvarchar](256) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_MembershipUsers] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_MembershipUsers]( [UserId] [uniqueidentifier] NOT NULL, [PasswordFormat] [int] NOT NULL, [MobilePIN] [nvarchar](16) NULL, [Email] [nvarchar](256) NULL, [LoweredEmail] [nvarchar](256) NULL, [PasswordQuestion] [nvarchar](256) NULL, [PasswordAnswer] [nvarchar](128) NULL, [IsApproved] [bit] NOT NULL, [IsLockedOut] [bit] NOT NULL, [CreateDate] [datetime] NOT NULL, [LastLoginDate] [datetime] NOT NULL, [LastPasswordChangedDate] [datetime] NOT NULL, [LastLockoutDate] [datetime] NOT NULL, [FailedPasswordAttemptCount] [int] NOT NULL, [FailedPasswordAttemptWindowStart] [datetime] NOT NULL, [FailedPasswordAnswerAttemptCount] [int] NOT NULL, [FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL, [Comment] [ntext] NULL, [ApplicationId] [uniqueidentifier] NOT NULL, [UserName] [nvarchar](256) NOT NULL, [MobileAlias] [nvarchar](16) NULL, [IsAnonymous] [bit] NOT NULL, [LastActivityDate] [datetime] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_Profiles] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_Profiles]( [UserId] [uniqueidentifier] NOT NULL, [LastUpdatedDate] [datetime] NOT NULL, [DataSize] [int] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_Roles] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_Roles]( [ApplicationId] [uniqueidentifier] NOT NULL, [RoleId] [uniqueidentifier] NOT NULL, [RoleName] [nvarchar](256) NOT NULL, [LoweredRoleName] [nvarchar](256) NOT NULL, [Description] [nvarchar](256) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_Users] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_Users]( [ApplicationId] [uniqueidentifier] NOT NULL, [UserId] [uniqueidentifier] NOT NULL, [UserName] [nvarchar](256) NOT NULL, [LoweredUserName] [nvarchar](256) NOT NULL, [MobileAlias] [nvarchar](16) NULL, [IsAnonymous] [bit] NOT NULL, [LastActivityDate] [datetime] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_UsersInRoles] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_UsersInRoles]( [UserId] [uniqueidentifier] NOT NULL, [RoleId] [uniqueidentifier] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_WebPartState_Paths] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_WebPartState_Paths]( [ApplicationId] [uniqueidentifier] NOT NULL, [PathId] [uniqueidentifier] NOT NULL, [Path] [nvarchar](256) NOT NULL, [LoweredPath] [nvarchar](256) NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_WebPartState_Shared] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_WebPartState_Shared]( [PathId] [uniqueidentifier] NOT NULL, [DataSize] [int] NULL, [LastUpdatedDate] [datetime] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[vw_aspnet_WebPartState_User] Script Date: 5/9/2014 5:01:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[vw_aspnet_WebPartState_User]( [PathId] [uniqueidentifier] NULL, [UserId] [uniqueidentifier] NULL, [DataSize] [int] NULL, [LastUpdatedDate] [datetime] NOT NULL ) ON [PRIMARY] GO USE [master] GO ALTER DATABASE [MyBus] SET READ_WRITE GO
Khan Abrar Ahmed12y ago
Hi,
first the detail sproc is insert sproc, cna you please send the get sproc or in the get sproc you can set the where clause on status and order by with depature time.