I'm trying to separate weeks from timestamp per quarter so it should be between 1-13 week per quarter so I used function week() but it takes between 1-52 week as whole year so I made it to be divided by function of quarter like below
select Week (EVENTTIMESTAMP) / QUARTER (EVENTTIMESTAMP) from KAP
The thing here that results aren't accurate; for example it shows:
time stamp 2014-07-06 12:13:03.018
week number 9
which isn't correct because July is first month in Q3 and it's in the 6 days so it should be 1 week from Q3 not 9.
Any suggestion where it go wrong?
You want something like WEEK modulo 13 to get week number within a quarter. You will have to tinker with 'modulo 13 yields 0..12' by adding or subtracting one at appropriate points.
Some minimal Google searching using 'ibm db2 sql modulo' yields DB2 MOD function:
The MOD function divides the first argument by the second argument and returns the remainder.
Hence MOD(WEEK(...), 13), except you probably need MOD(WEEK(...)-1, 13) + 1, as intimated already.
You may need to watch for what the WEEK() function does at year ends:
The WEEK function returns an integer in the range of 1 to 54 that represents the week of the year. The week starts with Sunday, and January 1 is always in the first week.
I'm curious about how they can come up with week 54. I suppose it requires 1st January to be a Saturday (so 2nd January is the start of week 2) of a leap year, as in 2000 and 2028. Note that week 53 and (occasionally) week 54 will show up as weeks 1 and 2 of Q5 unless you do something. Also, Saturday 2000-03-25 would be the end of Q1 and Sunday 2000-03-26 would be the start of Q2 under the regime imposed by the WEEK() function and a simple MOD(WEEK(...), 13) calculation. You're likely to have to tune this to meet your real requirements.
There's also the WEEK_ISO() function:
The WEEK_ISO function returns an integer in the range of 1 to 53 that represents the week of the year. The week starts with Monday and includes seven days. Week 1 is the first week of the year that contains a Thursday, which is equivalent to the first week that contains January 4.
Note that under the ISO scheme, the 3rd of January can be in week 52 or 53 of the previous year, and the 29th of December can be in week 1 of the next year. Curiously, there doesn't seem to be a YEAR_ISO() function to resolve such ambiguities.
In a data warehouse, the proper solution to this is to create a time dimension that contains static mappings for days/weeks/months/quarters/years. This provides the ability to define these based on your business' fiscal calendar (if it is not following on the calendar year).
See: http://www.kimballgroup.com/1997/07/10/its-time-for-time/ for more information.
