Solved

Adjust Time difference

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

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
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.

 

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

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 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

18 Experts available now in Live!

Get 1:1 Help Now