How to loop over table variable to make specific calculations?

Question

If i have a table variable like that how to loop over this var to make some processing :

DECLARE @userData TABLE(
                                    userId int NOT NULL,
                                    dayDate datetime NOT NULL,
                                    transIn datetime NULL,
                                    transOut datetime NULL,
                                    attIn datetime NULL,
                                    attOut datetime NULL,
                                    MissionIn datetime NOT NULL,
                                    MissionOut datetime NOT NULL,
                                    empState varchar(10) NULL
                                   );


                INSERT INTO @userData
                SELECT userid, trans_date,transtime_in,transtime_out,att_start_time,att_end_time,@Mission_fromdatetime,@Mission_todatetime,day_flag
                FROM datatable_o a
                WHERE a.userid = @userid AND a.trans_date = @date ORDER BY  transtime_in ;

---

According to the comments the Whole case :

If the work starts at : att_start_time and ends at att_end_time (work period]

Every employee could check -in and check-out many times in the same date so we could follow him . the check-in stored in transtime_in and check-out stored in transtime_out

and i have day_flag so i could know the day is 'W' work day or 'E' weekend

Now considering all these information in addition to the emp_num ,date

I want to calc for the an employee External mission over time :

I have four cases :

1. No check-in-out && Not work day [weekend] So the employee should take all the mission period as overtime
2. No check-in-out && Work day [Absent] so the employee should take only the mission period out of the work period
3. There are check-in-outs && Not work day [week end] so the employee should take only the mission period out of these check-ins-outs
4. There are check-ins-outs && work day so the employee should take only the mission period out of these check-ins-outs and at the same time out of work period .

Example :

emp_num     date         att_start   att_end    mission-in  mission-out
  672      2015-3-4       07:05:00    13:30:00   12:12:00    20:00:00

---

emp_num      date         trans_in     trans_out 

 672       2015-3-4       06:54:00     11:10:00
 672       2015-3-4       12:00:00     14:05:00

Answer 1

You can loop through your table by taking help from a copy of that table:

with regard to your question assuming your table is:

DECLARE @userData TABLE(
                         userId int NOT NULL,
                         /*Other fields*/
                        );

and the data of your table is:

INSERT INTO @userData
/*A SELECT or values*/

now create a copy of your table as:

DECLARE @userData_2 TABLE(
                         userId int NOT NULL,
                         /*Structure should be the same as @userData*/
                        );
INSERT INTO @userData_2
SELECT * FROM  @userData

now you can do the loop and do whatever you want:

DECLARE @userId INT
WHILE EXISTS (SELECT * FROM @userData_2)
BEGIN
SET @userId=(SELECT TOP 1 userId FROM @userData)
/*

DO YOUR TRANSACTION HERE

*/
DELETE FROM @userData_2 WHERE userId=@userID
END

NOTICE: this assumes the userId is unique, if not then you need to have a unique field, or use a composite fields instead of userId.