---- 1. alxe congfiguration table defeinition changed --delete from tms.tlAxleConfiguration --DBCC CHECKIDENT ('tms.tlAxleConfiguration', RESEED, 0) --drop table tms.tlAxleConfiguration --CREATE TABLE [TMS].[tlAxleConfiguration] --( -- [ID] [int] primary key IDENTITY(1,1) NOT NULL, -- [Code] [int] NOT NULL, -- [NoofTyres] [int] NOT NULL, -- [NoofLoads] [int] NULL, -- [FormulaToBeUsed] [nvarchar](max) NULL, -- [Status] [bit] NULL, -- [IsDeleted] [bit] NULL, -- [AddedBy] [nvarchar](50) NULL, -- [AddedOn] [datetime] NULL, -- [ModifiedBy] [nvarchar](50) NULL, -- [ModifiedOn] [datetime] NULL, -- [clr] [nvarchar](10) NULL, -- [iconpath] [nvarchar](250) NULL --) ---- ===================================================== ---- 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 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] --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](15) 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