locked
find a number of requests that had been solved in a 6 hours RRS feed

  • Question

  • Hello, I newbie in Microsoft Visual Studio 2008.

    I have a SQL query, which shows a time that had been spendeed on solving each request by every employee. Data base is Microsoft SQL Server on Windows Server 

    2008.
    I want to find a number of requests that had been solved in a 6 hours and below in percentage terms and also a summ of all solved requests of every employee 

    below and above 6 hours.
    How should i change a table 1 using Report Designer в Microsoft Visual Studio 2008, in order to get a table 

    • Moved by lake Xiao Monday, October 17, 2016 2:53 AM
    • Edited by Aleks Roth Friday, October 21, 2016 11:02 AM у
    Saturday, October 15, 2016 1:04 PM

Answers

  • If I understand correctly, this is table1 data:

    Get data directly in Report Designer based on Dataset1(Table 1):

    ü  Insert Table in Designer

    ü  Group by UserID

    ü  Delete Details in Row Groups: Delete group Only

    ü  Percentage formula: sum(iif(Fields!Spend.Value<=6,1,0))/count(Fields!UserID.Value)

    Number Formula: Count(Fields!UserID.Value)

    ü  Set Percentage format to P2 or 0.00%

    Monday, October 17, 2016 7:39 AM
  • correct line: sum(CASE WHEN time <= 6 THEN 1 ELSE 0 END)  ) 


    • Marked as answer by Aleks Roth Friday, October 21, 2016 11:05 AM
    • Edited by Aleks Roth Friday, October 21, 2016 11:06 AM correct
    Friday, October 21, 2016 11:05 AM

All replies

  • Hi Aleks Roth,

    Since your problem is more related SQL server, I moved your thread to our SQL server forum for better support.

    Thanks for your understanding.

    Best Regards,

    Lake Xiao

    Monday, October 17, 2016 2:51 AM
  • Hi Aleks Roth,

    Script below shows how to achieve the result in T-SQL. You could directly modify your existing SQL query accordingly.

    DECLARE @T Table
        ([employee] varchar(10), [requestId] varchar(10), [hour] int)
    ;
        
    INSERT INTO @T
    VALUES
        ('USER1', '001', 6),
        ('USER1', '002', 5),
        ('USER1', '003', 7),
        ('USER1', '004', 9),
        ('USER2', '005', 2),
        ('USER2', '006', 1)
    ;
    
    SELECT [employee]
    	, CONVERT(varchar, CONVERT(decimal(5,2), COUNT(CASE WHEN [hour] <= 6 THEN 1 END) * 1.0 / COUNT(1) * 100)) + '%' AS [percent]
    	, COUNT(1) AS [solved_sum]
    FROM @T
    GROUP BY [employee]

    Sam Zha
    TechNet Community Support

    • Proposed as answer by Visakh16MVP Monday, October 17, 2016 7:23 AM
    Monday, October 17, 2016 7:14 AM
  • If I understand correctly, this is table1 data:

    Get data directly in Report Designer based on Dataset1(Table 1):

    ü  Insert Table in Designer

    ü  Group by UserID

    ü  Delete Details in Row Groups: Delete group Only

    ü  Percentage formula: sum(iif(Fields!Spend.Value<=6,1,0))/count(Fields!UserID.Value)

    Number Formula: Count(Fields!UserID.Value)

    ü  Set Percentage format to P2 or 0.00%

    Monday, October 17, 2016 7:39 AM
  • Hello,  it works but  while it works it produces an error.
    Conversion failed when converting the varchar value 'hour' to data type int
    Tuesday, October 18, 2016 12:24 AM
  • Check your data type. you can use cast(hour as int). For example:
    declare @t varchar(20);
    select @t='3'
    select sum(cast(@t as int))
    Tuesday, October 18, 2016 1:48 AM
  • It seems that you have put 'hour' as value in the query result, the below code will reproduce the same error. 
    DECLARE @T Table
        ([employee] varchar(10), [requestId] varchar(10), [hour] int)
    ;
        
    INSERT INTO @T
    VALUES
        ('USER1', '001', 6),
           ('USER2', '006', 'hour');
    
    select * from @t
    Because HOUR type is int, USER2 gives a 'hour' varchar type. It is not correct. So check your code and result to correct them.
    Tuesday, October 18, 2016 2:33 AM
  • Another assumption:

    SELECT [employee]
    	, CONVERT(varchar, CONVERT(decimal(5,2), COUNT(CASE WHEN 'hour' <= 6 THEN 1 END) * 1.0 / COUNT(1) * 100)) + '%' AS [percent]
    	, COUNT(1) AS [solved_sum]
    FROM @T
    GROUP BY [employee]

    Do you use 'hour' instead of [hour] or hour? 'hour' is not correct as column name. Kindly have a check.

    • Proposed as answer by Riaon Tuesday, October 18, 2016 2:45 AM
    Tuesday, October 18, 2016 2:43 AM
  • Hello.
    I have corrected my sql query and get table 1.
    Help me please change table 1 in order to get table 2. I think that i need to group "employee" lines, but how can i do that in Report Designer I don't know

    Table1
    USER1,0.00%,1
    USER1,100.00%,1
    USER1,100.00%,1
    USER1,100.00%,1
    USER1,0.00%,1
    USER1,100.00%,1
    USER1,0.00%,1
    USER2,0.00%,1
    USER2,100.00%,1
    USER2,0.00%,1

    Table2
    USER1,80.00%,7
    USER2,100.00%,3
    Tuesday, October 18, 2016 1:12 PM
  • How to get 80%? If you do not use T-SQL, you can refer to the solution provided by me.
    Wednesday, October 19, 2016 2:53 AM
  • Using sum(iif(Fields!Spend.Value<=6,1,0))/count(Fields!UserID.Value)
    error: Statistical function does not work with my data type
    Wednesday, October 19, 2016 12:11 PM
  • SELECT fio, quantity,sum,
    (quantity/sum)*100 as result
    FROM 
    (SELECT fio, sum(quantity) as quantity, sum(courequst) as sum
    FROM
    (SELECT date_s, id, fio , time
     , CONVERT(decimal(5,2), COUNT(CASE WHEN time <= 6 THEN 1 ELSE 0 END)  )   AS 'quantity'
    , COUNT(1) AS 'courequst'
     FROM Z_speed
    WHERE (date_f > convert(date, @date)) 
    GROUP BY fio,date_s,id,date_f  ) tmp_table
    GROUP BY fio) tmp_table2

    this request should make this calculations:
    1. If spended less then 6 hours for accomplishment of one request then 1, if not 0.
    2. summ of every request, that was accomplished  in 6 hours by every employee (quantity)
    3. summ of all requests made by every employee (sum)
    4. item 2 divide item 3 (result)

    Now error is: request calculate 'quantity' by every  id request, but not by every employee

    Wednesday, October 19, 2016 1:15 PM
  • What's your datatype? In my example, Spend type is int while UserID is varchar.
    Thursday, October 20, 2016 5:42 AM
  • correct line: sum(CASE WHEN time <= 6 THEN 1 ELSE 0 END)  ) 


    • Marked as answer by Aleks Roth Friday, October 21, 2016 11:05 AM
    • Edited by Aleks Roth Friday, October 21, 2016 11:06 AM correct
    Friday, October 21, 2016 11:05 AM