Solved

Adjust Time difference

Posted on 2012-03-09
10
346 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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
try
<= with your +1
0
 
LVL 21

Expert Comment

by:huslayer
Comment Utility
Hi,
Can you provide a sample values for h.timein and s.timein ? and what's the expected result?
0
 

Author Comment

by:BobRosas
Comment Utility
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
 

Author Comment

by:BobRosas
Comment Utility
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:huslayer
huslayer earned 150 total points
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:BobRosas
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now