## A Simple Formula to Calculate the ISO Week Number

 Author Message Mike Dougherty-384281 Ten Centuries Group: General Forum Members Points: 1400 Visits: 944 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.") ChrisM@Work SSC Guru Group: General Forum Members Points: 96530 Visits: 20680 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps ALZDBA SSC Guru Group: General Forum Members Points: 70356 Visits: 9210 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. JohanDont drive faster than your guardian angel can fly ...but keeping both feet on the ground wont 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 :-DNeed a bit of Powershell? How about thisWho am I ? Sometimes this is me but most of the time this is me Jeff Moden SSC Guru Group: General Forum Members Points: 506049 Visits: 44261 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 506049 Visits: 44261 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 506049 Visits: 44261 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. :-D --Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 506049 Visits: 44261 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. :-P --Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs SwePeso SSC-Insane Group: General Forum Members Points: 22095 Visits: 3433 *removed* N 56°04'39.16"E 12°55'05.25" SwePeso SSC-Insane Group: General Forum Members Points: 22095 Visits: 3433 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" Jeff Moden SSC Guru Group: General Forum Members Points: 506049 Visits: 44261 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs