I'm missing something which is probably very simple but tearing my hair out all the same, so hope some body can help :-) What i'm trying to achieve is the number of times something has been produced in a day and the average duration per day it takes. Now i'm happy enough with the average duration but the numbers produced i'm really struggling with and i've created a simplified example.
When something is produced it's in a couple of batches but the total produced is for the entire product so i don't want to double count them. So in my example below on the 10th Product1 was produced at 08:00 and 09:00 in 2 batches and the number produced was 10 at 0800 and 50 at 0900 so for product1 on the 10th 60 were produced in total. If I try and sum the number produced i end up with 120 - so i want to group in a way that it sums the number produced based on date and product. Any help welcome!
create table randomtable (
insert into randomtable values
select datepart(dd,dt) as date,pname,sum(produced)as produced,cast(1.0 sum(produced duration_secs) / sum(produced)/1000 as decimal(10,5)) as avg_duration_secs from randomtable
group by datepart(dd,dt),pname
order by 1