|
|
|
---- created new table tms.tlStationType
|
|
--create table TMS.tlStationType
|
|
--(
|
|
-- ID int primary key identity(1,1)
|
|
-- , Code nvarchar(10) NOT NULL
|
|
-- , [Name] nvarchar(100) NOT NULL
|
|
-- , [Description] nvarchar(max) NOT NULL
|
|
-- , [Status] bit default(1) NULL
|
|
-- , [IsDeleted] bit default(0) NULL
|
|
-- , AddedBy nvarchar(100) NULL
|
|
-- , AddedOn datetime NULL
|
|
-- , ModifiedBy nvarchar(100) NULL
|
|
-- , ModifiedOn datetime NULL
|
|
--)
|
|
--insert into TMS.tlStationType(Code, [Name], [Description]) values('TVC-M', 'TVC-Manual', 'Manual Counting of TVC')
|
|
--insert into TMS.tlStationType(Code, [Name], [Description]) values('ATCC', 'TVC-Automatic', 'Automatic Counting of TVC')
|
|
--insert into TMS.tlStationType(Code, [Name], [Description]) values('Axle-M', 'Axle-Manual', 'Manual Counting of Axle')
|
|
--insert into TMS.tlStationType(Code, [Name], [Description]) values('WB', 'Axle-Weighbridge', 'Automatic Counting of Axle')
|
|
|
|
--ALTER table TMS.tdTrafficStation
|
|
--add StationTypeID int NULL default(1)
|
|
--ALTER TABLE [TMS].[tdTrafficStation] ADD CONSTRAINT [DF__tdTraffic__Stati__695FCAFD] DEFAULT ((1)) FOR [StationTypeID]
|
|
|
|
---- =======================================
|
|
|
|
---- added new column SurveyInterval in the TVC table and we need to fetch this data from sysadm.tsTimePeriod table
|
|
--ALTER TABLE [TMS].[tdTrafficVolumeCount]
|
|
--drop CONSTRAINT [FK_tdTrafficVolumeCount_tsTimePeriod]
|
|
--alter table TMS.tdTrafficVolumeCount
|
|
--add SurveyInterval int not null
|
|
|
|
--drop table sysadm.tsTimePeriod
|
|
|
|
--create TABLE [TMS].[tlTimePeriod]
|
|
--(
|
|
-- [ID] [int] IDENTITY(1,1) NOT NULL,
|
|
-- [Interval] int NOT NULL,
|
|
-- [Code] [nvarchar](13) NOT NULL,
|
|
-- [Status] [bit] NULL,
|
|
-- [IsDeleted] [bit] NULL,
|
|
-- [AddedBy] [nvarchar](100) NULL,
|
|
-- [AddedOn] [datetime] NULL,
|
|
-- [ModifiedBy] [nvarchar](100) NULL,
|
|
-- [ModifiedOn] [datetime] NULL,
|
|
-- CONSTRAINT [PK_tlTimePeriod] PRIMARY KEY CLUSTERED
|
|
--(
|
|
-- [Interval] ASC,
|
|
-- [Code] ASC
|
|
--)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
--) ON [PRIMARY]
|
|
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '00:00 - 01:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '01:00 - 02:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '02:00 - 03:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '03:00 - 04:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '04:00 - 05:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '05:00 - 06:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '06:00 - 07:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '07:00 - 08:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '08:00 - 09:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '09:00 - 10:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '10:00 - 11:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '11:00 - 12:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '12:00 - 13:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '13:00 - 14:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '14:00 - 15:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '15:00 - 16:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '16:00 - 17:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '17:00 - 18:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '18:00 - 19:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '19:00 - 20:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '20:00 - 21:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '21:00 - 22:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '22:00 - 23:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '23:00 - 00:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '00:00 - 00:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '00:30 - 01:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '01:00 - 01:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '01:30 - 02:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '02:00 - 02:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '02:30 - 03:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '03:00 - 03:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '03:30 - 04:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '04:00 - 04:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '04:30 - 05:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '05:00 - 05:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '05:30 - 06:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '06:00 - 06:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '06:30 - 07:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '07:00 - 07:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '07:30 - 08:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '08:00 - 08:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '08:30 - 09:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '09:00 - 09:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '09:30 - 10:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '10:00 - 10:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '10:30 - 11:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '11:00 - 11:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '11:30 - 12:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '12:00 - 12:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '12:30 - 13:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '13:00 - 13:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '13:30 - 14:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '14:00 - 14:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '14:30 - 15:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '15:00 - 15:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '15:30 - 16:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '16:00 - 16:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '16:30 - 17:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '17:00 - 17:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '17:30 - 18:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '18:00 - 18:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '18:30 - 19:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '19:00 - 19:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '19:30 - 20:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '20:00 - 20:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '20:30 - 21:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '21:00 - 21:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '21:30 - 22:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '22:00 - 22:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '22:30 - 23:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '23:00 - 23:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '23:30 - 00:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '00:00 - 00:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '00:15 - 00:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '00:30 - 00:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '00:45 - 01:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '01:00 - 01:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '01:15 - 01:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '01:30 - 01:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '01:45 - 02:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '02:00 - 02:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '02:15 - 02:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '02:30 - 02:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '02:45 - 03:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '03:00 - 03:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '03:15 - 03:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '03:30 - 03:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '03:45 - 04:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '04:00 - 04:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '04:15 - 04:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '04:30 - 04:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '04:45 - 05:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '05:00 - 05:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '05:15 - 05:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '05:30 - 05:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '05:45 - 06:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '06:00 - 06:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '06:15 - 06:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '06:30 - 06:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '06:45 - 07:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '07:00 - 07:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '07:15 - 07:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '07:30 - 07:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '07:45 - 08:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '08:00 - 08:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '08:15 - 08:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '08:30 - 08:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '08:45 - 09:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '09:00 - 09:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '09:15 - 09:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '09:30 - 09:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '09:45 - 10:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '10:00 - 10:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '10:15 - 10:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '10:30 - 10:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '10:45 - 11:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '11:00 - 11:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '11:15 - 11:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '11:30 - 11:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '11:45 - 12:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '12:00 - 12:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '12:15 - 12:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '12:30 - 12:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '12:45 - 13:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '13:00 - 13:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '13:15 - 13:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '13:30 - 13:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '13:45 - 14:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '14:00 - 14:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '14:15 - 14:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '14:30 - 14:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '14:45 - 15:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '15:00 - 15:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '15:15 - 15:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '15:30 - 15:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '15:45 - 16:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '16:00 - 16:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '16:15 - 16:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '16:30 - 16:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '16:45 - 17:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '17:00 - 17:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '17:15 - 17:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '17:30 - 17:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '17:45 - 18:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '18:00 - 18:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '18:15 - 18:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '18:30 - 18:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '18:45 - 19:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '19:00 - 19:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '19:15 - 19:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '19:30 - 19:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '19:45 - 20:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '20:00 - 20:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '20:15 - 20:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '20:30 - 20:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '20:45 - 21:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '21:00 - 21:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '21:15 - 21:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '21:30 - 21:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '21:45 - 22:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '22:00 - 22:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '22:15 - 22:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '22:30 - 22:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '22:45 - 23:00')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '23:00 - 23:15')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '23:15 - 23:30')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '23:30 - 23:45')
|
|
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '23:45 - 00:00')
|
|
|
|
--alter table TMS.tdTrafficVolumeCount
|
|
--add SurveyInterval int default(60) NOT NULL
|
|
|
|
--ALTER TABLE [TMS].[tdTrafficVolumeCount] WITH CHECK
|
|
--ADD CONSTRAINT [FK_tdTrafficVolumeCount_tdTrafficVolumeCount] FOREIGN KEY([SurveyInterval], [Time])
|
|
--REFERENCES [TMS].[tlTimePeriod] ([Interval], [Code])
|
|
|
|
---- ======================================
|
|
|
|
---- change the data in commoditytype table
|
|
--delete from tms.tlCommodityType
|
|
--DBCC CHECKIDENT ('tms.tlCommodityType', RESEED, 0)
|
|
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('TEX', 'Textiles & clothing', 'Textiles & clothing')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('FnV', 'Fruits and vegetables', 'Fruits and vegetables')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('BOK', 'Books', 'Books')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('BOT', 'Bottles', 'Bottles')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('BLD', 'Building materials', 'Building materials')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('FDG', 'Food grains', 'Food grains')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('CRA', 'Craters containing drinks', 'Craters containing drinks')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('ANI', 'Animals carrying', 'Animals carrying')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('FUL', 'Fuel related like Pretoleum products, oil, gas, etc.', 'Fuel related like Pretoleum products, oil, gas, etc.')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('FRN', 'Furnitures or wood products', 'Furnitures or wood products')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('IRN', 'Iron & steel', 'Iron & steel')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('MED', 'Medicines or Pharmaceutical Products', 'Medicines or Pharmaceutical Products')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('PAS', 'Passengers or Passenger vehicles', 'Passengers or Passenger vehicles')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('MAT', 'Materials like plastic, paint, solar equipments, stones, etc.', 'Materials like plastic, paint, solar equipments, stones, etc.')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('WTR', 'Water related items', 'Water related items')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('MIN', 'Minerals & ores', 'Minerals & ores')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('FERT', 'Fertilizers / Chemicals', 'Fertilizers / Chemicals')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('EMP', 'Empty', 'Empty')
|
|
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('OTH', 'Others', 'Others')
|
|
|
|
---- =======================================
|
|
|
|
---- changes in the legalmaxload table
|
|
--truncate table tms.tlLegalMaxLoad
|
|
--drop table tms.tlLegalMaxLoad
|
|
--create table TMS.tlLegalMaxLoad
|
|
--(
|
|
-- ID int identity(1,1) primary key not null,
|
|
-- Code nvarchar(10) not null,
|
|
-- [Name] nvarchar(100) not null,
|
|
-- AxleConf int not null,
|
|
-- PermAxleLoad float not null,
|
|
-- NoofTyres int null,
|
|
-- NoofLoads int null,
|
|
-- DmgFactFormula nvarchar(max) null,
|
|
-- Remarks nvarchar(max) null,
|
|
-- [Status] [bit] NULL,
|
|
-- [IsDeleted] [bit] NULL,
|
|
-- [AddedBy] [nvarchar](50) NULL,
|
|
-- [AddedOn] [datetime] NULL,
|
|
-- [ModifiedBy] [nvarchar](50) NULL,
|
|
-- [ModifiedOn] [datetime] NULL,
|
|
--)
|
|
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('SASW', 'Single Axle - Single Wheel', 1, 8, 2, 1, '((2*Axle Load)/6626)^4')
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('SADW', 'Single Axle - Dual Wheel', 2, 10, 4, 1, '((2*Axle Load)/8155)^4')
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('TASW', 'Tandem Axle - Single Wheel', 11, 16, 2, 2, '((2* 1st Axle Load)/6626)^4 + ((2* 2nd Axle Load)/6626)^4')
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('TADW', 'Tandem Axle - Dual Wheel', 22, 18, 8, 2, '((2*(1st Axle Load + 2nd Axle Load))/15086)^4')
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('TRSW', 'Tridem Axle - Single Wheel', 111, 22.5, 6, 3, '((2* 1st Axle Load)/6626)^4 + ((2* 2nd Axle Load)/6626)^4 + ((2* 3rd Axle Load)/6626)^4')
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('TRDW', 'Tridem Axle - Dual Wheel', 222, 24, 12, 3, '((2* (1st Axle Load + 2nd Axle Load + 3rd Axle Load))/22834)^4')
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('SASW_SADW', '2 Axle', 12, 18, 6, 2, '((2* 1st Axle Load)/6626)^4 + ((2* 2nd Axle Load)/8155)^4')
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('SADW_SASW', '2 Axle', 21, 18, 6, 2, '((2* 1st Axle Load)/8155)^4 + ((2 * 2nd Axle Load)/6626)^4')
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('SASW_TADW', '3 Axle', 122, 26, 10, 3, '((2* 1st Axle Load)/6626)^4 + ((2*(2nd Axle Load + 3rd Axle Load))/15086)^4')
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('TADW_SASW', '3 Axle', 221, 26, 10, 3, '((2* (1st Axle Load + 2nd Axle Load))/15086)^4 + ((2* 3rd Axle Load)/6626)^4')
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('LIFTS', 'Liftable Single ', 1, 8.5, NULL, NULL, '')
|
|
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('LIFTD', 'Liftable Single ', 2, 10, NULL, NULL, '')
|
|
|
|
---- =======================================
|
|
|
|
---- changes in axleconfig table
|
|
--drop table tms.tlAxleConfiguration
|
|
--create table TMS.tlAxleConfiguration
|
|
--(
|
|
-- ID int identity(1,1) primary key NOT NULL,
|
|
-- Code nvarchar(100) NOT NULL,
|
|
-- PermVehLoadDesc nvarchar(100) NOT NULL,
|
|
-- PermVehLoadVal float NOT NULL,
|
|
-- Tolerance float NOT NULL,
|
|
-- Remarks nvarchar(max) null,
|
|
-- [Status] [bit] NULL,
|
|
-- [IsDeleted] [bit] NULL,
|
|
-- [AddedBy] [nvarchar](50) NULL,
|
|
-- [AddedOn] [datetime] NULL,
|
|
-- [ModifiedBy] [nvarchar](50) NULL,
|
|
-- [ModifiedOn] [datetime] NULL,
|
|
--)
|
|
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2', '8+10', 18, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22', '8+18', 26, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_222', '8+18+24', 50, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_12_111', '8+8+10+22.5', 48.5, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_111', '8+18+22.5', 48.5, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_21', '8+10+8', 26, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_1_222', '8+8+24', 40, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_12_222', '8+8+10+24', 50, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_222', '8+10+24', 42, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_1_22', '8+8+18', 34, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_11_2_1', '8+16', 24, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_11_222', '8+16', 24, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_12', '8+18', 26, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_12_22', '8+18', 26, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_11', '8+10+16', 34, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_111', '8+10+22.5', 40.5, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_21_111', '8+18+22.5', 48.5, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_2_22', '8+18+10+18', 54, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_11', '8+18+16', 42, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_1_22', '8+18+8+18', 52, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_222', '8+24', 32, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_22', '8+10+18', 36, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_2_111', '8+18+10+22.5', 58.5, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_22', '8+18+18', 44, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_2_2', '8+18+10+10', 46, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_2_22', '8+10+10+18', 46, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_22_22', '8+10+18+18', 54, 5)
|
|
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_22_22', '8+18+18+18', 62, 5)
|
|
|
|
---- ===============================================
|
|
|
|
---- changes in axle load survey table
|
|
--drop table tms.tdAxleLoadSurvey
|
|
--CREATE TABLE [TMS].[tdAxleLoadSurvey]
|
|
--(
|
|
-- [ID] [int] IDENTITY(1,1) NOT NULL,
|
|
-- [SurveyDate] [datetime] NOT NULL,
|
|
-- [StationID] [int] NOT NULL,
|
|
-- [TrfDir] [nvarchar](15) NOT NULL,
|
|
-- [VehicleRegNo] [nvarchar](50) NULL,
|
|
-- [VehicleName] [nvarchar](100) NULL,
|
|
-- [CommodityID] [int] NOT NULL,
|
|
-- [Origin] [nvarchar](50) NULL,
|
|
-- [Destination] [nvarchar](50) NULL,
|
|
-- [AxleConfigID] [int] NOT NULL,
|
|
-- [Axle1Load] [float] NULL,
|
|
-- [Axle2Load] [float] NULL,
|
|
-- [Axle3Load] [float] NULL,
|
|
-- [Axle4Load] [float] NULL,
|
|
-- [Axle5Load] [float] NULL,
|
|
-- [Axle6Load] [float] NULL,
|
|
-- [Axle7Load] [float] NULL,
|
|
-- [Remarks] [nvarchar](1000) NULL,
|
|
-- [SurveyYear] [nvarchar](10) NOT NULL,
|
|
-- [NetworkYear] [nvarchar](10) NULL,
|
|
-- [Status] [bit] NULL,
|
|
-- [IsDeleted] [bit] NULL,
|
|
-- [isRetire] [bit] NULL,
|
|
-- [AddedBy] [nvarchar](50) NULL,
|
|
-- [AddedOn] [datetime] NULL,
|
|
-- [ModifiedBy] [nvarchar](50) NULL,
|
|
-- [ModifiedOn] [datetime] NULL,
|
|
-- CONSTRAINT [PK__tdAxleLo__3214EC27FFABF186] PRIMARY KEY CLUSTERED
|
|
--(
|
|
-- [ID] 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
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadSurvey] ADD CONSTRAINT [DF__tdAxleLoa__Statu__2BC97F7C] DEFAULT ((1)) FOR [Status]
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadSurvey] ADD CONSTRAINT [DF__tdAxleLoa__IsDel__2CBDA3B5] DEFAULT ((0)) FOR [IsDeleted]
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadSurvey] ADD CONSTRAINT [DF__tdAxleLoa__isRet__2AD55B43] DEFAULT ((0)) FOR [isRetire]
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadSurvey] WITH NOCHECK ADD CONSTRAINT [FK_tdAxleLoadSurvey_tdTrafficStation] FOREIGN KEY([StationID])
|
|
--REFERENCES [TMS].[tdTrafficStation] ([ID])
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadSurvey] CHECK CONSTRAINT [FK_tdAxleLoadSurvey_tdTrafficStation]
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadSurvey] WITH CHECK ADD CONSTRAINT [FK_tdAxleLoadSurvey_tlAxleConfiguration] FOREIGN KEY([AxleConfigID])
|
|
--REFERENCES [TMS].[tlAxleConfiguration] ([ID])
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadSurvey] CHECK CONSTRAINT [FK_tdAxleLoadSurvey_tlAxleConfiguration]
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadSurvey] WITH NOCHECK ADD CONSTRAINT [FK_tdAxleLoadSurvey_tlCommodityType] FOREIGN KEY([CommodityID])
|
|
--REFERENCES [TMS].[tlCommodityType] ([ID])
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadSurvey] CHECK CONSTRAINT [FK_tdAxleLoadSurvey_tlCommodityType]
|
|
--GO
|
|
|
|
---- ========================================
|
|
|
|
---- changes in axle load counts table - this table is not needed so hiding the menu
|
|
--select * from admn.tsMenuDefinition where code like 'TMS_MGDT%'
|
|
--update admn.tsMenuDefinition set [status]=0 where id=381
|
|
--drop table tms.tdAxleLoadCounts
|
|
--CREATE TABLE [TMS].[tdAxleLoadCounts]
|
|
--(
|
|
-- [ID] [int] IDENTITY(1,1) primary key NOT NULL,
|
|
-- [SurveyDate] [datetime] NOT NULL,
|
|
-- [StationID] [int] NOT NULL,
|
|
-- [TrfDir] [nvarchar](15) NOT NULL,
|
|
-- [SurveyInterval] [int] default(60) NOT NULL,
|
|
-- [Time] [nvarchar](13) NOT NULL,
|
|
-- [MiniBus] [int] NULL,
|
|
-- [Bus] [int] NULL,
|
|
-- [LGV] [int] NULL,
|
|
-- [Axle2] [int] NULL,
|
|
-- [Axle3] [int] NULL,
|
|
-- [SemiTrailer] [int] NULL,
|
|
-- [TruckTrailer] [int] NULL,
|
|
-- [Coasters] [int] NULL,
|
|
-- [Others] [int] NULL,
|
|
-- [SurveyYear] [nvarchar](10) NOT NULL,
|
|
-- [NetworkYear] [nvarchar](10) NULL,
|
|
-- [isRetire] [bit] NULL,
|
|
-- [Status] [bit] NULL,
|
|
-- [IsDeleted] [bit] NULL,
|
|
-- [AddedBy] [nvarchar](50) NULL,
|
|
-- [AddedOn] [datetime] NULL,
|
|
-- [ModifiedBy] [nvarchar](50) NULL,
|
|
-- [ModifiedOn] [datetime] NULL
|
|
--)
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadCounts] WITH CHECK ADD CONSTRAINT [FK_tdAxleLoadCounts_tdTrafficStation] FOREIGN KEY([StationID])
|
|
--REFERENCES [TMS].[tdTrafficStation] ([ID])
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadCounts] CHECK CONSTRAINT [FK_tdAxleLoadCounts_tdTrafficStation]
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadCounts] WITH CHECK ADD CONSTRAINT [FK_tdAxleLoadCounts_tlTimePeriod] FOREIGN KEY([SurveyInterval], [Time])
|
|
--REFERENCES [TMS].[tlTimePeriod] ([Interval], [Code])
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tdAxleLoadCounts] CHECK CONSTRAINT [FK_tdAxleLoadCounts_tlTimePeriod]
|
|
--GO
|
|
|
|
---- =================================================
|
|
|
|
-- remove traffic station code from seasonal factor lookup table and then use the average of 12 months factors multiply it to ADT and show 2 new columns (Factor and AADT) in all reports where ADT is there
|
|
|
|
-- ================================================
|
|
|
|
-- queing length is just a html page with calculations and then export functionality
|
|
|
|
-- ============================================
|
|
|
|
-- notification to show missing data report in all modules dashboard if possible give filters based on table name and roadcode
|
|
|
|
-- ============================================
|
|
|
|
---- set the language table data
|
|
--select * from admn.tsLanguage order by [type], subtype, Code
|
|
--truncate table admn.tslanguage
|
|
---- insert the latest data from dev (from the excel)
|
|
|
|
---- =====================================================
|
|
|
|
---- set the menu definition table data
|
|
--select max(id) from admn.tsMenuDefinition order by id, parentid, modulemenuid, Sequence
|
|
---- remove identity for ID column then insert the latest data from dev (from the excel) and then re-apply the identity
|
|
|
|
---- =====================================================
|
|
|
|
---- changes in vehclassification lookup table
|
|
---- need PCU values for these veh and formula to generate V/C using this, ESAL can be removed
|
|
--drop table TMS.tlVehClassification
|
|
--create table TMS.tlVehClassification
|
|
--(
|
|
-- [ID] [int] IDENTITY(1,1) NOT NULL,
|
|
-- [Code] [nvarchar](50) NOT NULL,
|
|
-- [Name] [nvarchar](100) NOT NULL,
|
|
-- [Description] [nvarchar](1000) NULL,
|
|
-- [Category] [nvarchar](100) NOT NULL,
|
|
-- [SubCategory] [nvarchar](100) NOT NULL,
|
|
-- [PCU] [float] NULL,
|
|
-- [HDMCode] [nvarchar](10) NULL,
|
|
-- [Status] [bit] NOT NULL,
|
|
-- [IsDeleted] [bit] NOT NULL,
|
|
-- [AddedBy] [nvarchar](50) NULL,
|
|
-- [AddedOn] [datetime] NULL,
|
|
-- [ModifiedBy] [nvarchar](50) NULL,
|
|
-- [ModifiedOn] [datetime] NULL,
|
|
-- [clr] [nvarchar](10) NULL,
|
|
-- [iconpath] [nvarchar](250) NULL,
|
|
--PRIMARY KEY CLUSTERED
|
|
--(
|
|
-- [ID] 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
|
|
|
|
--ALTER TABLE [TMS].[tlVehClassification] ADD DEFAULT ((1)) FOR [Status]
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tlVehClassification] ADD DEFAULT ((0)) FOR [IsDeleted]
|
|
--GO
|
|
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'Cars', N'Car', N'Car', N'Light Motor Vehicles', N'Private', 1, N'8', 1, 0, N'Admin', CAST(N'2022-09-17T18:47:04.000' AS DateTime), N'jagadish', CAST(N'2024-02-27T09:48:23.497' AS DateTime), NULL, NULL)
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'Jeep', N'Jeep', N'Jeep', N'Light Motor Vehicles', N'Private', 0.4, N'1', 1, 0, N'Admin', CAST(N'2022-09-17T18:47:04.000' AS DateTime), N'Admin', CAST(N'2024-02-19T16:43:32.473' AS DateTime), NULL, NULL)
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'MotorCycle', N'TWL', N'Motor Cycles', N'Motor Cycle', N'Private', 0.15, N'2', 1, 0, N'Admin', CAST(N'2022-09-17T18:47:04.000' AS DateTime), NULL, NULL, NULL, NULL)
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'MiniBus', N'Mini Buses (<=18 seats)', N'Mini Buses (<=18 seats)', N'Heavy Vehicles', N'Public', 3, N'9', 1, 0, N'srk', CAST(N'2022-10-30T16:55:35.937' AS DateTime), NULL, NULL, NULL, NULL)
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'Coasters', N'Coasters (< = 29 seats)', N' Coasters (< = 29 seats)', N'Heavy Vehicles', N'Public', 3, N'0', 1, 0, N'srk', CAST(N'2022-10-30T16:56:39.653' AS DateTime), N'harinathv', CAST(N'2024-02-16T17:25:17.693' AS DateTime), NULL, NULL)
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'BUS', N'Buses (> = 30 seats)', N'Buses (> = 30 seats)', N'Heavy Vehicles', N'Public', 3, N'3', 1, 0, N'srk', CAST(N'2022-10-30T16:57:03.390' AS DateTime), N'Admin', CAST(N'2024-03-19T14:01:46.993' AS DateTime), NULL, NULL)
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'LGV', N'Light goods vehicles: Dynas, Daihatsu (>3.5T)', N'Light goods vehicles: Dynas, Daihatsu (>3.5T)', N'Light Goods Vehicles', N'Commercial', 0.45, N'7', 1, 0, N'srk', CAST(N'2022-10-30T16:58:10.830' AS DateTime), NULL, NULL, NULL, NULL)
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'2Axle', N'Medium Trucks 2-Axle (>3.5T)', N' Medium Trucks 2-Axle (>3.5T)', N'Heavy Goods Vehicles', N'Heavy & Freight', 1.5, N'6', 1, 0, N'srk', CAST(N'2022-10-30T16:58:47.523' AS DateTime), NULL, NULL, NULL, NULL)
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'3Axle', N'Trucks 3-Axle', N'Trucks 3-Axle', N'Heavy Goods Vehicles', N'Heavy & Freight', 2, N'5', 1, 0, N'srk', CAST(N'2022-10-30T17:01:55.820' AS DateTime), N'ravig', CAST(N'2024-03-13T11:47:07.017' AS DateTime), NULL, NULL)
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'SemiTrailer', N'Semi Trailer', N'Semi Trailer', N'Heavy Goods Vehicles', N'Heavy & Freight', 2, N'4', 1, 0, N'srk', CAST(N'2022-10-30T17:02:14.377' AS DateTime), NULL, NULL, NULL, NULL)
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'TruckTrailer', N'Trucks Trailer (other Trucks)', N'Trucks Trailer (other Trucks)', N'Heavy Goods Vehicles', N'Heavy & Freight', 2.5, N'4', 1, 0, N'srk', CAST(N'2022-10-30T17:02:46.633' AS DateTime), NULL, NULL, NULL, NULL)
|
|
--INSERT [TMS].[tlVehClassification] ([Code], [Name], [Description], [Category], [SubCategory], [PCU], [HDMCode], [Status], [IsDeleted], [AddedBy], [AddedOn], [ModifiedBy], [ModifiedOn], [clr], [iconpath]) VALUES (N'Others', N'Others (Specify)', N'Others (Specify)', N'Heavy Goods Vehicles', N'Heavy & Freight', 3, N'4', 1, 0, N'srk', CAST(N'2022-10-30T17:03:10.687' AS DateTime), N'ravig', CAST(N'2024-03-14T15:00:00.183' AS DateTime), NULL, NULL)
|
|
|
|
---- ===========================================
|
|
|
|
---- changes in seasonal factors lookup table
|
|
---- remove stationid column and change caption of trafficyear to survey year
|
|
--select * from TMS.tlSeasonalFac
|
|
--drop table TMS.tlSeasonalFac
|
|
--CREATE TABLE TMS.tlSeasonalFac
|
|
--(
|
|
-- [ID] [int] IDENTITY(1,1) NOT NULL,
|
|
-- [TrafficYear] [int] NOT NULL,
|
|
-- [January] [float] NOT NULL,
|
|
-- [February] [float] NOT NULL,
|
|
-- [March] [float] NOT NULL,
|
|
-- [April] [float] NOT NULL,
|
|
-- [May] [float] NOT NULL,
|
|
-- [June] [float] NOT NULL,
|
|
-- [July] [float] NOT NULL,
|
|
-- [August] [float] NOT NULL,
|
|
-- [September] [float] NOT NULL,
|
|
-- [October] [float] NOT NULL,
|
|
-- [November] [float] NOT NULL,
|
|
-- [December] [float] NOT NULL,
|
|
-- [Status] [bit] NOT NULL,
|
|
-- [IsDeleted] [bit] NOT NULL,
|
|
-- [AddedBy] [nvarchar](50) NULL,
|
|
-- [AddedOn] [datetime] NULL,
|
|
-- [ModifiedBy] [nvarchar](50) NULL,
|
|
-- [ModifiedOn] [datetime] NULL,
|
|
-- CONSTRAINT [PK__tlSeason__3214EC27D7D3ABCA] PRIMARY KEY CLUSTERED
|
|
--(
|
|
-- [ID] 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
|
|
|
|
--ALTER TABLE [TMS].[tlSeasonalFac] ADD CONSTRAINT [DF__tlSeasona__Statu__05A3D694] DEFAULT ((1)) FOR [Status]
|
|
--GO
|
|
|
|
--ALTER TABLE [TMS].[tlSeasonalFac] ADD CONSTRAINT [DF__tlSeasona__IsDel__0697FACD] DEFAULT ((0)) FOR [IsDeleted]
|
|
--GO
|
|
|
|
---- ================================
|
|
|
|
---- changes in veh capacity range lookup table
|
|
---- this table is used for V/C ratio, need these values
|
|
--select * from tms.tlVehCapacityRange
|
|
|
|
---- ========================================
|
|
|
|
---- changes in tms.tdDirectLinkAADT table
|
|
---- as of now not needed so hide the menu
|
|
--select * from tms.tdDirectLinkAADT
|
|
|
|
---- ========================================
|
|
|
|
---- changes in tms.tlVehRegistrations table
|
|
---- as of now not needed so hide the menu
|
|
--select * from tms.tlVehRegistrations
|
|
|
|
---- =========================================
|
|
|
|
---- changes in tms.tlTareWeightValues table
|
|
---- this table is not needed so delete it
|
|
--drop table tms.tlTareWeightValues
|
|
|
|
-- =========================================
|
|
|
|
-- list of tables in TMS
|
|
-- tms.tlVehClassification
|
|
-- tms.tlSeasonalFac
|
|
-- tms.tlTimePeriod
|
|
-- tms.tlStationType
|
|
-- tms.tlTrafficCategory
|
|
-- tms.tlVehCapacityRange
|
|
|
|
-- tms.tdTrafficStation
|
|
-- tms.tdTrafficVolumeCount
|
|
-- tms.tdTrafficDistribution
|
|
-- tms.tdDirectLinkAADT -- not using
|
|
|
|
-- tms.tlVehRegistrations -- not using
|
|
-- tms.tlLegalMaxLoad
|
|
-- tms.tlAxleConfiguration
|
|
-- tmstlTareWeightValues -- delete this table
|
|
|
|
-- tms.tdAxleLoadCounts -- not using
|
|
-- tms.tdAxleLoadSurvey
|
|
|
|
-- TMS.oTisAxleLoadSumm
|
|
-- TMS.oTisRoadLife -- not using
|
|
-- TMS.oTisVdf
|
|
|
|
-- tms.tlGrowthRates
|
|
-- tms.toGrowthrates
|
|
-- tms.toProjectedTGR
|
|
|
|
-- =======================================
|