setres.blogg.se

1. #ORACLE WEEK NUMBER STARTING SUNDAY HOW TO#
2. #ORACLE WEEK NUMBER STARTING SUNDAY SERIAL NUMBERS#
3. #ORACLE WEEK NUMBER STARTING SUNDAY SERIAL#

Very fortunately for me, I had misplaced my previously written T-SQL solution to solve for ISO Week Numbers and decided to do a quick web search to find one instead of trying to find my own.

#ORACLE WEEK NUMBER STARTING SUNDAY HOW TO#

How to build a “Broadcast Calendar” will have to wait until another article, though, because I found something else that’s very cool. SELECT was working on a thread that asked how to create a "Broadcast Calendar" ( ) and, to make a much longer story shorter, it dawned on me that I might be able to solve the problem using a calculation similar to an "ISO Week Number" calculation. The modifications only cause a scant increase of a dozen or so milliseconds over a million row test so it's still a competitor with the ISOWEEK date-part of SQL Server 2008 and up and still works in earlier versions. Here's that code and that's the one I recommend that you use if you have the need (< SQL Server 2008). SELECT verified the modified formula and made an additional optimization that removed one of the additions which saves a bit on CPU time.

#ORACLE WEEK NUMBER STARTING SUNDAY SERIAL#

I found the fix and that was to add the difference in dates to the "base date" instead of using the difference in dates as a date serial number.

I had the article taken "offline" until I could fix it and test it.

#ORACLE WEEK NUMBER STARTING SUNDAY SERIAL NUMBERS#

This works just fine for dates greater than or equal to because that difference in days matches SQL Server's underlying date serial numbers but it doesn't work correctly for dates that have a date serial number less than 0 (). The problem is with "Step 3 – Convert the Date Serial to the "Day of the Year" where a difference in days is used in the calculation instead of the actual date. The original formula has a problem that I didn't originally test for and, that is, if you use the formula for dates earlier than, the calculation of the correct ISOWeek is by chance rather than by plan. This article is no exception and I thank Peter Larsson who discovered that there was a problem. Week 3 1020304050.I've made no changes in the article starting with the "Introduction" because it still correctly describes the original formula written by "t-clausen.dk".Īs is the nature of this fine community, if there's a problem with something in an article, it is quickly ferreted out by those who participate in the discussion. What I want is something around this idea: ORDER_TOT_NET | ORDER_TOT_WGHT | ORDER_TOT_QTY | ORDER_TOT_SQM Where ORDER.ORDER_NO = ORDER_TOTALS.ORDER_NOĪnd ORDER.DATE Between '14-DEC-15' And '18-DEC-15Īs you can see this will show results for orders that were created last week however I do not understand now how to make it more advanced so that we can get each week of the current year for example. Sum(ORDER_TOTALS.ORDER_TOT_SQM) As "ORDER_TOT_SQM" Sum(ORDER_TOTALS.ORDER_TOT_QTY) As "ORDER_TOT_QTY", Sum(ORDER_TOTALS.ORDER_TOT_WGHT) As "ORDER_TOT_WGHT", Here is the SQL query that I've created: Select Sum(ORDER_TOTALS.ORDER_TOT_NET) As "ORDER_TOT_NET", I am building a report to show order statistics by week within our company.