459,654 Members | 1,422 Online
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

 100+ P: 159 Hey All, I have following query which gives me 7-8 record Expand|Select|Wrap|Line Numbers 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. Expand|Select|Wrap|Line Numbers 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 Feb 4 '08 #1
5 Replies

 Expert 2.5K+ P: 2,878 Hey All, I have following query which gives me 7-8 record Expand|Select|Wrap|Line Numbers 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. Expand|Select|Wrap|Line Numbers 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 Feb 4 '08 #2

 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

 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

 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

 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