0

I got a question from our product team to build a "Table availability of customer service representatives (user)". f

For example : user_A was available to accept a call from 12:00 to 12:03, so I have in the database a table with UserName = user_A,StartDatetime = 20151110 12:00,EndDateTime = 20151110 12:03.

From this data I need to create an availability report that can show which users were available in minute interval (every minute) along the all day .

  • You could probably design a simple SSRS report to display that information. Did you try to write a query for this? – sql_handle Nov 11 '15 at 6:17
  • yes, I wrote a query that spread the "duration" across a minute table. – itzik Paz Nov 11 '15 at 6:25
  • 2
    Please add the query to the question and the specific issue or problem that you need help with. – sql_handle Nov 11 '15 at 6:27
1

I think in think the use of a tally table would work in this case. There are 1440 minutes in a day, so a tally table would need a minimum of 1440 records in order to work. Using the tally table, you can utilize the DATEADD function to add one minute between your start times and your end times. This would pull back one record per minute for the interval.

/* create the tables variables */
DECLARE @Tally TABLE
    (MinuteOfDay INT)

DECLARE @Users TABLE
    (UserName  VARCHAR(100)
    ,StartDatetime DATETIME 
    ,EndDateTime DATETIME
    )


/* Populate the user table */
INSERT INTO @Users
        ( UserName ,
          StartDatetime ,
          EndDateTime
        )
VALUES  ( 'User_A' , -- UserName - varchar(100)
          '11-10-2015 12:00' , -- StartDatetime - datetime
          '11-10-2015 12:03'  -- EndDateTime - datetime
        )

/* Populate the tally table with 1 minute for 24 hours */
INSERT INTO @Tally
        ( MinuteOfDay )
SELECT TOP 1440
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) -1 AS Row_No
FROM sys.all_objects AS ao


/* Query to pull the result for each minute */
SELECT * FROM @Users AS u
INNER JOIN @Tally AS t ON DATEADD(MINUTE, t.MinuteOfDay,u.StartDatetime) BETWEEN u.StartDatetime AND u.EndDateTime

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.