SUM values in a table based on date range in different table

Question

I've been trying to solve this problem on my own for 3 days, so I'd be ecstatic if anyone could help me with this. I want to use a SQL query to do a couple things:

First, I want to calculate the sum of HourlyDefects from the Quality table between each of the production time periods (StartProdTime and EndProdTime) in the Production table. Right now, I keep getting only the HourlyDefects from the first hour in the Production date range instead of the sum of all HourlyDefects within that time period.

Second, sometimes the EndProdTime of one Product and the StartProdTime of the following Product will occur during the middle of an hour (i.e. Product1 has EndProdTime [3/30/2012 1:25:00 PM] and Product2 has StartProdTime [3/30/2012 1:25:00 PM]). In this case, I need the sum of the HourlyDefects for Product1 to include the HourlyDefects from the Quality table for the 1:00:00 PM to 1:59:59 PM hour, but I do not want that same hour to be counted in the sum for Product2 because it would be double counting the HourlyDefects for that hour. In other words, the sum for Product2 should start adding the HourlyDefects from the 2:00:00 PM to 2:59:59 PM hour thru the EndProdTime hour.  The HourlyDefects data is only available in hourly lump sums, otherwise I wouldn't have to do it this way if it were available with more precise start and end times that could be matched exactly with the production start and end times.

Any help is greatly appreciated!

Table Name below is "Quality"      
     StartTime                  EndTime           HourlyDefects
3/30/2012 7:00:00 AM     3/30/2012 7:59:59 AM           12
3/30/2012 8:00:00 AM     3/30/2012 8:59:59 AM           19
3/30/2012 9:00:00 AM     3/30/2012 9:59:59 AM            0
3/30/2012 10:00:00 AM    3/30/2012 10:59:59 AM           6
3/30/2012 11:00:00 AM    3/30/2012 11:59:59 AM           1
3/30/2012 12:00:00 PM    3/30/2012 12:59:59 AM           3
3/30/2012 1:00:00 PM     3/30/2012 1:59:59 PM            4
3/30/2012 2:00:00 PM     3/30/2012 2:59:59 PM            4
3/30/2012 3:00:00 PM     3/30/2012 3:59:59 PM            4
3/30/2012 4:00:00 PM     3/30/2012 4:59:59 PM           10
3/30/2012 5:00:00 PM     3/30/2012 5:59:59 PM           11

Table Name below is "Production"
 Team      Product        StartProdTime             EndProdTime

Team A     Product1    3/30/2012 12:00:00 AM    3/30/2012 8:00:00 AM
Team B     Product1    3/30/2012 8:00:00 AM     3/30/2012 11:00:00 AM
Team B     Product1    3/30/2012 11:00:00 AM    3/30/2012 1:25:00 PM
Team C     Product2    3/30/2012 1:25:00 PM     3/30/2012 5:30:00 PM
Team D     Product3    3/30/2012 5:30:00 PM     3/30/2012 6:00:00 PM

Desired results...

 Team      Product        StartProdTime             EndProdTime       DefectSum

Team A     Product1    3/30/2012 12:00:00 AM    3/30/2012 8:00:00 AM       12 
Team B     Product1    3/30/2012 8:00:00 AM     3/30/2012 11:00:00 AM      25
Team B     Product1    3/30/2012 11:00:00 AM    3/30/2012 1:25:00 PM        8
Team C     Product2    3/30/2012 1:25:00 PM     3/30/2012 5:30:00 PM       29
Team D     Product3    3/30/2012 5:30:00 PM     3/30/2012 6:00:00 PM        0

Answer 1

Try:

select P.*, SUM(Q.HourlyDefects) as DefectsSum

from Production P INNER JOIN Quality Q ON

Q.StartTime >=P.StartProdTime and (Q.EndTime<=P.EndProdTime 
or (datepart(minute,P.EndProdTime) <> 0 and datediff(minute, P.EndProdTime, Q.EndTime) < 60))

GROUP  BY P.Team, P.Product, P.StartProdTime, P.EndProdTime

This is from the top of my head, but hopefully will give you the idea.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

please try this

select P.Team,P.Product,p.startprodtime,p.endprodtime, isnull(sum(Q.HourlyDefects),0) as DefectsSum from Production P left outer JOIN Quality Q ON 
Q.StartTime between DateAdd(Minute, 60 * Round((DateDiff(minute, 0, P.StartProdTime)+29)/60.0, 0), 0)
and Dateadd(ss,-1,DateAdd(Minute, 60 * Round((DateDiff(minute, 0, P.EndProdTime)+29)/60.0, 0), 0))
GROUP BY P.Team, P.Product, P.StartProdTime, P.EndProdTime

here I am rounding off your StartProdTime to next hour. ie 1.29 to 2

also rounding EndProdTime to 1.29 to 1.59.59

Thanks

Johnson

Answer 3

SELECT p.*,SUM(q.HourlyDefects) FROM Production p
LEFT JOIN Quantity q
ON q.StartTime >= p.StartProdTime and q.StartTime < p.EndProdTime
group by p.EndProdTime,p.Product,p.StartProdTime,p.Team

that should be all you need. The defects count against the shift during which they started. Only gotcha here is that between won't work. you need to do a less than because your shift end/start dates overlap.