CODEWITHSUNDEEP
sql-server-2008
Andrew Martin
Andrew Martin

Reputation: 151

SQL Server : how to loop through rows and assign value

I have a couple of tables to deal with. One is like this:

dbo.userActivity 

 userID    Action    Time
   1         25       12:00
   1         10       12:01
   1         12       12:35
   1          6       13:54
   2         10        6:47
   2         42        6:48
   3          8       11:54
   etc.

The other table is a schedule that looks like this:

dbo.userSchedule 

 userID    schedule_start    schedule_stop
   1            07:00            09:00
   2            11:00            12:30
   3            14:00            15:00
  etc.

What I need to do for each row in dbo.userActivity is determine if each action was an hour before the schedule_start, between the schedule_start and schedule_stop times, an hour after schedule_stop, or any other time.

So I need to append a column to dbo.userActivity with the values 'before', 'during', 'after', 'other' based on the time calculations.

I'm really not sure how to do this and would appreciate any help you all could offer.

EDIT:

Okay, so I have that mostly working. I've just seen some of the real data I'll be working on and noticed that the activity times are full datetime stamps, whereas the activity schedule is just in time. So I need to convert the datetime to something I can compare.

This looks like it works:

SELECT * FROM ( SELECT CONVERT(TIME, SCANDATE) as scanTime FROM appData ) st WHERE st.scanTime <= '6:00' ORDER BY st.scanTime

Just as an example. But when I try to incorporate that into the case statement below like this, it doesn't work. It applies the same THEN to every row.

SELECT
  CASE
  WHEN EXISTS
  (
  SELECT *
  FROM
  (
  SELECT CONVERT(TIME, SCANDATE) as scanTime 
  FROM appData
  ) st
  WHERE st.scanTime <= '6:00'
  )
  THEN 'Before 6 am'

  ELSE '6 am or after'
  END
FROM appData

Any further thoughts on this?

Upvotes: 2

Views: 3117

Answers (1)

Adam Wenger
Adam Wenger

Reputation: 17540

First you would want to add the column (NULL) to your table.

ALTER TABLE dbo.UserActivity
ADD TimeStatus VARCHAR(6) NULL;

Next you would write your update query. It can be written with a CASE statement:

UPDATE ua
SET ua.TimeStatus =
   CASE
      WHEN CAST(ua.Time AS TIME) >= us.Schedule_Start
         AND CAST(ua.Time AS TIME) <= us.Schedule_stop THEN 'During'
      WHEN CAST(ua.Time AS TIME) >= DATEADD(HOUR, -1, us.Schedule_Start)
         AND CAST(ua.Time AS TIME) <= us.ScheduleStart THEN 'Before'
      WHEN CAST(ua.Time AS TIME) >= us.Schedule_Stop
         AND CAST(ua.Time AS TIME) <= DATEADD(HOUR, 1, us.Schedule_Stop)
         THEN 'After'
      ELSE 'Other'
   END
FROM dbo.UserActivity AS ua
INNER JOIN dbo.userSchedule AS us ON ua.UserId = us.UserId

Once all the columns have data, you can set the column to NOT NULL if you have updated the application to know about, and populate, this new column.

I would also consider not storing the string values in this table, but instead a smaller value that will foreign key to a reference table instead. With only 4 values, you can use a TINYINT and save space for each dbo.UserActivity record that you store.

If you decide to go this route, you would just take the values from the reference table, and replace the string values with the ID values. If Before = 0, during = 1, After = 2, and Other = 3

UPDATE ua
SET ua.TimeStatusId =
   CASE
      WHEN CAST(ua.Time AS TIME) >= us.Schedule_Start
         AND CAST(ua.Time AS TIME) <= us.Schedule_stop THEN 1
      WHEN CAST(ua.Time AS TIME) >= DATEADD(HOUR, -1, us.Schedule_Start)
         AND CAST(ua.Time AS TIME) <= us.ScheduleStart THEN 0
      WHEN CAST(ua.Time AS TIME) >= us.Schedule_Stop
         AND CAST(ua.Time AS TIME) <= DATEADD(HOUR, 1, us.Schedule_Stop)
         THEN 2
      ELSE 3
   END
FROM dbo.UserActivity AS ua
INNER JOIN dbo.userSchedule AS us ON ua.UserId = us.UserId

You would then get the text values for the UI with an INNER JOIN to your TimeStatus reference table

Upvotes: 1

Related Questions