USE [HelpDesk01_Dat]
GO

/*** Repairs ***/		
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Chargeback_to_Customer' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [Chargeback_to_Customer] [bit] NOT NULL DEFAULT 0;
END

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Parts_Repaired' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [Parts_Repaired] nvarchar(MAX) NULL;
END

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Cost' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [Cost] [money] NULL;
END
	
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Subcategory' )
	ALTER TABLE [dbo].[tblDts] ADD [Subcategory] [int] NULL;
	
GO

/****** Show Sample Escalation Level ******/

UPDATE [tblDts]
	SET nEscalationLevel=1
	WHERE nID IN (8,12, 13);

UPDATE [tblDts]
	SET nEscalationLevel=2
	WHERE nID IN (1,11,14);

UPDATE [tblDts]
	SET nEscalationLevel=4
	WHERE nID IN (7, 10, 16);

UPDATE [tblDts]
	SET nEscalationLevel=5
	WHERE nID IN (3,4);

GO











