Script for employees and their monthly man hours

Question

Post reply

Script for employees and their monthly man hours

hurricaneDBA

SSCertifiable

Points: 5027
More actions
January 5, 2021 at 6:08 am
Go to Answer
#3827430
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 years, 11 months ago by hurricaneDBA.

DesNorton

SSC-Insane

Points: 24399
More actions
January 13, 2021 at 12:28 pm
Answer
#3831904
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
```
How to post data/code on a forum to get the best help.

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

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

DesNorton SSC-Insane Points: 24399 More actions · Answer 1

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;

How to post data/code on a forum to get the best help.

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 2

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

This reply was modified 3 years, 11 months ago by hurricaneDBA.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 3

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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DesNorton SSC-Insane Points: 24399 More actions · Answer 4

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

How to post data/code on a forum to get the best help.

GaryV Hall of Fame Points: 3307 More actions · Answer 5

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

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 6

@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

DesNorton SSC-Insane Points: 24399 More actions · Answer 7

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;

How to post data/code on a forum to get the best help.

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 8

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

DesNorton SSC-Insane Points: 24399 More actions · Answer 9

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.

How to post data/code on a forum to get the best help.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 10

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 11

@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

GaryV Hall of Fame Points: 3307 More actions · Answer 12

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.

This reply was modified 3 years, 11 months ago by GaryV.

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 13

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