CREATE VIEW [dbo].[NewView] AS 
SELECT        dbo.view_lab_information_Request.IREQ_PID AS HN, dbo.view_lab_information_Request.IPT_ALT_ID AS [REF ID], dbo.view_lab_information_Request.IREQ_FULLNAME AS FULLNAME, 
                         dbo.view_lab_information_Request.IPT_DOB AS BIRTHDATE, dbo.view_lab_information_Request.IREQ_AGE_Y AS YEAR, dbo.view_lab_information_Request.IREQ_AGE_M AS MONTH, 
                         dbo.view_lab_information_Request.IREQ_AGE_D AS DAY, dbo.view_lab_information_Request.IPT_SEX AS [SEX CODE], dbo.view_lab_information_Request.IPT_SEX_TEXT AS SEX, 
                         dbo.view_lab_information_Request.IREQ_LN AS LN, dbo.view_lab_information_Request.IREQ_OD_NO AS [ORDER NNO], dbo.view_lab_information_Request.IREQ_OD_DT AS [ORDER DATETIME], 
                         dbo.view_lab_information_Request.IREQ_OD_STAFF_CODE AS [ORDER STAFF CODE], dbo.view_lab_information_Request.IREQ_OD_STAFF_NAME AS [ORDER STAFF NAME], 
                         dbo.view_lab_information_Request.IREQ_SRC_CODE AS [SOURCE CODE], dbo.view_lab_information_Request.IREQ_SRC_TEXT AS [SOURCE NAME], dbo.view_lab_information_Request.IREQ_WARD_CODE AS [WARD CODE], 
                         dbo.view_lab_information_Request.IREQ_WARD_TEXT AS [WARD NAME], dbo.view_lab_information_Request.IREQ_PTYPE_CODE AS [PATIENT TYPE CODE], 
                         dbo.view_lab_information_Request.IREQ_PTYPE_TEXT AS [PATIENT TYPE NAME], dbo.view_lab_information_Request.IREQ_DR_CODE AS [DORTOR CODE], 
                         dbo.view_lab_information_Request.IREQ_DR_NAME AS [DORTOR NAME], dbo.view_lab_information_Request.IREQ_DR_DIAG AS [DORTOR DIAG], dbo.view_lab_information_Request.IREQ_BEDNO AS BEDNO, 
                         dbo.view_lab_information_Request.IREQ_AN AS AN, dbo.view_lab_information_Request.IREQ_VN AS VN, dbo.view_lab_information_Request.IREQ_COMMENT AS [ORDER COMMENT], 
                         dbo.view_lab_information_Request.IREQ_PRIORITY AS PRIORITY, dbo.view_lab_information_Request.IREQ_INACTIVE AS [ORDER INACTIVE], dbo.tbl_lab_setup_Test.Test_Group_Code AS [REQ ITEM GROUP CODE], 
                         dbo.view_lab_information_Request_Items.IREQI_GTEXT AS [REQ ITEM GROUP NAME], dbo.view_lab_information_Request_Items.IREQI_GSEQ AS [REQ ITEM GROUP SEQ], 
                         dbo.view_lab_information_Request_Items.IREQI_CODE AS [REQ ITEM CODE], dbo.view_lab_information_Request_Items.IREQI_TEXT AS [REQ ITEM NAME], 
                         dbo.view_lab_information_Request_Items.IREQI_DISPLAY AS [REQ ITEM DISPLAY], dbo.view_lab_information_Request_Items.IREQI_SEQ AS [REQ ITEM SEQ], 
                         dbo.view_lab_information_Request_Items.IREQI_STATE AS [REQ ITEM STATE], dbo.view_lab_information_Request_Items.IREQI_OD_DT AS [REQ ITEM ORDER DATETIME], 
                         dbo.view_lab_information_Request_Items.IREQI_OD_STAFF_CODE AS [REQ ITEM ORDER STAFF CODE], dbo.view_lab_information_Request_Items.IREQI_OD_STAFF_NAME AS [REQ ITEM ORDER STAFF NAME], 
                         dbo.view_lab_information_Request_Items.IREQI_CHK_DT AS [REQ ITEM CHECKIN DATETIME], dbo.view_lab_information_Request_Items.IREQI_CHK_STAFF_CODE AS [REQ ITEM CHECKIN STAFF CODE], 
                         dbo.view_lab_information_Request_Items.IREQI_CHK_STAFF_NAME AS [REQ ITEM CHECKIN STAFF NAME], dbo.tbl_lab_setup_Test_Control.Test_Countable AS [REQ ITEM COUNTABLE], 
                         dbo.tbl_lab_setup_Test.Test_Inactive AS [TEST INACTIVE], dbo.tbl_lab_setup_Specimen_Type.Specimen_Type_Type AS [ISP TEXT], dbo.view_lab_information_Request.IREQ_RUNNING, 
                         dbo.view_lab_information_Request.IREQ_SITE_NAME, dbo.view_lab_information_Request.IREQ_PROJECT_CODE, dbo.view_lab_information_Request.IREQ_SITE_CODE

FROM
	dbo.view_lab_information_Request
INNER JOIN
                         dbo.view_lab_information_Request_Items ON dbo.view_lab_information_Request.IREQ_LN = dbo.view_lab_information_Request_Items.IREQI_LN INNER JOIN
                         dbo.tbl_lab_setup_Test ON dbo.view_lab_information_Request_Items.IREQI_CODE = dbo.tbl_lab_setup_Test.Test_Code INNER JOIN
                         dbo.tbl_lab_setup_Specimen_Type ON dbo.tbl_lab_setup_Test.Specimen_Type_Code = dbo.tbl_lab_setup_Specimen_Type.Specimen_Type_Code RIGHT OUTER JOIN
                         dbo.tbl_lab_setup_Test_Control ON dbo.view_lab_information_Request_Items.IREQI_CODE = dbo.tbl_lab_setup_Test_Control.Test_Code
GO