and goes like that.
i need to calculate the time between first start and first idle time after the first start, then, time between second start and first idle after the second start, and it goes like that and there are too many lines. at the and i need total work time and total idle time.
Can anyone help me with this?
The easiest way would be to read all the times into a dataset and then go through them calculating the individual differences. See http://msdn.microsoft.com/en-us/library/1905yhe2(v=vs.110).aspx[^] for how to get the TimeSpan value between two DateTime objects. I assume that you are storing these values as DateTime types.
thank you for your response, yes i store these values as DateTime types, and also i am aware of TimeSpan. but the fact is, it is easy for just two values, but i have too many values and my real struggle is this, i couldn't create a logic to calculate for all that values.
It is just a matter of reading all the values one by one.
Read the first time value, that is the starting time of work.
Read the next time, that is the ending time of work.
The difference between those two is added to the total work time.
Set the end time as the new start time, that is the starting time of idle.
Read the next time, that is the ending time of idle (and the next starting time of work).
The difference between start and end should be added to idle time.
Set the end time as the new start time (of work).
Repeat the process until all times have been dealt with.
In cases such as this you should forget about programming and coding issues, and think about the logical steps needed to get your answer. Write down those steps and go through them a few times to ensure they make sense. Then when you are happy that your algorithm is correct you can write and test the actual code needed for your application.
Since you didn't post any details about the structure of your table, I had to make up an example. You'll want just the SELECT block; replace @Data with the name of your table, and replace TimeLogged and Action with your column names.
Sum(DateDiff(second, D1.NameOfYourTimeLoggedColumn, D2.NameOfYourTimeLoggedColumn)) As DurationInSeconds
NameOfYourTable As D1
NameOfYourTable As D2
D2.NameOfYourActionColumn != D1.NameOfYourActionColumn
) As D2
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer