select top 30 a.image_id,a.patient_id,a.updated_date,a.updated_time,lab.dbo.RmDateToDT(a.updated_date,a.updated_time) as upd_date,a.image_date,
        a.image_time,a.image_type,a.image_subtype,a.image_format,b.string_value,c.external_id 
        from Romexis_db.dbo.RIM_Image_Info a(nolock) 
        left join Romexis_db.dbo.RIM_Image_Attrib b(nolock) on (b.image_id = a.image_id) and (b.attrib_type=171) 
        left join Romexis_db.dbo.RRM_Person c(nolock)  on (c.person_id = a.patient_id) 
        where (a.image_id>=11111) and (a.image_id not in (select seq from lab.dbo.tbImgInfo where (seq=a.image_id))) 
        and ((b.string_value IS NULL) OR (b.string_value not in (select ExaminationID from lab.dbo.tbImgInfo where (ExaminationID IS NOT NULL))))
        order by a.image_id

CREATE TABLE [dbo].[RIM_Image_Info](
	[image_id] [int] IDENTITY(1,1) NOT NULL,
	[history_id] [int] NOT NULL,
	[patient_id] [int] NOT NULL,
	[status] [int] NOT NULL,
	[updated_by] [int] NOT NULL,
	[updated_date] [int] NOT NULL,
	[updated_time] [int] NOT NULL,
	[doctor_id] [int] NULL,
	[image_size] [int] NOT NULL,
	[image_date] [int] NOT NULL,
	[image_time] [int] NOT NULL,
	[image_source] [int] NOT NULL,
	[image_type] [int] NOT NULL,
	[image_subtype] [int] NOT NULL,
	[image_format] [nvarchar](4) NULL,
	[bit_depth] [int] NOT NULL,
	[pixel_size] [int] NULL,
	[rotation_angle] [int] NULL,
	[is_mirrored] [int] NULL,
	[tooth_mask] [int] NULL,
	[tooth_mask_child] [int] NULL,
	[operator_id] [int] NULL,
 CONSTRAINT [PK_RIM_IMAGE_INFO] PRIMARY KEY CLUSTERED 
(
	[image_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 [dbo].[RIM_Image_Info]  WITH CHECK ADD  CONSTRAINT [FK_RIM_IMAGE_INFO_1] FOREIGN KEY([patient_id])
REFERENCES [dbo].[RRM_Person] ([person_id])
GO

ALTER TABLE [dbo].[RIM_Image_Info] CHECK CONSTRAINT [FK_RIM_IMAGE_INFO_1]
GO


CREATE TABLE [dbo].[RIM_Image_Attrib](
	[attrib_id] [int] IDENTITY(1,1) NOT NULL,
	[history_id] [int] NOT NULL,
	[image_id] [int] NOT NULL,
	[status] [int] NOT NULL,
	[updated_by] [int] NOT NULL,
	[updated_date] [int] NOT NULL,
	[updated_time] [int] NOT NULL,
	[attrib_type] [int] NOT NULL,
	[int_value] [int] NOT NULL,
	[string_value] [nvarchar](2000) NULL,
	[double_value] [float] NULL,
 CONSTRAINT [PK_RIM_IMAGE_ATTRIB] PRIMARY KEY CLUSTERED 
(
	[attrib_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 [dbo].[RIM_Image_Attrib]  WITH CHECK ADD  CONSTRAINT [FK_RIM_IMAGE_ATTRIB_1] FOREIGN KEY([image_id])
REFERENCES [dbo].[RIM_Image_Info] ([image_id])
GO

ALTER TABLE [dbo].[RIM_Image_Attrib] CHECK CONSTRAINT [FK_RIM_IMAGE_ATTRIB_1]
GO



CREATE TABLE [dbo].[RRM_Person](
	[person_id] [int] IDENTITY(1,1) NOT NULL,
	[history_id] [int] NOT NULL,
	[external_id] [nvarchar](64) NULL,
	[system_id] [nvarchar](32) NULL,
	[other_id] [nvarchar](64) NULL,
	[status] [int] NOT NULL,
	[updated_by] [int] NOT NULL,
	[updated_date] [int] NOT NULL,
	[updated_time] [int] NOT NULL,
	[title] [nvarchar](20) NULL,
	[first_name] [nvarchar](64) NULL,
	[second_name] [nvarchar](64) NULL,
	[third_name] [nvarchar](64) NULL,
	[last_name] [nvarchar](64) NULL,
	[maiden_name] [nvarchar](64) NULL,
	[nickname] [nvarchar](64) NULL,
	[name_suffix] [nvarchar](10) NULL,
	[initials] [nvarchar](10) NULL,
	[date_of_birth] [int] NULL,
	[date_of_death] [int] NULL,
	[gender] [nchar](1) NULL,
	[marital_status] [nchar](1) NULL,
	[nationality] [nchar](3) NULL,
	[language_id] [nchar](2) NULL,
	[occupation] [nvarchar](50) NULL,
 CONSTRAINT [PK_RRM_PERSON] PRIMARY KEY CLUSTERED 
(
	[person_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


/****** Object:  Table [dbo].[tbImgInfo]    Script Date: 05/20/2014 17:54:08 ******/

CREATE TABLE [dbo].[tbImgInfo](
	[seq] [int] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[uploadUrl] [varchar](256) NULL,
	[ExaminationID] [varchar](128) NULL,
	[IsMinicopySended] [bit] NOT NULL,
	[IsPatientSended] [bit] NOT NULL,
	[uploadExaminationID] [uniqueidentifier] NULL,
	[Width] [int] NULL,
	[Height] [int] NULL,
	[SendSizeCount] [int] NOT NULL,
	[C_Type] [int] NULL,
	[C_SubType] [int] NULL,
 CONSTRAINT [PK_ImgInfo] PRIMARY KEY CLUSTERED 
(
	[seq] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbImgInfo] ADD  CONSTRAINT [DF_ImgInfo_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[tbImgInfo] ADD  CONSTRAINT [DF_tbImgInfo_IsMinicopySended]  DEFAULT ((0)) FOR [IsMinicopySended]
GO

ALTER TABLE [dbo].[tbImgInfo] ADD  CONSTRAINT [DF_tbImgInfo_IsPatientSended]  DEFAULT ((0)) FOR [IsPatientSended]
GO

ALTER TABLE [dbo].[tbImgInfo] ADD  CONSTRAINT [DF_tbImgInfo_SizeSendCount]  DEFAULT ((0)) FOR [SendSizeCount]
GO