Solved

Adjust Time difference

Posted on 2012-03-09
10
352 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 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