Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

A Simple Formula to Calculate the ISO Week Number Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 7:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:25 PM
Points: 261, Visits: 913
I like the emphasis on thanking someone for writing a clever equation. Maybe it isn't as popular as y = mx + b, but that one doesn't solve for ISO Week Number - so the right tool for the job, eh?

fwiw, thanks Jeff for another example of how to write an article taking us through the discovery/investigation process (ex: "I've left all of the other interim calculations in place just for comparison purposes and will continue to do so in all other examples that follow.")
Post #1439842
Posted Monday, April 8, 2013 8:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 6,750, Visits: 13,898
How often we see "so how does that work, exactly?" when someone posts an up-to-date solution to their tricky problem. Try explaining this algorithm in the remaining 3 minutes of your lunch break!
Thanks Jeff for taking the time to write - for a brilliant algorithm - an equally brilliant explanation. Bookmarked.


“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1439852
Posted Monday, April 8, 2013 8:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:44 AM
Points: 6,731, Visits: 8,476
It took me some time to crawl back into working position after reading this finding.
Completely astonished by the simplicity of the solution.

Huge thank you for notifying and publishing another great help for the community.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1439858
Posted Monday, April 8, 2013 10:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
Andy Warren (4/8/2013)
Jeff, that is a very nice write up and a very nice job of giving credit where credit is due. The code is interesting, the explanation is just first class, I appreciate the little details like running it in Tempdb to make it easier for someone new.


Thanks for the great feedback, Andy. Heh... yeah... I remember what it was like being new. Good thing I had backups so I could restore the things I deleted by mistake. Hopefully doing these types of experiments in TempDB will protect those that have not yet been burned by an experiment.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1439925
Posted Monday, April 8, 2013 10:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
Mike Dougherty-384281 (4/8/2013)
I like the emphasis on thanking someone for writing a clever equation. Maybe it isn't as popular as y = mx + b, but that one doesn't solve for ISO Week Number - so the right tool for the job, eh?

fwiw, thanks Jeff for another example of how to write an article taking us through the discovery/investigation process (ex: "I've left all of the other interim calculations in place just for comparison purposes and will continue to do so in all other examples that follow.")


Thanks, Mike. I sure do appreciate the feedback on this. Hopefully folks will go to the link I gave and give the inventor about a bazillion +1's because he sure does deserve it.

I was absolutely amazed when I first tested the code to make sure it worked. I just had to tear it apart so I could understand it better. The code in the article is how I actually went through most of it. I'm getting too old to hold very many calculations in mid-air anymore.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1439929
Posted Monday, April 8, 2013 10:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
ChrisM@Work (4/8/2013)
How often we see "so how does that work, exactly?" when someone posts an up-to-date solution to their tricky problem. Try explaining this algorithm in the remaining 3 minutes of your lunch break!
Thanks Jeff for taking the time to write - for a brilliant algorithm - an equally brilliant explanation. Bookmarked.


I sure do appreciate that kind of feedback, Chris. I guess "how it works" is what made things like the Tally Table article so popular in it's day. I didn't invent it... I just 'splained it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1439931
Posted Monday, April 8, 2013 10:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
ALZDBA (4/8/2013)
It took me some time to crawl back into working position after reading this finding.
Completely astonished by the simplicity of the solution.

Huge thank you for notifying and publishing another great help for the community.


Thanks, Johan. I agree. The author of that formula did an amazing job at reducing the formula to such a nice, tight, short, high performance bit o' code. I sat there with my mouth open for an unknown but long period of time when I realized what he'd done.

BTW, when you were crawling around, did you happen to see any of my dust bunnies? After the Tally Oh article, some of them said they were going overseas for an extended vacation.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1439933
Posted Monday, April 8, 2013 10:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:23 AM
Points: 2,397, Visits: 3,411
*removed*


N 56°04'39.16"
E 12°55'05.25"
Post #1439936
Posted Monday, April 8, 2013 10:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:23 AM
Points: 2,397, Visits: 3,411
After further investigation, it seems you don't need the CASE constructor either.
Another simple math trick will do.

SELECT (DATEPART(DAYOFYEAR, DATEDIFF(DAY, -53690, Date) / 7 * 7 - 53687) + 6) / 7




N 56°04'39.16"
E 12°55'05.25"
Post #1439938
Posted Monday, April 8, 2013 10:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
SwePeso (4/8/2013)
Jeff Moden (4/8/2013)

Thanks for stopping by, Peter. Haven't seen you around much and it's a real pleasure to see that fabulous mind at work here, again.

I've been trying to reach you at the email adress I have for you. All emails last year are returned with "Email address unknown".


It hasn't changed in 16 years although some ISP's block it because the old provider that I used to have was famous for spamming folks. I'll send it to you again.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1439940
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse