Solved

Adjust Time difference

Posted on 2012-03-09
10
353 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:BobRosas
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37702557
try
<= with your +1
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37702594
Hi,
Can you provide a sample values for h.timein and s.timein ? and what's the expected result?
0
 

Author Comment

by:BobRosas
ID: 37702629
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.
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:BobRosas
ID: 37702644
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,
0
 
LVL 21

Assisted Solution

by:Jason Yousef, MS
Jason Yousef, MS earned 150 total points
ID: 37702923
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?
0
 

Author Comment

by:BobRosas
ID: 37702933
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?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37702952
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...
0
 

Author Comment

by:BobRosas
ID: 37703046
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?
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 350 total points
ID: 37703142
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,
0
 

Author Closing Comment

by:BobRosas
ID: 37703353
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!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question