SQL query – doing a loop but using an array of variables

Question

Here is a sample of the code I'm writing

DECLARE @totlunch int = 0;
DECLARE @totbreak int = 0;
DECLARE @over int = 0;


SELECT @totlunch = SUM(CASE when [StatusKey] = 'at lunch' then StateDuration else 0 end),
        @totbreak = SUM(CASE when [StatusKey] = 'break' then StateDuration else 0 end) 
  FROM [I3_IC].[dbo].[AgentActivityLog]
  WHERE UserId in ('mdavila')
    AND StatusDateTime between '2014-08-28 00:00:00' AND '2014-08-28 23:59:59'
  --group by UserId

  print @totlunch;
  print @totbreak;

  if(@totlunch > 3600)BEGIN
    SET @over = @over + (@totlunch - 3600);
  END

  if(@totbreak > 1800)BEGIN
    SET @over = @over + (@totbreak - 1800);
  END

  print @over;

I want to do this task for a group of "UserId"s, insterad of juat "mdavila" but I'm not sure how to loop it as SQL queries seem to not support arrays. Any help in how I can accomplish this task would be greatly appreciated. Thanks!

Please note that I need to keep track of each user individually. I will populate a temp table with the data I want, but I just need to know a way of looping this code for a group of users.

what rdbms are you using?

Donal
– Donal

2014-09-11 23:57:57 +00:00
Commented Sep 11, 2014 at 23:57 — Donal
– Donal, Commented Sep 11, 2014 at 23:57
Is this SQL Server, Oracle, MySQL?

Donal
– Donal

2014-09-12 00:06:36 +00:00
Commented Sep 12, 2014 at 0:06 — Donal
– Donal, Commented Sep 12, 2014 at 0:06
ok, see my answer below.

Donal
– Donal

2014-09-12 15:42:06 +00:00
Commented Sep 12, 2014 at 15:42 — Donal
– Donal, Commented Sep 12, 2014 at 15:42

Donal · Accepted Answer · 2014-09-12 07:15:29Z

5

If you are using SQL Server, you can use a Table variable to loop through records.

I have simplified it here to show you the concept - it should be enough to get you started.

-- Table variable to store the list of user ID
declare @UserIds table (UserId INT) 
declare @CurrentUserID INT

-- Load the table with the list of users we want to work with
INSERT INTO @UserIds (UserId)
SELECT userid
FROM AgentActivityLog   

-- loop through each user
WHILE EXISTS (SELECT UserId FROM @UserIds)
BEGIN

    -- select a user from the list of users
    SELECT TOP 1 @CurrentUserID = UserId 
    FROM @UserIds
    ORDER BY UserId ASC

    -- Do stuff with @CurrentUserID

   --Remove the current user id from the table variable - we are done with it
   DELETE FROM @UserIds WHERE UserId = @CurrentUserID
END

edited Sep 12, 2014 at 7:15

answered Sep 12, 2014 at 0:06

Donal

33.2k10 gold badges69 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bugsta Over a year ago

Works perfectly! Thanks Donal! This site is great!

Collectives™ on Stack Overflow

SQL query – doing a loop but using an array of variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related