Hey All,
I have following query which gives me 7-8 record - select Date,
-
abs((cast((Datediff(mi,MorningIn,MorningOut) + Datediff(mi,AfternoonIn,Afternoonout))/60 as varchar(5))+ '.' + RIGHT('0' + cast((Datediff(mi,MorningIn,MorningOut) + Datediff(mi,AfternoonIn,Afternoonout))%60 as varchar(2)), 2))) as TotalHours
-
from timesheet
-
where date between Startdate and EndDate
-
Now I want total of TotalHours Column. I tried doing sum but it does not give me accurate information in the sense to time. for ex: it gives total 2.15+2.45 as 4.60 whereas it should be 5.00. - select
-
sum(abs((cast((Datediff(mi,MorningIn,MorningOut) + Datediff(mi,AfternoonIn,Afternoonout))/60 as varchar(5))+ '.' + RIGHT('0' + cast((Datediff(mi,MorningIn,MorningOut) + Datediff(mi,AfternoonIn,Afternoonout))%60 as varchar(2)), 2)))) as TotalHours
-
from timesheet
-
where date between Startdate and EndDate
-
please help
thanks
5 1322
Hey All,
I have following query which gives me 7-8 record - select Date,
-
abs((cast((Datediff(mi,MorningIn,MorningOut) + Datediff(mi,AfternoonIn,Afternoonout))/60 as varchar(5))+ '.' + RIGHT('0' + cast((Datediff(mi,MorningIn,MorningOut) + Datediff(mi,AfternoonIn,Afternoonout))%60 as varchar(2)), 2))) as TotalHours
-
from timesheet
-
where date between Startdate and EndDate
-
Now I want total of TotalHours Column. I tried doing sum but it does not give me accurate information in the sense to time. for ex: it gives total 2.15+2.45 as 4.60 whereas it should be 5.00. - select
-
sum(abs((cast((Datediff(mi,MorningIn,MorningOut) + Datediff(mi,AfternoonIn,Afternoonout))/60 as varchar(5))+ '.' + RIGHT('0' + cast((Datediff(mi,MorningIn,MorningOut) + Datediff(mi,AfternoonIn,Afternoonout))%60 as varchar(2)), 2)))) as TotalHours
-
from timesheet
-
where date between Startdate and EndDate
-
please help
thanks
do you mean you want it rounded? read more here
-- ck
First I want to convert totalHours to minutes and than sum it.For ex:
Date------TotalHours
9/22/2007--12.15
9/23/2008---14.30
now I want sum of totalHours as 16.45. the way to get is ((12.15+14.30) * 60)/60
but i don'tknow how to put it in query
thanks
do you mean you want it rounded? read more here
-- ck
you can add that outside the sum
eg (sum(your bit here)*60)/60
However that dosn't seem mathematically right to me because if you multiply something by a value and then divide the answer by the same value then you end up with what you originally had.
delerna is right....
((x+y)*60)/60 = (x+y) * 60/60 = (x + ) * 1
they're all equal...
sorry I wanted calculation like this for below mentioned values.
12.15
14.45
add the values before decimal andmultiply by 60 which is (12+14)*60 and than add the values after decimal(15+45).
Now add them together and finally divie by 60 gives you 27
I am unable to understand how shd i do this in sql query?
Thanks
Sign in to post your reply or Sign up for a free account.
Similar topics
by: unixman |
last post by:
As usual, it is 2:00am, and I'm pulling my hair out, finally resorting to
posting in the newsgroups for help. :)
Simple problem, in theory.
Given table "map":
CREATE TABLE map (
entry_id...
|
by: Sondra Wilson |
last post by:
I have created a sorted query. The total(records) I get in the query is
correct. My problem lies with getting that total into my report.
I use count to insert the total number of records in my...
|
by: ED |
last post by:
I am attempting to to write a query that has a numerous nested IIf
statements. The problem that I am having is that it is to long of a
query to be built in design mode and when I build it in sql...
|
by: Jamie Pittman via AccessMonster.com |
last post by:
I am having trouble bellow wit this query. I have the total regular hours and the overtime. The problem is that if it is 8 hours and under, I need it to show as regular hours. Any thoughts?
...
|
by: ????? |
last post by:
I have an access query which gets data from a number of different
tables. Although the tables have primary key fields, the order in which
the records are returned means that none of these are in...
|
by: Brian |
last post by:
Hello,
Basically, I'm running a query on a form's activation, and I'd like to
have the results of the query be placed into other fields on the same
form automatically.
Does anybody know how...
|
by: Ivan Carey |
last post by:
How can a query display multiple fields with diferent condition on the same
field
example
I have a field name of reason and a field name of duration. I would like to
display 2 fileds of total...
|
by: Flic |
last post by:
Hi,
I have a basic db that I access with MySQL query browser. Everything
seems fine to me but I am using this db as part of a php shopping
basket and when I try to add an item I get:
Notice:...
|
by: Richard Hollenbeck |
last post by:
I hope this isn't too confusing.
The following query runs pretty fast by itself, but when I want to use it in
a report (pasted below the query), it takes at least fifteen seconds to run!
Then I...
|
by: lorirobn |
last post by:
Hi,
I have a report displaying items that are missing from a room. I
created 2 queries, the first getting the items IN the room, and the
second being an "unmatched" query that references the...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |