Calculating time difference

Question

Calculating time difference

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

... was able to achieve this with code:
convert(int,((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(hour,cat2_hours) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,cat2_hours) * 60) + sum(datepart(second,Timenetin) * 1) + sum(datepart(second,cat2_hours) * 1))))/3600 -
((sum(datepart(hour,Monhours) * 3600) + sum(datepart(hour,Tuehours) * 3600) + sum(datepart(hour,Wedhours) * 3600) + sum(datepart(hour,Thuhours) * 3600) + sum(datepart(hour,Frihours) * 3600)
+ sum(datepart(minute,Monhours) * 60) + sum(datepart(minute,Tuehours) * 60) + sum(datepart(minute,Wedhours) * 60) + sum(datepart(minute,Thuhours) * 60) + sum(datepart(minute,Frihours) * 60) +
sum(datepart(second,Monhours) * 1) + sum(datepart(second,Tuehours) * 1) + sum(datepart(second,Wedhours) * 1) +
sum(datepart(second,Thuhours) * 1) + sum(datepart(second,Frihours) * 1)))/18000 As Hrs_Diff,

Chop some garlic in, add some chilli, cramped boiled egg, green beans and prawns, stir-fry with one spoon of veg. oil and two spoons of soya sauce.

Portion of Singaporean Noodles is ready to be boxed :hehe::hehe::hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

pwalter83 SSChampion Points: 14575 More actions · Answer 2

Eugene Elutin (4/27/2012)
... was able to achieve this with code:
convert(int,((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(hour,cat2_hours) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,cat2_hours) * 60) + sum(datepart(second,Timenetin) * 1) + sum(datepart(second,cat2_hours) * 1))))/3600 -
((sum(datepart(hour,Monhours) * 3600) + sum(datepart(hour,Tuehours) * 3600) + sum(datepart(hour,Wedhours) * 3600) + sum(datepart(hour,Thuhours) * 3600) + sum(datepart(hour,Frihours) * 3600)
+ sum(datepart(minute,Monhours) * 60) + sum(datepart(minute,Tuehours) * 60) + sum(datepart(minute,Wedhours) * 60) + sum(datepart(minute,Thuhours) * 60) + sum(datepart(minute,Frihours) * 60) +
sum(datepart(second,Monhours) * 1) + sum(datepart(second,Tuehours) * 1) + sum(datepart(second,Wedhours) * 1) +
sum(datepart(second,Thuhours) * 1) + sum(datepart(second,Frihours) * 1)))/18000 As Hrs_Diff,
Chop some garlic in, add some chilli, cramped boiled egg, green beans and prawns, stir-fry with one spoon of veg. oil and two spoons of soya sauce.
Portion of Singaporean Noodles is ready to be boxed :hehe::hehe::hehe:

Whats that supposed to mean ? I can only hope its not sarcastic.:-D

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

It's not, just this huge set of sum's and brackets on Friday did make me dizzy. Time for a pint!

😀

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Eugene Elutin (4/27/2012)
It's not, just this huge set of sum's and brackets on Friday did make me dizzy. Time for a pint!
😀

Stop that! It's two hours to Beer O'Clock!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Eugene Elutin (4/27/2012)
It's not, just this huge set of sum's and brackets on Friday did make me dizzy. Time for a pint!
😀

Careful with that axe, Eugene

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden