Select A.SS_ID,A.SD_ID, A.SD_Info, A.SD_User, A.SD_Date 
from dbo.BC_ShiftSummaryInfo A
Join  dbo.BC_ShiftSummary B
On A.SS_ID = B.SS_ID
Where B.ShiftStartDate > '2012-12-14 00:00:00.000' 
and B.ShiftStartDate <'2012-12-15 00:00:00.00' order by B.ShiftStartDate

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BC_ShiftSummaryInfo](
	[SS_ID] [uniqueidentifier] NOT NULL,
	[SD_ID] [uniqueidentifier] NOT NULL,
	[SD_Info] [ntext] NOT NULL,
	[SD_User] [nvarchar](400) NULL,
	[SD_Date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[BC_ShiftSummaryInfo]  WITH NOCHECK ADD  CONSTRAINT [FK_BC_ShiftSummaryInfo_BC_Display] FOREIGN KEY([SD_ID])
REFERENCES [dbo].[BC_Display] ([SD_ID])
GO

ALTER TABLE [dbo].[BC_ShiftSummaryInfo] CHECK CONSTRAINT [FK_BC_ShiftSummaryInfo_BC_Display]
GO

CREATE TABLE [dbo].[BC_ShiftSummaryInfo](
	[SS_ID] [uniqueidentifier] NOT NULL,
	[SD_ID] [uniqueidentifier] NOT NULL,
	[SD_Info] [ntext] NOT NULL,
	[SD_User] [nvarchar](400) NULL,
	[SD_Date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[BC_ShiftSummaryInfo]  WITH NOCHECK ADD  CONSTRAINT [FK_BC_ShiftSummaryInfo_BC_Display] FOREIGN KEY([SD_ID])
REFERENCES [dbo].[BC_Display] ([SD_ID])
GO

ALTER TABLE [dbo].[BC_ShiftSummaryInfo] CHECK CONSTRAINT [FK_BC_ShiftSummaryInfo_BC_Display]
GO

CREATE TABLE [dbo].[BC_ShiftSummary](
	[SS_ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[SC_Name] [nvarchar](64) NOT NULL,
	[SC_OwnerID] [varchar](50) NOT NULL,
	[CreatedBy] [nvarchar](255) NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[Status] [tinyint] NOT NULL,
	[SubmitDate] [datetime] NULL,
	[SubmittedBy] [nvarchar](255) NULL,
	[ApproveDate] [datetime] NULL,
	[ApprovedBy] [nvarchar](255) NULL,
	[Eq_No] [int] NOT NULL,
	[ShiftID] [nvarchar](16) NOT NULL,
	[ShiftName] [nvarchar](16) NOT NULL,
	[ShiftStartDate] [datetime] NOT NULL,
	[ShiftEndDate] [datetime] NOT NULL,
	[MinTillLocked] [int] NULL,
	[Archive_File_name] [nvarchar](55) NULL,
	[TakeOverDate] [datetime] NULL,
	[TakeOverBy] [nvarchar](255) NULL,
	[TakeOverRole] [nvarchar](255) NULL,
 CONSTRAINT [PK_BC_ShiftSummary] PRIMARY KEY CLUSTERED 
(
	[SS_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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BC_ShiftSummary] ADD  CONSTRAINT [DF_BC_ShiftSummary_SS_ID]  DEFAULT (newid()) FOR [SS_ID]
GO

ALTER TABLE [dbo].[BC_ShiftSummary] ADD  CONSTRAINT [DF_BC_ShiftSummary_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[BC_ShiftSummary] ADD  DEFAULT (NULL) FOR [Archive_File_name]
GO

CREATE TABLE [dbo].[BC_Display](
	[SD_ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[LKP_Type] [varchar](50) NOT NULL,
	[Name] [nvarchar](64) NOT NULL,
	[OwnerID] [varchar](50) NOT NULL,
	[Caption] [nvarchar](255) NULL,
	[Description] [nvarchar](255) NULL,
	[Height] [int] NOT NULL,
	[LKP_Unit] [varchar](50) NOT NULL,
	[Scrolling] [bit] NOT NULL,
	[URL] [ntext] NOT NULL,
	[DDAO_ID] [uniqueidentifier] NULL,
	[RO] [tinyint] NOT NULL,
	[DDAO_Name] [nvarchar](64) NULL,
	[DDAO_DataSetView] [nvarchar](64) NULL,
	[DDAO_TableOperation] [nvarchar](64) NULL,
	[Query_ConnApp] [nvarchar](64) NULL,
	[Query_ConnSource] [nvarchar](64) NULL,
	[Query_ConnKey] [nvarchar](64) NULL,
	[Query] [ntext] NULL,
	[GOTO_WKSP] [varchar](256) NULL,
	[GOTO_WKBK] [varchar](256) NULL,
	[GOTO_WKSH] [varchar](256) NULL,
	[GOTO_URL] [ntext] NULL,
	[TagGroup_Type] [varchar](50) NULL,
 CONSTRAINT [SN_DISPLAY_PK] PRIMARY KEY CLUSTERED 
(
	[SD_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] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BC_Display]  WITH NOCHECK ADD  CONSTRAINT [FK_BC_Display_BC_Owner] FOREIGN KEY([OwnerID])
REFERENCES [dbo].[BC_Owner] ([OwnerID])
GO

ALTER TABLE [dbo].[BC_Display] CHECK CONSTRAINT [FK_BC_Display_BC_Owner]
GO

ALTER TABLE [dbo].[BC_Display] ADD  CONSTRAINT [DF_BC_Display_SD_ID]  DEFAULT (newid()) FOR [SD_ID]
GO