Hi thereI have a FileConsumptionDetail table which has [DataTime] every 30 minutes. Also, I have a schedule table with has range time and with certain condition.Now I would like to query against these 2 tables what is the best approach to handle to do this due the process load is around 20,000 records. The end of result is sum of the Value1 of FileConsumptionDetail with the relevat schedule. I can probably using cursor and loop the 20,000 records of the data and check one by one whether that value is applied on that schedule table. Overkill .. I guess?Here's the schema of the tables:CREATE TABLE [dbo].[tnd_FileConsumptionDetail]( [FileConsumptionDetailID] [bigint] IDENTITY(1,1) NOT NULL, [FileConsumptionID] [bigint] NULL, [Time] [datetime] NULL, [Value1] [decimal](18, 4) NULL CONSTRAINT [DF_tnd_ConsumptionDetail_Value1] DEFAULT ((0)), [Value2] [decimal](18, 4) NULL CONSTRAINT [DF_tnd_ConsumptionDetail_Value2] DEFAULT ((0)), [Value3] [decimal](18, 4) NULL CONSTRAINT [DF_tnd_ConsumptionDetail_Value3] DEFAULT ((0)), [Value4] [decimal](18, 4) NULL CONSTRAINT [DF_tnd_ConsumptionDetail_Value4] DEFAULT ((0)), [Value5] [decimal](18, 4) NULL CONSTRAINT [DF_tnd_ConsumptionDetail_Value5] DEFAULT ((0)), [DateCreated] [datetime] NULL CONSTRAINT [DF_UsageDetail_DateCreated] DEFAULT (getdate()), [LastDateModified] [datetime] NULL CONSTRAINT [DF_UsageDetail_LastDateModified] DEFAULT (getdate()), [IsActive] [bit] NULL
CREATE TABLE [dbo].[glb_Schedules]( [ScheduleID] [bigint] IDENTITY(1,1) NOT NULL, [ScheduleGroupID] [bigint] NULL, [ScheduleTypeID] [int] NULL, [Name] [varchar](100) COLLATE Latin1_General_CI_AS NULL, [StartTime] [smalldatetime] NULL, [EndTime] [smalldatetime] NULL, [Length] [int] NULL, [IsMonday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsMonday] DEFAULT ((0)), [IsTuesday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsTuesday] DEFAULT ((0)), [IsWednesday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsWednesday] DEFAULT ((0)), [IsThursday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsThursday] DEFAULT ((0)), [IsFriday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsFirday] DEFAULT ((0)), [IsSaturday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsSaturday] DEFAULT ((0)), [IsSunday] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsSunday] DEFAULT ((0)), [IsJanuary] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsJanuary] DEFAULT ((0)), [IsFebruary] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsFebruary] DEFAULT ((0)), [IsMarch] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsMarch] DEFAULT ((0)), [IsApril] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsApril] DEFAULT ((0)), [IsMay] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsMay] DEFAULT ((0)), [IsJune] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsJune] DEFAULT ((0)), [IsJuly] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsJuly] DEFAULT ((0)), [IsAugust] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsAugust] DEFAULT ((0)), [IsSeptember] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsSeptember] DEFAULT ((0)), [IsOctober] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsOctober] DEFAULT ((0)), [IsNovember] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsNovember] DEFAULT ((0)), [IsDecember] [bit] NULL CONSTRAINT [DF_glb_Schedules_IsDecember] DEFAULT ((0)), [DateCreated] [datetime] NULL CONSTRAINT [DF_GLB_ScheduleDetails_DateCreated] DEFAULT (getdate()), [LastDateModified] [datetime] NULL CONSTRAINT [DF_GLB_ScheduleDetails_LastDateModified] DEFAULT (getdate()), [IsActive] [bit] NULL CONSTRAINT [DF_GLB_ScheduleDetails_IsActive] DEFAULT ((1))
IsMonday - IsSunday is basically a flag for each day if it's a daily type of schedule.IsJan - isDec is a flag for each month if it's a monthly type of schedule.Regarding 2nd table design, i am not sure this is a best approach though and I am happy to change if it's easy to query. Hope this clear and I am appreciated your comment.Thanks
Date range query
Posted - 2008-04-20 : 04:45:12
