CREATE TABLE [dbo].[CaseManagement] (
	[CaseManagementID] [int] IDENTITY (1, 1) NOT NULL,
	[CaseID] [int] NOT NULL,
	[cm_date] [datetime] NOT NULL,
	[cm_call_back] [datetime] NULL,
	[cm_notes] [varchar] (255) NULL, 
	CONSTRAINT [PK_CaseManagement] PRIMARY KEY  CLUSTERED 
	([CaseManagementID]))
CREATE TABLE [dbo].[Cases] (
	[CaseID] [int] IDENTITY (1, 1) NOT NULL,
	[PatientID] [int] NOT NULL,
	[case_sub_type] [varchar] (20) NOT NULL,
	[case_first_visit] [datetime] NOT NULL,
	[case_authorized_by] [varchar] (20) NOT NULL,
	[case_occupation] [varchar] (50) NOT NULL,
	[case_job_code] [varchar] (10) NOT NULL,
	CONSTRAINT [PK_Cases] PRIMARY KEY  CLUSTERED 
	([CaseID]))
CREATE TABLE [dbo].[Patients] (
	[PatientID] [int] IDENTITY (1, 1) NOT NULL,
	[id] [varchar] (50) NOT NULL,
	[lname] [varchar] (40) NOT NULL,
	[fname] [varchar] (20) NOT NULL,
	[phone] [char] (12) NOT NULL,
	[address] [varchar] (40) NULL,
	[city] [varchar] (20) NULL,
	[state] [char] (2) NULL,
	[zip] [char] (5) NULL,
	CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED 
	([PatientID]))

SET Identity_Insert [CaseManagement] ON
INSERT INTO [CaseManagement]([CaseManagementID], [CaseID], [cm_date], [cm_call_back], [cm_notes]) Values (1, 1, 'Jul 4 2000 12:00AM', 	 
	'Jul 9 2000 12:00AM', 'Complaints of dizziness.')
