By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,422 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

Query to get total

parshupooja
100+
P: 159
Hey All,

I have following query which gives me 7-8 record

Expand|Select|Wrap|Line Numbers
  1. select Date,
  2. 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
  3. from timesheet 
  4. where date between Startdate and EndDate
  5.  
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.
Expand|Select|Wrap|Line Numbers
  1. select 
  2. 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
  3. from timesheet 
  4. where date between Startdate and EndDate
  5.  
please help
thanks
Feb 4 '08 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
Hey All,

I have following query which gives me 7-8 record

Expand|Select|Wrap|Line Numbers
  1. select Date,
  2. 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
  3. from timesheet 
  4. where date between Startdate and EndDate
  5.  
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.
Expand|Select|Wrap|Line Numbers
  1. select 
  2. 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
  3. from timesheet 
  4. where date between Startdate and EndDate
  5.  
please help
thanks

do you mean you want it rounded? read more here

-- ck
Feb 4 '08 #2

parshupooja
100+
P: 159
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
Feb 4 '08 #3

Delerna
Expert 100+
P: 1,134
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.
Feb 4 '08 #4

ck9663
Expert 2.5K+
P: 2,878
delerna is right....

((x+y)*60)/60 = (x+y) * 60/60 = (x + ) * 1

they're all equal...
Feb 5 '08 #5

parshupooja
100+
P: 159
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
Feb 5 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.