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
Page 1 / 1
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. .
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. .
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
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?
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