Calculating the time duration for each event

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculating the time duration for each event

Author

Topic

samneedshelp
Starting Member

9 Posts

Posted - 2008-06-06 : 12:32:48

Hi all,

Thanks for setting up such a great site and forum.

Here is my problem:

I have a table like the following in SQL Server 2005:


order | taskid | main_person | temp_person | start_assign_date
  1   |   3    |    John     |    John     |  2008-01-01 10:20:22 
  2   |   3    |    John     |    Joe      |  2008-02-05 15:20:22 
  3   |   3    |    John     |    John     |  2008-02-07 20:25:20 
  4   |   6    |    Joe      |    Joe      |  2008-01-01 10:20:22 
  5   |   6    |    Joe      |    Mike     |  2008-02-01 10:20:22 
  6   |   10   |    Doug     |    Doug     |  2008-01-01 10:20:22 
  7   |   7    |    Russ     |    Russ     |  2008-02-01 11:20:22
  8   |   7    |    Russ     |    Mike     |  2008-02-08 12:20:22  
  9   |   7    |    Russ     |    John     |  2008-02-10 20:05:12

It was made to record who was in charge of a specific task at a specific time. Each task has its own main responsible person and some substitutes for that person as Temporary Persons (who did the task while main person was away). The Main Person's name is in the temp_person column when he is doing the task by himself.

I'd like to generate a report that shows:
- in a specific time period
- which persons were in charge of a specific task and
- for how long

Something like this:


From 2008-##-## to 2008-##-##
Task 3  - John  - 15 days
Task 3  - Joe   - 5 days
Task 6  - Joe   - 18 days
Task 6  - Mike  - 2 days
Task 10 - Doug  - 20 days

I have some ideas to do that when there are both start and end dates for every record but I couldn't find a way to use the next assignment start date for each task, as the end date for its previous record (in that task group) to calculate the duration for that record.

I can group the tasks and users and put them in the chronological order but I can't indicate the next start date as the end date for the previous record (in specific task group) to use the date difference functions.

Any hint or comment would be appreciated.

Thanks
Sami

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 12:37:49

How did you determine the period of task? You've only a start assign date and have only a singlerecord for task 3 in 1/2008 for John. Didnt understand how you got 15 days?

samneedshelp
Starting Member

9 Posts

Posted - 2008-06-06 : 12:58:08

quote:
Originally posted by visakh16

How did you determine the period of task? You've only a start assign date and have only a singlerecord for task 3 in 1/2008 for John. Didnt understand how you got 15 days?

Hi visakh16,
Thank you for your prompt response. Sorry for that. The report sample was just a sample and had nothing to do with the sample data. I should correct it to prevent confusion again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 13:03:50

quote:
Originally posted by samneedshelp

quote:
Originally posted by visakh16

How did you determine the period of task? You've only a start assign date and have only a singlerecord for task 3 in 1/2008 for John. Didnt understand how you got 15 days?

Hi visakh16,
Thank you for your prompt response. Sorry for that. The report sample was just a sample and had nothing to do with the sample data. I should correct it to prevent confusion again.

ok thats fine. Still i didnt understand how the report displays number days? Is it just based on start and end dates passed from user? will we be comparing the start assign date with start nad end dates to calculate the number of days of task?

samneedshelp
Starting Member

9 Posts

Posted - 2008-06-06 : 14:24:53

quote:

ok thats fine. Still i didnt understand how the report displays number days? Is it just based on start and end dates passed from user? will we be comparing the start assign date with start nad end dates to calculate the number of days of task?

I am trying to do it in this way:

Report start date 2008-01-01 and end date 2008-03-01
Task 3 was assigned to John on 2008-01-01
Report start date 2008-01-01
=> John has 0 days for task 3 (2008-01-01 - 2008-01-01)

THEN
Task 3 was assigned to John on 2008-01-01
Task 3 was assigned to Joe on 2008-02-05
As a result 2008-02-15 is the end date for John on Task 3
=> John has ~35 days for task 3 so far (2008-02-05 - 2008-01-01)

THEN
Task 3 was assigned to John (again) on 2008-02-07
This would be end date for Joe (on task 3 started on 2008-02-05)
=> Joe has 2 days for task 3 so far (2008-02-07 - 2008-02-05)

THEN
Since Task 3 was assigned to John on 2008-02-07 and
the report end date is 2008-03-01
=> John has another ~23 days for Task 3 by the end of report period

Result
John had ~58 days for task 3 during Report start date 2008-01-01 and end date 2008-03-01
Joe had ~2 days for task 3 during Report start date 2008-01-01 and end date 2008-03-01

Does this make sense?

Sami

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources