/********************************************************************************/
/* Create Tables								*/ 
/********************************************************************************/

if exists (select * from dbo.sysobjects where id = object_id(N'[PatientEpisodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [PatientEpisodes]
GO

CREATE TABLE [PatientEpisodes] (
	[EpisodeID] [int] NOT NULL ,
	[PatientID] [int] NOT NULL ,
	[NationalSpecialty] [smallint] NOT NULL ,
	[StartDate] [smalldatetime] NOT NULL ,
	[EndDate] [smalldatetime] NULL ,
	[LengthOfStay] [smallint] NOT NULL ,
	[ResourceCode] [char] (3) NULL ,
	[ResourceLevel] [varchar] (6) NULL ,
	CONSTRAINT [PK_PatientEpisodes] PRIMARY KEY  CLUSTERED 
	(
		[EpisodeID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[LocalSpecialty]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [LocalSpecialty]
GO

CREATE TABLE [LocalSpecialty] (
	[Local] [tinyint] NOT NULL ,
	[National] [smallint] NOT NULL ,
	[Description] [varchar] (100) NULL 
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[ResourceLevel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [ResourceLevel]
GO

CREATE TABLE [ResourceLevel] (
	[LengthOfStay] [smallint] NOT NULL ,
	[NationalSpecialty] [smallint] NOT NULL ,
	[ResourceCode] [char] (3) NOT NULL ,
	[ResourceLevel] [varchar] (6) NOT NULL,
	CONSTRAINT [PK_ResourceLevel] PRIMARY KEY  CLUSTERED 
	([LengthOfStay], [NationalSpecialty])  ON [PRIMARY] 
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[EpisodeLoadLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [EpisodeLoadLog]
GO

CREATE TABLE [EpisodeLoadLog] (
	[EpisodeID] [int] NOT NULL ,
	[Error] [varchar] (100) NOT NULL ,
	[LoadTime] [datetime] NOT NULL CONSTRAINT [DF_EpisodeLoadLog_LoadTime] DEFAULT (CURRENT_TIMESTAMP)
) ON [PRIMARY]
GO

/********************************************************************************/
/* Create Lookup Stored Procedures						*/
/********************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spDTSLookup_MultiDemo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spDTSLookup_MultiDemo]
GO

CREATE PROC spDTSLookup_MultiDemo
    @NationalSpecialty smallint, @LengthOfStay smallint 
AS
  SELECT ResourceLevel.ResourceCode, ResourceLevel.ResourceLevel FROM ResourceLevel
  WHERE NationalSpecialty = @NationalSpecialty
    AND LengthOfStay = (SELECT MIN(LengthOfStay) FROM ResourceLevel WHERE NationalSpecialty = @NationalSpecialty AND LengthOfStay >= @LengthOfStay)
  ORDER BY LengthOfStay
GO
/********************************************************************************/
/* Load Sample Data								*/ 
/********************************************************************************/
INSERT [LocalSpecialty] VALUES(12, 300, 'General Medicine')
INSERT [LocalSpecialty] VALUES(22, 120, 'Trauma & Orthopaedics')

INSERT [ResourceLevel] VALUES(0, 120, 'N/A', 'N/A')
INSERT [ResourceLevel] VALUES(0, 300, 'BFG', 'Low')
INSERT [ResourceLevel] VALUES(5, 120, 'ASD', 'Medium')
INSERT [ResourceLevel] VALUES(5, 300, 'BFG', 'Low')
INSERT [ResourceLevel] VALUES(10, 120, 'GHJ', 'High')
INSERT [ResourceLevel] VALUES(10, 300, 'TYU', 'Medium')
INSERT [ResourceLevel] VALUES(15, 120, 'ZSA', 'High')
INSERT [ResourceLevel] VALUES(15, 300, 'TYU', 'Medium')