Hi, I have a bunch of data in SQL with date stamps and I have had a request to convert the data into our 4 weekly cycles. eg. any dates from 7th Jan to 13th Jan 2017 are a week 1, from 14th Jan to 20th Jan 2017 are Week2 ... Week 3, Week 4 and then 4th Feb to 10th Feb being Week 1 again ongoing. The data continues to be added to and they want me to be able to continue to run this workspace into the future and keep working out on the new data which week is it falling into. Any thoughts
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
- 7843 replies
Hi @garryp, you can convert a date YYYYmmdd to Sunday or Monday-based week of year (00 - 53) with the DateTimeConvertor (Output Format: %U or %W). I think you can calculate desired 4 weekly cycle values (iteration for 1 to 4) based on these values.
However, 2017-01-07 was Saturday. Since the week of year is Sunday or Monday-based, the operations could be a little complicated if required 4 weekly cycle should start with Saturday. .
+1- Participant
- 3 replies
Hi @garryp, you can convert a date YYYYmmdd to Sunday or Monday-based week of year (00 - 53) with the DateTimeConvertor (Output Format: %U or %W). I think you can calculate desired 4 weekly cycle values (iteration for 1 to 4) based on these values.
However, 2017-01-07 was Saturday. Since the week of year is Sunday or Monday-based, the operations could be a little complicated if required 4 weekly cycle should start with Saturday. .
- 7843 replies
Hi @garryp, you can convert a date YYYYmmdd to Sunday or Monday-based week of year (00 - 53) with the DateTimeConvertor (Output Format: %U or %W). I think you can calculate desired 4 weekly cycle values (iteration for 1 to 4) based on these values.
However, 2017-01-07 was Saturday. Since the week of year is Sunday or Monday-based, the operations could be a little complicated if required 4 weekly cycle should start with Saturday. .
@int(<elapsed days> / 7) % 4 + 1
+44- Influencer
- 3434 replies
SELECT a."Date", date(a."Date",'start of month','weekday 6') as first_saturday from "Output" aThe inlinequerier can give you the first Saturday of the month for a given date and you can work out the week number based on the difference between these two dates.
Edit: looking at your comments though, if a week does not start consistently on the first saturday of the month this probably isn't going to work. What is the logic that makes 30th December 2017 Week 1 of January?
+1- Participant
- 3 replies
SELECT a."Date", date(a."Date",'start of month','weekday 6') as first_saturday from "Output" aThe inlinequerier can give you the first Saturday of the month for a given date and you can work out the week number based on the difference between these two dates.
Edit: looking at your comments though, if a week does not start consistently on the first saturday of the month this probably isn't going to work. What is the logic that makes 30th December 2017 Week 1 of January?
Hi, the weeks are not linked to years, they are an ongoing 4 week cycle which started years ago.
I have made a work around with a table with all the dates and what week they fall into and just look up in that to find the week that the job has fallen into.
Thanks for your assistance
Hi @garryp
with FME 2017 DateTimeCalculator you can find how many days there are between your date and the beginning of the four-week cycle date. With this value, you can calculate the date week number as:
@floor((@Value(_days)-@floor(@Value(_days)/28)*28)/7)+1