Solved

sql query

Posted on 2007-12-04
21
302 Views
Last Modified: 2010-03-19
hi all,

i have a query written to calculate total hours for employees logged in. but with my query it is giving round figure. is there anyway i get decimal also. my query goes like this

SELECT employeeid, storeid, CONVERT(VARCHAR,timein,101) AS date, CONVERT(VARCHAR,timein,108)AS [clock in], CONVERT(VARCHAR,timeout,108) AS [Clock Out],
DATEDIFF(hour,timein,timeout)AS [Total Hours], ((DATEDIFF(minute,timein,timeout))/60)AS [Total minutes] FROM hrhours
WHERE timein IS NOT NULL AND
(timein BETWEEN CONVERT(DATETIME, '12/1/2007', 101) AND CONVERT(DATETIME, '12/3/2007', 101))
0
Comment
Question by:romeiovasu
  • 8
  • 8
  • 4
  • +1
21 Comments
 
LVL 18

Assisted Solution

by:Yveau
Yveau earned 166 total points
ID: 20407523
Try this:

(changed from 60 to 60.0)

Hope this helps ...

SELECT  employeeid

,       storeid

,       CONVERT(VARCHAR,timein,101) AS date

,       CONVERT(VARCHAR,timein,108)AS [clock in]

,       CONVERT(VARCHAR,timeout,108) AS [Clock Out]

,       DATEDIFF(hour,timein,timeout)AS [Total Hours]

,       ((DATEDIFF(minute,timein,timeout))/60.0)AS [Total minutes] 

FROM    hrhours

WHERE   timein IS NOT NULL 

AND     (timein BETWEEN CONVERT(DATETIME, '12/1/2007', 101) AND CONVERT(DATETIME, '12/3/2007', 101))

Open in new window

0
 
LVL 25

Accepted Solution

by:
imitchie earned 167 total points
ID: 20407565
did you know... that DATEDIFF ignores all other fields?

i.e. DATEDIFF(hour, '12:59', '13:01') returns 1 ??

I think you need to revisit your query
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20407567
I think you are looking for this:

(changed / to %)

Hope this helps ...
SELECT  employeeid

,       storeid

,       CONVERT(VARCHAR,timein,101) AS date

,       CONVERT(VARCHAR,timein,108)AS [clock in]

,       CONVERT(VARCHAR,timeout,108) AS [Clock Out]

,       DATEDIFF(hour,timein,timeout)AS [Total Hours]

,       ((DATEDIFF(minute,timein,timeout))%60)AS [Total minutes] 

FROM    hrhours

WHERE   timein IS NOT NULL 

AND     (timein BETWEEN CONVERT(DATETIME, '12/1/2007', 101) AND CONVERT(DATETIME, '12/31/2007', 101))

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20407618
SELECT employeeid, storeid,
 CONVERT(VARCHAR,timein,101) AS date,
 CONVERT(VARCHAR,timein,108)AS [clock in],
 CONVERT(VARCHAR,timeout,108) AS [Clock Out],
 convert(int,left(convert(varchar, timeout-timein, 108),2)) AS [Total Hours],
 convert(decimal(19,10),timeout-timein)*24*60 % 60 AS [Total minutes]
FROM hrhours
WHERE timein BETWEEN '2007-12-01' and '2007-12-03'
0
 
LVL 6

Assisted Solution

by:PaultheBroker
PaultheBroker earned 167 total points
ID: 20407687
surely you can do better than:
convert(int,left(convert(varchar, timeout-timein, 108),2)) AS [Total Hours]
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20407718
... as we're at it ... try:

... or wait for SQL 2008 which has a time data type :-)


SELECT  employeeid

,       storeid

,       CONVERT(VARCHAR,timein,101) AS date

,       CONVERT(VARCHAR,timein,108)AS [clock in]

,       CONVERT(VARCHAR,timeout,108) AS [Clock Out]

,       DATEDIFF(hour,timein,timeout)AS [Total Hours]

,       cast(((DATEDIFF(hour,timein,timeout))%60)as varchar(2)) + ':'

+       right('00'+cast(((DATEDIFF(minute,timein,timeout))%60)as varchar(2)),2) + ':'

+       right('00'+cast(((DATEDIFF(second,timein,timeout))%60)as varchar(2)),2)AS [Total time] 

FROM    hrhours

WHERE   timein IS NOT NULL 

AND     (timein BETWEEN CONVERT(DATETIME, '12/1/2007', 101) AND CONVERT(DATETIME, '12/31/2007', 101))
 

--

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20407990
yveau: datediff & http:#20407565
paul: I'm all ears!
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20408007
romeiovasu: I changed it to
WHERE timein BETWEEN '2007-12-01' and '2007-12-03'
because yyyy-mm-dd never needs conversion
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20408628
well, I don't want to burst y'all's bubble, but check this out....
declare @timein datetime, @timeout datetime

set @timein = getdate()

