build users availability report/Table (using sql) 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 .
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