June 17, 2016 at 9:27 am
Hi everyone
I have a table in SQL called dbo.ACTIVITIES which records the time it takes to complete a certain activity.
The time taken to perform the activity is recorded in the ACTIVITY_TIME_TAKEN field. Each activity is recoded in the ACTIVITY field (Dance, Music etc) with a corresponding date (ACTIVITY_DATE)
My problem is that we have activities booked in the future and I would like to calculate how long it is going to take to perform these activities based on the results in the past. So if we had 5 dance classes in the past and they each took 20 mins then the average for the future classes would be 20 mins in length ie 100 / 5. How would I go about performing this in an SQL query? If possible I would also like to make it a little more complex by bringing in the CATEGORY_AGE ie what would be the average activity by age in the past..
Hope this makes sense.
Thanks in advance.
BO
June 17, 2016 at 9:37 am
ByronOne (6/17/2016)
Hi everyoneI have a table in SQL called dbo.ACTIVITIES which records the time it takes to complete a certain activity.
The time taken to perform the activity is recorded in the ACTIVITY_TIME_TAKEN field. Each activity is recoded in the ACTIVITY field (Dance, Music etc) with a corresponding date (ACTIVITY_DATE)
My problem is that we have activities booked in the future and I would like to calculate how long it is going to take to perform these activities based on the results in the past. So if we had 5 dance classes in the past and they each took 20 mins then the average for the future classes would be 20 mins in length ie 100 / 5. How would I go about performing this in an SQL query? If possible I would also like to make it a little more complex by bringing in the CATEGORY_AGE ie what would be the average activity by age in the past..
Hope this makes sense.
Thanks in advance.
BO
would be useful to provide some more details to help us.
please see this article : https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/%5B/url%5D
if you can post necessary set up scripts as per this article and expected results based on your sample data...am sure someone will help
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 17, 2016 at 10:35 am
This code may give you some ideas. It looks at the most recent <nn> identical activities and averages them to get an estimate:
declare @num_of_most_recent_activities_to_use_to_estimate int
set @num_of_most_recent_activities_to_use_to_estimate = 5
create table #activities_to_estimate (
ACTIVITY varchar(100) primary key
)
insert into #activities_to_estimate values('DANCE')
select derived.ACTIVITY, CAST(SUM(derived.ACTIVITY_TIME_TAKEN) * 1.0 / COUNT(derived.ACTIVITY) AS int) AS ACTIVITY_ESTIMATED_TIME
from (
select *, row_number() over(partition by a.ACTIVITY order by ACTIVITY_DATE DESC) AS row_num
from dbo.ACTIVITIES a
inner join #activities_to_estimate ate on ate.ACTIVITY = a.ACTIVITY
) as derived
where row_num <= @num_of_most_recent_activities_to_use_to_estimate
group by ACTIVITY --Edit: added.
order by ACTIVITY
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 17, 2016 at 10:49 am
Thanks Scott - that looks great.
(Un)fortunately I have left the office for the day but will test this first thing Monday.
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply