SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Date range query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts

Posted - 04/20/2008 :  04:45:12  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
Hi there

I 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

Edited by - dewacorp.alliances on 04/20/2008 04:54:41

PeterNeo
Constraint Violating Yak Guru

356 Posts

Posted - 04/21/2008 :  03:20:17  Show Profile  Reply with Quote
can u post some sample data and desired output as u r problem is not clear.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000