Script for employees and their monthly man hours

  • Dear Everyone

    I hope you are all safe

    I have a script which needs to be modified for gathering the monthly man hours for my company.

    I use a cursor to add the employee ID and man hours into the cursor and display it at the end of the cursor.

    I cant get to display all employee IDs only the latest one.

    How do I do this?

    Here is my script below.

    create table #Monthly_Hours (

    emp_id int,

    TRANSIT_DATE datetime

    )

    insert into #Monthly_Hours values (95,'2020-12-07 08:01:57.000')

    insert into #Monthly_Hours values (95,'2020-12-07 08:03:14.000')

    insert into #Monthly_Hours values (95,'2020-12-07 09:49:23.000')

    insert into #Monthly_Hours values (95,'2020-12-07 15:54:05.000')

    insert into #Monthly_Hours values (95,'2020-12-08 08:01:57.000')

    insert into #Monthly_Hours values (95,'2020-12-08 09:49:23.000')

    insert into #Monthly_Hours values (95,'2020-12-08 15:54:05.000')

    insert into #Monthly_Hours values (211,'2020-12-07 08:01:57.000')

    insert into #Monthly_Hours values (211,'2020-12-07 08:03:14.000')

    insert into #Monthly_Hours values (211,'2020-12-07 09:49:23.000')

    insert into #Monthly_Hours values (211,'2020-12-07 15:54:05.000')

    insert into #Monthly_Hours values (211,'2020-12-08 08:03:14.000')

    insert into #Monthly_Hours values (211,'2020-12-08 10:49:23.000')

    insert into #Monthly_Hours values (211,'2020-12-08 15:54:05.000')

    select * from #Monthly_Hours;

    SELECT emp_id, DATEDIFF(minute,convert(varchar, min(CONVERT(VARCHAR(8),TRANSIT_DATE,108)), 8), convert(varchar, max(CONVERT(VARCHAR(8),TRANSIT_DATE,108)), 8))/60.00 AS HOURS

    from #Monthly_Hours

    where transit_Date>= '2020-12-06 00:00:00.000' and transit_date <= '2020-12-09 00:00:00.000'

    and emp_id not like '%C%'

    --and emp_id like '%95%'

    and emp_id not like '%V%'

    and emp_id not like '%O%'

    and emp_id not like '%T%'

    and emp_id != ''

    group by emp_id, CONVERT(Date, TRANSIT_DATE)

     

     

     

    -- Declare the return variable here

    DECLARE @ALLHours FLOAT;

    DECLARE @AddHours FLOAT;

    DECLARE @EmpID INT;

    -- Add the T-SQL statements to compute the return value here

    DECLARE cursor_hours CURSOR FOR

    SELECT emp_id, DATEDIFF(minute,convert(varchar, min(CONVERT(VARCHAR(8),TRANSIT_DATE,108)), 8), convert(varchar, max(CONVERT(VARCHAR(8),TRANSIT_DATE,108)), 8)) AS HOURS

    from #Monthly_Hours

    where transit_Date>= '2020-12-06 00:00:00.000' and transit_date <= '2020-12-09 00:00:00.000'

    and emp_id not like '%C%'

    --and emp_id like '%95%'

    and emp_id not like '%V%'

    and emp_id not like '%O%'

    and emp_id not like '%T%'

    and emp_id != ''

    group by emp_id, CONVERT(Date, TRANSIT_DATE)

    -- you need to INITIALIZE this value to 0 !!!!!

    SET @ALLHours = 0;

    OPEN cursor_hours;

    FETCH NEXT FROM cursor_hours INTO @EmpID, @AddHours

    WHILE @@FETCH_STATUS=0

    BEGIN

    -- you need to make sure to use ISNULL(.., 0) to avoid a NULL value in the SUM

    SET @ALLHours += ISNULL(@AddHours , 0);

    FETCH NEXT FROM cursor_hours INTO @EmpID,@AddHours

    END

    CLOSE cursor_hours

    DEALLOCATE cursor_hours

    select @EmpID, @ALLHours/60.00

    drop table #Monthly_Hours;

    • This topic was modified 3 weeks, 1 day ago by  hurricaneDBA.
  • Why are you using a cursor?  A simple sum with group by will do the trick

    WITH cteHours AS (
    SELECT emp_id
    , [HOURS] = DATEDIFF(
    MINUTE, CONVERT( varchar, MIN( CONVERT( varchar(8), TRANSIT_DATE, 108 )), 8 )
    , CONVERT( varchar, MAX( CONVERT( varchar(8), TRANSIT_DATE, 108 )), 8 )
    )
    FROM #Monthly_Hours
    WHERE transit_Date >= '2020-12-06 00:00:00.000'
    AND transit_date <= '2020-12-09 00:00:00.000'
    AND emp_id NOT LIKE '%C%'
    --and emp_id like '%95%'
    AND emp_id NOT LIKE '%V%'
    AND emp_id NOT LIKE '%O%'
    AND emp_id NOT LIKE '%T%'
    AND emp_id != ''
    GROUP BY emp_id, CONVERT( date, TRANSIT_DATE )
    )
    SELECT h.emp_id
    , AllHours = SUM(h.[HOURS])/60.00
    FROM cteHours AS h
    GROUP BY h.emp_id;
  • Hi Des

    thanks for the answer it works well except for values which are zero then it doesn’t list them.

    how to list the zero values?

    For example if I include this line into the table then the query above doesn't pick it up.

    Id like it to at least display the employee ID and display 0 next to him.

    430 2020-12-07 00:00:00.000

    430 2020-12-08 00:00:00.000

     

    Desired outcome:

    EmpID  AllHours

    480          0

     

    kal

  • hurricaneDBA wrote:

    Hi Des

    thanks for the answer it works well except for values which are zero then it doesn’t list them. how to list the zero values?

    For example if I include this line into the table then the query above doesn't pick it up.

    Id like it to at least display the employee ID and display 0 next to him.

    430 2020-12-07 00:00:00.000 430 2020-12-08 00:00:00.000

    Desired outcome:

    EmpID  AllHours 480          0

    kal

    Your sample data does not contain any rows for EmpId 480. Given that, how do you expect anyone to write a query which will provide the results you require?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • hurricaneDBA wrote:

    Hi Des

    thanks for the answer it works well except for values which are zero then it doesn’t list them. how to list the zero values?

    For example if I include this line into the table then the query above doesn't pick it up.

    Id like it to at least display the employee ID and display 0 next to him.

    430 2020-12-07 00:00:00.000 430 2020-12-08 00:00:00.000

    Desired outcome:

    EmpID  AllHours 480          0

    kal

    I just added the 2 new values into the temp table, and my query definitely returns the user

    Capture

  • Why is emp_id defined as an int, and then in the WHERE clause it looks for character strings?

     

  • @Phil my original post doesn't include but I did in the next post.

    @desnorton - yes it works now but if the employee ID is there and he or she don't swipe then nothing is captured as it would appear as null. Is there a way to capture this and make the monthly hours set to 0 is the value is null?

    @gvoshol - It was a typing mistake

    The only question left is if I have a null time in / time out how do I capture this null and return it as zero?

    Kal

  • hurricaneDBA wrote:

    @desnorton - yes it works now but if the employee ID is there and he or she don't swipe then nothing is captured as it would appear as null. Is there a way to capture this and make the monthly hours set to 0 is the value is null?

    Kal

     

    If he did not swipe, and is not recorded in the table, then there is no way to know from that table that he even exists.

    You would need an employee table where you get a list of employees, and LEFT JOIN it to the query above

    WITH cteHours AS (
    -- See previous code
    )
    SELECT e.emp_id
    , AllHours = ISNULL(SUM(h.[HOURS]), 0)/60.00
    FROM tb_Employee AS e
    LEFT JOIN cteHours AS h ON e.emp_id = h.emp_id
    GROUP BY e.emp_id;
  • Dear DesNorton

    I was testing the JOINS but your query didn't give the correct numbers or the employee IDs.

    Here is the new query with some adjustments as im looking for the specific employee IDs below:

    WITH cteHours AS (

    SELECT identifier

    , [HOURS] = DATEDIFF(

    MINUTE, CONVERT( varchar, MIN( CONVERT( varchar(8), TRANSIT_DATE, 108 )), 8 )

    , CONVERT( varchar, MAX( CONVERT( varchar(8), TRANSIT_DATE, 108 )), 8 )

    )

    FROM cms.dbo.HA_TRANSIT

    WHERE transit_Date >= '2020-12-01 00:00:00.000'

    AND transit_date <= '2021-01-01 00:00:00.000'

    AND identifier NOT LIKE '%C%'

    and identifier in

    (

    '95',

    '1589',

    '1451',

    '480',

    '934',

    '970',

    '1499',

    '1559',

    '1610',

    '1864',

    '2030',

    '2047',

    '2055',

    '2091',

    '2118',

    '2193',

    '2251',

    '2343',

    '2354',

    '2399',

    '2404',

    '2438',

    '2600',

    '2602',

    '2635',

    '2660',

    '2718',

    '2742',

    '2757',

    '2782',

    '2804',

    '2814',

    '2862',

    '2866')

    AND identifier NOT LIKE '%V%'

    AND identifier NOT LIKE '%O%'

    AND identifier NOT LIKE '%T%'

    AND identifier != ''

    GROUP BY identifier, CONVERT( date, TRANSIT_DATE )

    )

    SELECT e.identifier

    , AllHours = ISNULL(SUM(h.[HOURS]),0)/60.00

    FROM cms.dbo.HA_TRANSIT AS e

    RIGHT JOIN cteHours AS h on e.IDENTIFIER = h.IDENTIFIER

    GROUP BY e.identifier

     

  • I cannot see your tables or the data in them, so I cannot test the scripts.

    The query that you have is not the same as the one that I posted.  You are looking in the same table inside and outside of the CTE, so I am not surprised that it is not returning the correct results.  The reason for the join to an external table is to get a full list of EmployeeIDs.

    A RIGHT JOIN is not the correct join for this scenario.

  • hurricaneDBA wrote:

    @Phil my original post doesn't include but I did in the next post.

    Kal

    I stated that your sample data did not include EmpId 480.

    That statement was correct now and remains correct.

    When your desired results do not match the sample data you have provided, it makes solving the problem more difficult for everyone.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • @desnorton

    I modified the query and joined with the employee table but as you can see from my query I need it to compare to specific employee IDs but its picking up all IDs.

    and identifier in

    (

    '95','480')

     

     

    The full query is below; please advise how to limit picking up only the required Emp IDs

     

    WITH cteHours AS (

    SELECT identifier

    , [HOURS] = DATEDIFF(

    MINUTE, CONVERT( varchar, MIN( CONVERT( varchar(8), TRANSIT_DATE, 108 )), 8 )

    , CONVERT( varchar, MAX( CONVERT( varchar(8), TRANSIT_DATE, 108 )), 8 )

    )

    FROM cms.dbo.HA_TRANSIT

    WHERE transit_Date >= '2020-12-01 00:00:00.000'

    AND transit_date <= '2021-01-01 00:00:00.000'

    AND identifier NOT LIKE '%C%'

    and identifier in

    (

    '95','480')

     

    AND identifier NOT LIKE '%V%'

    AND identifier NOT LIKE '%O%'

    AND identifier NOT LIKE '%T%'

    AND identifier != ''

    GROUP BY identifier, CONVERT( date, TRANSIT_DATE )

    )

    SELECT e.identifier

    , AllHours = ISNULL(SUM(h.[HOURS]),0)/60.00

    FROM cms.dbo.Employee AS e

    LEFT JOIN cteHours AS h on e.IDENTIFIER = h.IDENTIFIER

    GROUP BY e.identifier

     

  • Your LEFT JOIN works correctly:

    Get everything from Employee table

    And then match it to records in cteHours

    If anything in Employee doesn't match the cte, return NULL for AllHours

    If that's not what you want, then don't use a left join.

     

  • Dear Gvoshol

    It is giving me all employees including the ones I specified in the IN CLAUSE

    So what could be wrong?

    Thanks everyone for the help

    Kal

  • If you are only interested in specific employees, then put the filter on the Employee table

    WITH cteHours AS (
    ...
    )
    SELECT e.identifier
    , AllHours = ISNULL(SUM(h.[HOURS]),0)/60.00
    FROM cms.dbo.Employee AS e
    LEFT JOIN cteHours AS h on e.IDENTIFIER = h.IDENTIFIER
    WHERE e.identifier IN ( ... )
    GROUP BY e.identifier

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply