July 14, 2006 at 2:01 pm
I am looking for some assistance thinking this one through, and/or a possible example.
Each record in a table (TableJob) contains Start Date, StartTime, EndTime, Total, Operator, etc, etc. What I would like to accomplish is create a 24 hour table/recordset by hour (24 records) and identify how many widgets were created per hour for a given period.
For example, Record 1 had a StartDate of 1/1/2006, StartTime of 1:45PM, a EndTime of 2:45PM, and a Total Volume of 200. A caculation would be made to note that 25% of the 200 widgets were created in the ONE o'clock hour, and 75% during the TWO o'clock hour. Therefore, if I had a returned table/recordset, it would assign 50 widgets to 1, and 150 widgets to 2. Ultimately, I would like to pass a date to this SP/Function and get a table/recordset with 24 rows (hours) shows the total widgets per hour. Noting that there would be several records in TableJob for a given day.
Any suggestions, or implemented ideas?
July 14, 2006 at 2:51 pm
What data type are the time columns?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 14, 2006 at 2:59 pm
Thanks for your reply... to answer your question:
[StartDate] [datetime] NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[Operator] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Volume] [int] NULL ,
(So the times have the 1899-12-30 TIME GOES HERE)
July 15, 2006 at 9:38 am
@TableJob table([StartDateTime] [smalldatetime] NOT NULL ,
@TableJob(
[StartDateTime]
[EndDateTime]
[Volume])
substring(name, 1,2)
id % 3 + 32000.0000 + (colid % 1440)/1440.0000 + (colid % 4)/2.0000 + 0.002
abs(id)+1)/((abs(colid*5000))+20))+2000
master.dbo.syscolumns
@date smalldatetime
@hours table (hour int identity (0,1) not null unique clustered, start datetime null, endx datetime null)
@hours(start) select top 24 null from master.dbo.syscolumns
@hours set start = dateadd(hour,hour,@date), endx = dateadd(hour,hour+1,@date)
h.hour
sum(cast(
@hours h
@TableJob t
t.enddatetime >= h.start
t.startdatetime < h.endx
--t.[EndDateTime] is not null and --just to be explicit
t.enddatetime > @date
by
h.hour
by t.operator,h.hour
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 17, 2006 at 8:16 am
Thank you Tim for the code... I will try it out this afternoon, and get back with you. Once again, I appreciate all your help and understanding of this issue.
July 17, 2006 at 8:52 am
Tim,
I ran your code in QA this morning. What I would like the output to accomplish is at this point is 24 rows with the total volume listed by hour (not operator specific at this point). I tried to add an additional where statement to your SQL statement to see if I could limit the return to a particular date, and the recordset was empty.
Eventually, I would like to be able to pass either one date or a date range to the SP/Function... such as fn_Volumebyhour(1/1/2006) or fnVolumebyhour(1/1/2006,1/3/2006) and it return:
vDate Hour Volume
1/1/2006 0 285
1/1/2006 1 125
1/1/2006 2 654
... to hour 23 and if an end date range is provided (second parameter)
1/2/2006 0 123
1/2/2006 1 456
1/2/2006 2 789
I would need this limitation as the main table contains over 500,000 records. The main table is called Main_Job.
The code seems to work quite well, and I just need to see if it can be altered for this type of output. Once again, thank you for reading and responding to this post.
July 18, 2006 at 11:33 am
The code as it stands should limit results to a single day, using the @date variable. You can get rid of operator by removing it from the SELECT list and the GROUP BY and ORDER BY clauses.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply