Prev: Simple query help - selecting row with highest total (for each name)
Next: When are projections done?
From: dvestal on 26 Feb 2010 11:08
Suppose you were looking at widget manufacturing data, and your DB
looked like this:

tblWidgetsProduced
dtStartTime
dtEndTime
iWidgetsProduced

Suppose you wanted to look at widgets produced per hour....but there
was no guarantee that dtStartTime and dtEndTime fell on hour
boundaries, so you might have the split an entry across multiple hours
by allocating iWidgetsProduced proportionately across the spanned
hours. Would that be possible to do in SQL?
From: Gert-Jan Strik on 26 Feb 2010 17:44
"dvestal(a)gmail.com" wrote:
>
> Suppose you were looking at widget manufacturing data, and your DB
> looked like this:
>
> tblWidgetsProduced
> dtStartTime
> dtEndTime
> iWidgetsProduced
>
> Suppose you wanted to look at widgets produced per hour....but there
> was no guarantee that dtStartTime and dtEndTime fell on hour
> boundaries, so you might have the split an entry across multiple hours
> by allocating iWidgetsProduced proportionately across the spanned
> hours. Would that be possible to do in SQL?

Please tell your trainer / professor to change the training material to
use proper table and column names. It is considered a bad practice to
prefix column names with a data type indicator. Hungarian notation for
table names is even worse.

To get you start: you can use DATEDIFF with the HOUR parameter to count
the number of hour boundaries between two datetimes. If you also have to
take the minutes (or seconds) count into consideration, then you might
need a different parameter.

Also, be aware of integer division. Make sure to cast the number of
Widgets Produced to a decimal if you expect the average per hour to have
a fraction like for example 0.5.

Good luck,
Gert-Jan
 | 
Pages: 1
Prev: Simple query help - selecting row with highest total (for each name)
Next: When are projections done?