SQL 2014, SSRS - Time calculation using parameter gives error and wrong calculation

Question

I made a report using Visual Studio to calculate agents stats at our centre. In the report are parameters to select date for the report and another to select Agents to EXCLUDE from the report stats.

The data in the table has login times and logout times. I did a datediff to get the total logged in time in seconds. Then I add up all the agents' seconds to get total shift (in seconds) for that day. I created a function that converts the number of seconds to hh:mm:ss.

The issue:
No matter which agents I select from the drop-down, my total shift hours remains the same.

The function I created:

CREATE FUNCTION [dbo].[UDF_SecondsToHHMMSS_VarChar](@Seconds int) RETURNS varchar(20) AS
BEGIN
    RETURN (
             SELECT CONVERT(VARCHAR(20), @Seconds / 3600) + ':' 
           + RIGHT('00' + CONVERT(VARCHAR(2), (@Seconds % 3600) / 60) ,2) + ':' 
           + RIGHT('00' + CONVERT(VARCHAR(2), @Seconds%60), 2) AS [HMS] 
           )
END

GO

Query as part of Main dataset:

,(SELECT [UDF_SecondsToHHMMSS_VarChar](SUM (DATEDIFF(second, AgentLoginTime, AgentLogoutTime)))
      FROM     [AgentStats]
      WHERE    DATEADD(dd, 0, DATEDIFF(dd, 0, MidnightStartDate)) = CAST(@S AS DATETIME) 
        AND (AgentID NOT IN (@AgentName) OR ISNULL(AgentID,'') = '')
                  )[Actual Shift Hrs]
-- I used a GROUP BY later to calculate the SUM

I created another dataset to pull up the agent names to EXCLUDE from the stats. I used a Union to add a dummy line when there are no agents to exclude(I set that as the default value).

The dataset for that is:

SELECT DISTINCT AgentID, AgentFirstName + ' ' + AgentLastName [AgentName]

FROM AgentStats

WHERE MidnightStartDate >= dateadd(day, -60, getdate())
  AND MidnightStartDate <  getdate()
  AND AgentID NOT IN ('1067', '1050', '1150', '1052', '1161', '1158', '1063', '1173', '1101', '1128', '1165') -- These agents permanently excluded

UNION

SELECT '100', 'No Agents'  -- This is default if there are no agents to select from drop-down

ORDER BY [AgentName];

I've set parameters to select the date for the report and another to select the agents to exclude from a multiple-selection drop down.

For the AgentName parameter, I selected the relevant dataset and selected AgentID as the Value field and AgentName as the Label field.

I'm not sure why the main query is not selecting the AgentID from the AgentName parameter.

What am I doing wrong?

EDIT 1:
My AgentName dataset when run separately as a query gives me the following results. (see attached example. Ignore the celebrity names)

My intention was that the main dataset should take the IDs from the AgentID column, arrange them in comma separated strings and exclude those from the total shift hours.

EDIT 2:
I think I fixed it. I'm going to do a little testing to check the results. I changed the code in the Main dataset to:

AND (AgentID NOT IN (@AgentName,'1067', '1050', '1150', '1052', '1161', '1158', '1063', '1173', '1101', '1128', '1165') OR ISNULL(AgentID,'') = '')

Can you just post the entire query....this is very confusing trying to piece it all together. — S3S
AND (AgentID NOT IN (@AgentName) or... this part is almost certainly wrong. unless @AgentName is actually AgentID and is a comma seperated and quoted list. What is the actual values being passed into this parameter? — S3S
Yes. AgentIDs will be comma separated and go into @AgentName. Eg; NOT IN ('1120', '1121', '1122') — user1777929

LONG LONG · Accepted Answer · 2017-03-24T18:21:46

You mentioned:

Then I add up all the agents' seconds to get total shift (in seconds) for that day.

The issue:

No matter which agents I select from the drop-down, my total shift hours remains the same.

The total shift is a static value for that particular day based on your description.

1st Update: Try to alter the data type from int to bigint from the UDF incase data overflow happens

SQL 2014, SSRS - Time calculation using parameter gives error and wrong calculation

1 Answers