Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » How to Calculate Total Monthly time worked
Add to briefcase
16 posts, Page 2 of 2
««12

How to Calculate Total Monthly time worked Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
autoexcrement
Posted Thursday, December 09, 2010 9:37 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 52, Visits: 189
Wayne, yes, thank you. That explains it completely. So, using your example of a time "80101", the code would (working from the inside out) transform it thusly:
80101 (original)
10108 (reversed)
10:108 (stuff 3rd position with colon)
10:10:8 (stuff 6th position with colon)
8:01:01 (re-reverse)

So, all that remains to be explained is...are you guys some kind of freaking sql wizards or what? What hope is there for us normal people with you lot lurking around? :)

Thanks, I continue to be in awe at the creativity, intelligence, and amazing patience and tutelage available on this site.

EDIT: P.S. Your double-stuffed remix is tasty too!

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1032826
Evil Kraig F
Posted Friday, December 10, 2010 2:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 5,688, Visits: 6,142
autoexcrement (12/9/2010)

Wayne, yes, thank you. That explains it completely. So, using your example of a time "80101", the code would (working from the inside out) transform it thusly:
80101 (original)
10108 (reversed)
10:108 (stuff 3rd position with colon)
10:10:8 (stuff 6th position with colon)
8:01:01 (re-reverse)


On the money.


So, all that remains to be explained is...are you guys some kind of freaking sql wizards or what? What hope is there for us normal people with you lot lurking around? :)

Thanks, I continue to be in awe at the creativity, intelligence, and amazing patience and tutelage available on this site.



Plenty of hope, I've been at this ten years or so, Wayne longer. Give yourself time. Eventually you remember the functions offhand and then start coming up with strange and interesting ways to twist them together like burnt pretzels. :)

Oh, and thanks.

Btw, regarding the brain? Sorry, no can do. After the amount of alcohol I've poisoned it with over the years I'm afraid it'll spontaneously combust if it is allowed an oxygen source.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA
Post #1032879
WayneS
Posted Friday, December 10, 2010 7:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
autoexcrement (12/9/2010)
Wayne, yes, thank you. That explains it completely. So, using your example of a time "80101", the code would (working from the inside out) transform it thusly:
80101 (original)
10108 (reversed)
10:108 (stuff 3rd position with colon)
10:10:8 (stuff 6th position with colon)
8:01:01 (re-reverse)

So, all that remains to be explained is...are you guys some kind of freaking sql wizards or what? What hope is there for us normal people with you lot lurking around? :)

Thanks, I continue to be in awe at the creativity, intelligence, and amazing patience and tutelage available on this site.

EDIT: P.S. Your double-stuffed remix is tasty too!


One thing to note: The order that you do the STUFF calls can change what you're doing.
Above, you inserted the colons from left to right. What this means is that you have to account for that colon in the subsequent calls to STUFF.

The alternative is to insert them from right-to-left. Showing just them, it would be:
1010:8 (stuff 5th position with colon)
10:10:8 (stuff 3rd position with colon)
You'll find a lot of folks will do it this way just to take that variable out of the way.

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1033013
WayneS
Posted Friday, December 10, 2010 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
autoexcrement (12/9/2010)
So, all that remains to be explained is...are you guys some kind of freaking sql wizards or what?
But of course!
What hope is there for us normal people with you lot lurking around? :)
Well, you see, that's the whole point. By lurking around, you are hopefully acquiring this knowledge transfer that's going on. Reinforce it by practice (aka, help out here on the forums). Don't get upset if what you come up with isn't "the best"; instead, learn from it. (If you look into the forums here, you'll find that most learning really goes on after the first answer was posted, with even well-versus folks learning from others. Even "the guru himself" (Jeff) recently learned a new safety check that can be applied to the infamous "quirky update" to guarantee things were processed correctly. (The learning never stops.....)

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1033017
WayneS
Posted Friday, December 10, 2010 7:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
Craig Farrell (12/10/2010)
Plenty of hope, I've been at this ten years or so, Wayne longer. Give yourself time. Eventually you remember the functions offhand and then start coming up with strange and interesting ways to twist them together like burnt pretzels. :)


And, now that you understand what's going on here... you'll never forget it. So, you've been exposed to dateadd(datediff) to get the first of the month (change the datepart to get the first of any period!), and using reverse/stuff/reverse. Not too bad for one thread!

EDIT: Check out the common date/time routines link in my signature!

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1033021
autoexcrement
Posted Friday, December 10, 2010 11:44 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 52, Visits: 189
Nice, and thanks for giving me an inferiority complex about my signature now... :) Seriously, though, you guys are the best. I'll refrain from offering forum advice for awhile, but hopefully will get to that point soonish.

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1033165
« Prev Topic | Next Topic »

Add to briefcase
16 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.