Per hour calculations using timestamps and total widgets

Question

Post reply

Per hour calculations using timestamps and total widgets

Sam Jaynes

Mr or Mrs. 500

Points: 586
More actions
July 14, 2006 at 2:01 pm

#118781

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?
"Life without progression is entropy"
Sam Jaynes

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

What data type are the time columns?

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Sam Jaynes Mr or Mrs. 500 Points: 586 More actions · Answer 2

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)

"Life without progression is entropy"
Sam Jaynes

stax68 SSChampion Points: 11711 More actions · Answer 3

/**********************************************************/
/******** SETUP *****************************************/
/**********************************************************/
/*****test data******************/

declare

@TableJob table([StartDateTime] [smalldatetime] NOT NULL ,

[EndDateTime] [smalldatetime] NOT NULL ,

[Operator] [varchar] (3) NULL , [Volume] [int] NULL)

insert

@TableJob(

[Operator]

,

[StartDateTime]

,

[EndDateTime]

,

[Volume])

select

substring(name, 1,2)

,id % 3 + 32000.0000 + (colid % 1440)/1440.0000

,

id % 3 + 32000.0000 + (colid % 1440)/1440.0000 + (colid % 4)/2.0000 + 0.002

,((

abs(id)+1)/((abs(colid*5000))+20))+2000

from

master.dbo.syscolumns

/*****end test data***************/

/******date parameter*************/

declare

@date smalldatetime

select @date = 32002

/******end date parameter*********/

/***create and populate 'hours' table - could use (part of) a permanent table***/

declare

@hours table (hour int identity (0,1) not null unique clustered, start datetime null, endx datetime null)

insert

@hours(start) select top 24 null from master.dbo.syscolumns

update

@hours set start = dateadd(hour,hour,@date), endx = dateadd(hour,hour+1,@date)

/***end create and populate 'hours' table***************************************/

/**********************************************************/

/*********** END SETUP ************************************/

/**********************************************************/

--

/**********************************************************/

/*********** GET HOURLY VOLUMES ************************/

/**********************************************************/

select

t.operator

,

h.hour

--,t.volume,h.start,h.endx,t.startdatetime,t.enddatetime,datediff(hour, t.startdatetime,t.enddatetime)

,

sum(cast(

((case when t.enddatetime >= h.start and t.enddatetime < h.endx --ltrunc
then datepart(minute,enddatetime) else 60.0000 end
-
case when t.startdatetime >= h.start and t.startdatetime < h.endx --rtrunc
then datepart(minute,startdatetime) else 0.0000 end) --minsinhour
/
(datediff(minute,t.startdatetime,t.enddatetime))) --totminutes

as float) --change to appropriate size & prec

*

t.volume) volume

from

@hours h

left

join

@TableJob t

on

t.enddatetime >= h.start

and

t.startdatetime < h.endx

where

--t.[EndDateTime] is not null and --just to be explicit

t.startdatetime < @date + 1

and

t.enddatetime > @date

group

by

t.operator

,

h.hour

--,t.volume,h.start,h.endx,t.startdatetime,t.enddatetime,datediff(hour, t.startdatetime,t.enddatetime)

order

by t.operator,h.hour

/**********************************************************/

/*********END GET HOURLY VOLUMES***************************/

/**********************************************************/

--

/*

--(a version using separate date and time columns

-- you can use indexed calc columns or an indexed view

-- if performance with table indexes only is inadequate

select

@date date

,operator

,h.hour

, cast(

(case when t.enddate = @date and t.endtime > h.start and t.endtime < h.endx

then datepart(minute,endtime) else 60 end -

case when t.startdate = @date and t.starttime >= h.start and t.starttime < h.endx

then datepart(minute,starttime) else 0 end) --minsinhour

/

datediff(minute,startdate+starttime, enddate+endtime) --totminutes

) as float --change to appropriate size & prec

*

volume volume

from @hours h

--left

join TableJob t

on t.enddate+t.endtime >= h.start

and t.startdate+t.starttime < h.endx

where t.startdate < @date + 1

and t.enddate > @date

group by operator

,h.hour

*/

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Sam Jaynes Mr or Mrs. 500 Points: 586 More actions · Answer 4

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.

"Life without progression is entropy"
Sam Jaynes

Sam Jaynes Mr or Mrs. 500 Points: 586 More actions · Answer 5

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.

"Life without progression is entropy"
Sam Jaynes

stax68 SSChampion Points: 11711 More actions · Answer 6

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