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

and ((b.string_value IS NULL) OR (b.string_value not in (select ExaminationID from lab.dbo.tbImgInfo where (ExaminationID IS NOT NULL))))

and not exists (select 1 from lab.dbo.tbImgInfo where (ExaminationID = b.string_value))

where (a.image_id>=11111)

where (a.image_id>=@image_id)

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>=57400) 
and not exists (select 1 from lab.dbo.tbImgInfo(nolock) where (seq=a.image_id))
and not exists (select 1 from lab.dbo.tbImgInfo(nolock) where (ExaminationID = b.string_value))
order by a.image_id

  |--Compute Scalar(DEFINE:([Expr1014]=[lab].[dbo].[RmDateToDT]([Romexis_db].[dbo].[RIM_Image_Info].[updated_date] as [a].[updated_date],[Romexis_db].[dbo].[RIM_Image_Info].[updated_time] as [a].[updated_time])))
       |--Top(TOP EXPRESSION:((30)))
            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([a].[patient_id]))
                 |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([b].[string_value]))
                 |    |--Nested Loops(Left Outer Join, WHERE:([Romexis_db].[dbo].[RIM_Image_Attrib].[image_id] as [b].[image_id]=[Romexis_db].[dbo].[RIM_Image_Info].[image_id] as [a].[image_id]))
                 |    |    |--Nested Loops(Left Anti Semi Join, WHERE:([lab].[dbo].[tbImgInfo].[seq]=[Romexis_db].[dbo].[RIM_Image_Info].[image_id] as [a].[image_id]))
                 |    |    |    |--Clustered Index Seek(OBJECT:([Romexis_db].[dbo].[RIM_Image_Info].[PK_RIM_IMAGE_INFO] AS [a]), SEEK:([a].[image_id] >= (57400)) ORDERED FORWARD)
                 |    |    |    |--Clustered Index Seek(OBJECT:([lab].[dbo].[tbImgInfo].[PK_ImgInfo]), SEEK:([lab].[dbo].[tbImgInfo].[seq] >= (57400)) ORDERED FORWARD)
                 |    |    |--Clustered Index Scan(OBJECT:([Romexis_db].[dbo].[RIM_Image_Attrib].[PK_RIM_IMAGE_ATTRIB] AS [b]), WHERE:([Romexis_db].[dbo].[RIM_Image_Attrib].[attrib_type] as [b].[attrib_type]=(171) AND [Romexis_db].[dbo].[RIM_Image_Attrib].[image_id] as [b].[image_id]>=(57400)))
                 |    |--Top(TOP EXPRESSION:((1)))
                 |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1017], [Expr1018], [Expr1016]))
                 |              |--Compute Scalar(DEFINE:(([Expr1017],[Expr1018],[Expr1016])=GetRangeThroughConvert([Romexis_db].[dbo].[RIM_Image_Attrib].[string_value] as [b].[string_value],[Romexis_db].[dbo].[RIM_Image_Attrib].[string_value] as [b].[string_value],(62))))
                 |              |    |--Constant Scan
                 |              |--Index Seek(OBJECT:([lab].[dbo].[tbImgInfo].[ix_ExaminationID]), SEEK:([lab].[dbo].[tbImgInfo].[ExaminationID] > [Expr1017] AND [lab].[dbo].[tbImgInfo].[ExaminationID] < [Expr1018]),  WHERE:(CONVERT_IMPLICIT(nvarchar(128),[lab].[dbo].[tbImgInfo].[ExaminationID],0)=[Romexis_db].[dbo].[RIM_Image_Attrib].[string_value] as [b].[string_value]) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([Romexis_db].[dbo].[RRM_Person].[PK_RRM_PERSON] AS [c]), SEEK:([c].[person_id]=[Romexis_db].[dbo].[RIM_Image_Info].[patient_id] as [a].[patient_id]) ORDERED FORWARD)

ALTER FUNCTION [dbo].[RmDateToDT] (@updated_date int,@updated_time int)
RETURNS datetime
AS
BEGIN	
	RETURN CONVERT(datetime,
						(right('0000'+ CAST((@updated_date / 10000) as varchar),4)+'-'+
						right('00'+ CAST((@updated_date / 100) as varchar),2)+'-'+
						right('00'+ CAST(@updated_date as varchar),2)+'T'+
						right('00'+ CAST((@updated_time / 10000000) as varchar),2)+':'+
						right('00'+ CAST((@updated_time / 100000) as varchar),2)+':'+
						right('00'+ CAST((@updated_time / 1000) as varchar),2)+'.000'),126);
END