We help IT Professionals succeed at work.

Adjust Time difference

BobRosas
BobRosas asked
on
I have code to show if an employee was late for work.  The code works and shows if the person was 1 minute late or later.  Here is the code in part...

CONVERT(varchar(20), MIN(h.TimeIn), 100) AS Actual,
                  CASE WHEN s.TimeIn < MIN(h.TimeIn) THEN DATEDIFF(MINUTE, s.TimeIn, ISNULL(MIN(h.TimeIn), s.TimeIn)) END AS Tardy,
            MIN(h.RecordId) AS RecordId

Now I need to change the report so employees only show if they are 2 minutes late or more.  So I just added +1 to s.TimeIn above.  When I run the report, people who use to show 1 minute late are no longer showing...that's great!  But records that use to show 2 minutes late now don't show either.  Only 3 and more.  Is there some sort of format issue I'm missing?
Comment
Watch Question

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
try
<= with your +1
Jason YousefSr. BI  Developer

Commented:
Hi,
Can you provide a sample values for h.timein and s.timein ? and what's the expected result?

Author

Commented:
Is this what you mean?
CASE WHEN s.TimeIn +1 <= MIN(h.TimeIn) THEN DATEDIFF(MINUTE, s.TimeIn, ISNULL(MIN(h.TimeIn), s.TimeIn)) END AS Tardy,

I get the same result.  Employee who is 2 minutes late does not show on report.

Some sample values are...
TimeIn                                           TimeOut
2/21/2012 8:02:00 AM                  2/21/2012 10:41:00 AM
2/21/2012 10:55:00 AM                2/21/2012 12:35:00 PM
2/21/2012 1:04:00 PM                2/21/2012 2:47:00 PM
2/21/2012 3:02:00 PM                     2/21/2012 4:37:00 PM

The sample result I want is...
Name                Date                 Late
Jane Doe          2/21/2012        00:02

The above timeIn and TimeOut is an actual record that is not showing and I want it to.

Author

Commented:
The field I used on my report is actually "TimeOver" which uses "Tardy" from the above code.  Am I converting it wrong?

CONVERT(varchar, FLOOR(a.Tardy / 60.0)) + ':' + RIGHT('0' + CONVERT(varchar, a.Tardy % 60),2) AS TimeOver,
Jason YousefSr. BI  Developer
Commented:
What's s.timein ??  and where is it?

How you're comparing to see if he's late or not?!! what's your criteria or scheduled time in?

Author

Commented:
I found a third spot in my code referring to the field so I was able to get the right fields.
I apologize!   I'm so glad it's Friday!

So for the points would you please tell me how I can convert TimeOver field shown above from 0.00 format to 00.00 format?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
CONVERT(varchar, FLOOR(a.Tardy / 60.0))
>>>
right('00'+CONVERT(varchar, FLOOR(a.Tardy / 60.0)),2)

or

right('0'+CONVERT(varchar, FLOOR(a.Tardy / 60.0)),2)

if you are sure second part always gives you a number...

Author

Commented:
What a day...
I tried this...
CONVERT(varchar, FLOOR(a.Tardy / 60.0)) + ':' + RIGHT('00' + CONVERT(varchar, FLOOR(a.Tardy % 60)),2) AS TimeOver,
and I still get 0:00 format

The rest of my code wouldn't affect this outcome right?  This is the field on the report so when I change it here it should show...or so I thought.  Did I get the code wrong?
Sr. System Analyst
CERTIFIED EXPERT
Commented:
apply same thing to the first part:

CONVERT(varchar, FLOOR(a.Tardy / 60.0)) + ':' + RIGHT('00' + CONVERT(varchar, FLOOR(a.Tardy % 60)),2) AS TimeOver,

>>>

RIGHT('00'+CONVERT(varchar, FLOOR(a.Tardy / 60.0)),2) + ':' + RIGHT('00' + CONVERT(varchar, FLOOR(a.Tardy % 60)),2) AS TimeOver,

Author

Commented:
HainKurt - That was it!  Thank you so much!  i apologize for the original question.  I feel bad for the work huslayer did on my mess up so I also awarded that answer points.
Thanks again to you both and for your help!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.