TMS_Changes.sql - Rwanda RAMS - Redmine

Feature #1 » TMS_Changes.sql

Ravi Shekar Gotimukul, 13/03/2024 03:37 AM

    
    ---- 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

« Previous
1
2
3
…
6
Next »

(1-1/6)

Project

General

Profile

Rwanda RAMS

Feature #1 » TMS_Changes.sql