set @timeout = dateadd(ms,12345346,getdate())

print @timein 

print @timeout 
 

print datepart(hour,@timeout -@timein)

print datepart(minute,@timeout -@timein)

Open in new window

0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20408654
@Mitch -for someone that can write SQL code with two '=' signs in the same equation, I'm kinda disappointed in you    ....   :) :)
SELECT  employeeid

,       storeid

,       CONVERT(VARCHAR,timein,101) AS date

,       CONVERT(VARCHAR,timein,108)AS [clock in]

,       CONVERT(VARCHAR,timeout,108) AS [Clock Out]

,       DATEPART(hour,timein-timeout) [Total Hours]

,       DATEPART(minute,timein-timeout) [Total minutes] 

FROM    hrhours

WHERE   timein IS NOT NULL 

AND     timein BETWEEN '20071201' AND '20070101''--if you use 20071231 here, then the whole day of dec 31 will be missing, as this takes it to midnight only....

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 25

Expert Comment

by:imitchie
ID: 20408655
Good one Paul!
My mind was elsewhere, I knew of   datetime-datetime  for a quick diff as opposed to datediff, but forgot datepart.
You might as well post something that the questioner can use entirely
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20408696
..got my in's and out's mixed up (hey - no ones perfect :)  )
SELECT  employeeid

,       storeid

,       CONVERT(VARCHAR,timein,101) AS date

,       CONVERT(VARCHAR,timein,108)AS [clock in]

,       CONVERT(VARCHAR,timeout,108) AS [Clock Out]

,       DATEPART(hour,timeout-timein) [Total Hours]

,       DATEPART(minute,timeout-timein) [Total minutes] 

FROM    hrhours

WHERE   timein IS NOT NULL 

AND     timein BETWEEN '20071201' AND '20070101''--if you use 20071231 here, then the whole day of dec 31 will be missing, as this takes it to midnight only.... 

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20408749
careful with  timein "IS NOT NULL "
that part's redundant. sometimes SQL Server goes crazy when it sees IS NULL or IS NOT NULL criteria, which will cause full table scan.
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20408889
@Mitch - really? .. haven't heard that one...(anyway, its not necessary as timein between will eliminate the NULLS anyway)

As I'm on a roll....my solution will fail if the shift is longer than 24 hours (for a 25 hour shift, the day will be 1, the hour will be 1 - so the above will return just 1 (not 25 !!).  Anyway, that got me to thinking....this is a more robust method.  


hour = floor(datediff(second,@intime,@outtime)/3600)

minute =  (datediff(second,@intime,@outtime)%3600)/60 

Open in new window

0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20408900
...and I've just had a horrible thought, but maybe all that is wanted is this:
hours_minutes = convert(varchar,@outtime-@intime,108)

Open in new window

0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20408931
hmmm - or maybe the answer being looked for is the number of hours expressed as a decimal (so it can be multiplied by a per-hour rate, for instance....)...which is actually almost back at Yveau's original suggestion.

(so the solution is NOT to divide by an integer, as that will return an integer - you have to add a couple of spurious decimal places....._
hours_including_decimal = datediff(minute,@intime,@outtime)/60.00

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20409610
Paul: I think you've finally come around. The problem with Yveau's suggestions are that they don't take in to account the problem with datediff(hour as pointed out in http:#20407565
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20410858
Yes, well I only jumped into this question because of
convert(int,left(convert(varchar, timeout-timein, 108),2)) AS [Total Hours]  !!!!!!!!!
But your point was a good one for the hours column - 1 hour +/- 1 hour isn't very useful.....of course when it comes to the 'minutes' calculation, 4.25 hours +/1 one minute isn't too bad.... (which is what Yveaus original solution actually was) and of course datediff(second,@intime,@outtime)/3600.00 would be accurate +/- one second, which is probably overkill for this application....However, I also appreciated the IS NULL insight into performance - so thanks for that too !!! :)

Anyway, I'm sure Yveau nailed it at the beginning - so the general point for romeiovasu to remember is that MSSQL tends to prefer integers, and will degrade numbers to integers if if possibly can .... Rule of thumb is in mathematical operations like this, always stick some zeroes after the decimal point....even you are dividing by a whole number....
0
 

Author Comment

by:romeiovasu
ID: 20411808
Guys here i tried everyone and it worked fine how should i give point now. becasue everyone is correct.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20414487
May I suggest give to every correct solution. However, we have no idea which one solved your exact problem. Did  you really want hours, minutes the common sense way in two separate columns? i.e.

start: 9:50am
end: 12:10pm
worked [hours]: 2
worked [minutes]: 20
?
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20430466
Split them up as you feel is fair ... we're just happy to help you guys out in the big bad SQL jungle :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL 2005 Srink database in chunks 4 49
sql help 5 52
Set the max value for a column 7 37
Help with stripping out character in SQL LEFT/RIGHT/REPLACE 2 41
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

12 Experts available now in Live!

Get 1:1 Help Now