INSERT INTO [CaseManagement]([CaseManagementID], [CaseID], [cm_date], [cm_call_back], [cm_notes]) Values (2, 3, 'Jan 13 2001 12:00AM', 						    
	'Jan 18 2001 12:00AM', 'Evaluation of case file, Laceration to R hand. Patient does not appear to be a candidate for case mgmt at 	
	this time.')
INSERT INTO [CaseManagement]([CaseManagementID], [CaseID], [cm_date], [cm_call_back], [cm_notes]) Values (3, 11, 'Apr 21 2001 12:00AM', 		
	'Apr 26 2001 12:00AM', 'I spoke with George Adams from Adams Upholstery Shop regarding the modified duty status of the above named 
	patient. Modified work is available.  BS, RN')
INSERT INTO [CaseManagement]([CaseManagementID], [CaseID], [cm_date], [cm_call_back], [cm_notes]) Values (4, 21, 'Dec 4 2000 12:00AM', 
	'Jan 1 2001 12:00AM', 'Family history of thyroid cancer.')
SET Identity_Insert [CaseManagement] OFF

SET Identity_Insert [Cases] ON
INSERT INTO [Cases]([CaseID], [PatientID], [case_sub_type], [case_first_visit], [case_authorized_by], [case_occupation], 		
	[case_job_code]) Values (1, 1, 'Office', 'Jul  1 2000 12:00AM', 'Mary Smith', 'Warehouse Worker', '247387010')
INSERT INTO [Cases]([CaseID], [PatientID], [case_sub_type], [case_first_visit], [case_authorized_by], [case_occupation], [case_job_code]) 	
	Values (2, 5, 'Office', 'Nov 24 2000 12:00AM', 'George Wilson', 'Administrative', '248137018')
INSERT INTO [Cases]([CaseID], [PatientID], [case_sub_type], [case_first_visit], [case_authorized_by], [case_occupation], [case_job_code]) 
	Values (3, 6, 'Recert', 'Jan  8 2001 12:00AM', 'Sue Allen', 'Truck Driver', '003123987')
INSERT INTO [Cases]([CaseID], [PatientID], [case_sub_type], [case_first_visit], [case_authorized_by], [case_occupation], [case_job_code]) 
	Values (4, 7, 'Recert', 'Jul  5 2001 12:00AM', 'Sue Allen', 'Truck Driver', '708124561')
SET Identity_Insert [Cases] OFF

SET Identity_Insert [Patients] ON
INSERT INTO [Patients]([PatientID], [id], [lname], [fname], [phone], [address], [city], [state], [zip]) Values (1, '172-32-1176', 
	'White', 'Johnson', '408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'CA', '94025')
INSERT INTO [Patients]([PatientID], [id], [lname], [fname], [phone], [address], [city], [state], [zip]) Values (2, '213-46-8915', 
	'Green', 'Marjorie', '415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', '94618')
INSERT INTO [Patients]([PatientID], [id], [lname], [fname], [phone], [address], [city], [state], [zip]) Values (3, '238-95-7766', 
	'Carson', 'Cheryl', '415 548-7723', '589 Darwin Ln.', 'Berkeley', 'CA', '94705')
INSERT INTO [Patients]([PatientID], [id], [lname], [fname], [phone], [address], [city], [state], [zip]) Values (4, '267-41-2394', 
	'OLeary', 'Michael', '408 286-2428', '22 Cleveland Av. #14', 'San Jose', 'CA', '95128')
INSERT INTO [Patients]([PatientID], [id], [lname], [fname], [phone], [address], [city], [state], [zip]) Values (5, '274-80-9391', 
	'Straight', 'Dean', '415 834-2919', '5420 College Av.', 'Oakland', 'CA', '94609')
INSERT INTO [Patients]([PatientID], [id], [lname], [fname], [phone], [address], [city], [state], [zip]) Values (6, '341-22-1782', 
	'Smith', 'Meander', '913 843-0462', '10 Mississippi Dr.', 'Lawrence', 'KS', '66044')
INSERT INTO [Patients]([PatientID], [id], [lname], [fname], [phone], [address], [city], [state], [zip]) Values (7, '409-56-7008', 
	'Bennet', 'Abraham', '415 658-9932', '6223 Bateman St.', 'Berkeley', 'CA', '94705')
SET Identity_Insert [Patients] OFF

CREATE VIEW dbo.view_CaseManagement
AS SELECT 
dbo.Patients.id, dbo.Patients.lname, dbo.Patients.fname, dbo.Patients.PatientID, dbo.CaseManagement.CaseManagementID, dbo.CaseManagement.CaseID, dbo.CaseManagement.cm_date, dbo.CaseManagement.cm_call_back, dbo.CaseManagement.cm_notes
FROM 
dbo.CaseManagement INNER JOIN dbo.Cases ON dbo.CaseManagement.CaseID = dbo.Cases.CaseID 
INNER JOIN dbo.Patients ON dbo.Cases.PatientID = dbo.Patients.PatientID

CREATE VIEW dbo.view_Cases
AS SELECT 
dbo.Patients.id, dbo.Patients.lname, dbo.Patients.fname, dbo.Patients.PatientID, dbo.Cases.CaseID, dbo.Cases.case_sub_type, dbo.Cases.case_first_visit, dbo.Cases.case_authorized_by, dbo.Cases.case_occupation, dbo.Cases.case_job_code
FROM
dbo.Cases INNER JOIN dbo.Patients ON dbo.Cases.PatientID = dbo.Patients.PatientID

CREATE VIEW dbo.view_Patients
AS SELECT 
dbo.Patients.id, dbo.Patients.lname, dbo.Patients.fname, dbo.Patients.PatientID, dbo.Patients.phone, dbo.Patients.address, dbo.Patients.city, dbo.Patients.state, dbo.Patients.zip
FROM dbo.Patients