I have a database with weeks represented as yyyyww (201323), I need to extract the information for the previous 13 weeks, so far so good but the difficulty is that our calendar is different from the Gregorian one, as the year starts at the end of June e.g.
2013/14 Week 1 is from Friday, 28th June 2013 to Thursday, 4th July 2013, so as you can gather not only the week numbers will not correspond to the standard ISO the week also do not start and end on the normal Sun-Sat or Mon-Sun.
Is there anyone which come across to a similar issue and if so how can it be resolved as I do not have any idea, currently I do have a WHERE statement [code] WHERE (((Tbl_Telephony_All.Week)= (SELECT Max(Tbl_Telephony_All.Week) AS [WeekNo] FROM Tbl_Telephony_All)))
[\code] which would extract the information for the MAX ‘week’, however as I need to append the data for a 13 week period into another table I am a bit stock.
the year starts at the end of June e.g. 2013/14 Week 1 is from Friday, 28th June 2013 to Thursday, 4th July 2013, so as you can gather not only the week numbers will not correspond to the standard ISO the week also do not start and end
on the normal Sun-Sat or Mon-Sun.
And when does the first week of 2014 start? And 2015, 2016 ect.....? Basicly, what is the logic behind this?
vitordf
I have a database with weeks represented as yyyyww (201323),
In general, when you do this kind of calculations, you better store thes values as dates. So instead of storing 201323, I would store 2013/11/29 (the first day of week 23) Calculating a period of 13 weeks in SQL is just a matter of using the dataadd method
The conversion from/to a weeknumber only takes place in your code (C# or VB.NET). But then you need the logic behind it
We would definately need more information on this. As hans_v said it is better to use datetime instead of strings if you want to perform datetime calculations. What is the logic behind using such a calendar? What is the expectation? Do you need help
writing the LINQ query to append data for a 13 week period?
Member
1 Points
16 Posts
Gregorian Calendar vs. Subscriber Calendar week calculations
Dec 12, 2013 05:32 AM|vitordf|LINK
I have a database with weeks represented as yyyyww (201323), I need to extract the information for the previous 13 weeks, so far so good but the difficulty is that our calendar is different from the Gregorian one, as the year starts at the end of June e.g. 2013/14 Week 1 is from Friday, 28th June 2013 to Thursday, 4th July 2013, so as you can gather not only the week numbers will not correspond to the standard ISO the week also do not start and end on the normal Sun-Sat or Mon-Sun.
Is there anyone which come across to a similar issue and if so how can it be resolved as I do not have any idea, currently I do have a WHERE statement [code] WHERE (((Tbl_Telephony_All.Week)= (SELECT Max(Tbl_Telephony_All.Week) AS [WeekNo] FROM Tbl_Telephony_All))) [\code] which would extract the information for the MAX ‘week’, however as I need to append the data for a 13 week period into another table I am a bit stock.
Any help is very much appreciated.
I believe this is the wrong forun so I re post this at http://answers.microsoft.com/en-us/office/forum/office_2010-access/gregorian-calendar-vs-subscriber-calendar-week/ac20dff2-6e13-45eb-bc9a-99015223120e?tm=1386844945291
Star
12777 Points
1635 Posts
Re: Gregorian Calendar vs. Subscriber Calendar week calculations
Dec 12, 2013 09:53 PM|Terry Guo - MSFT|LINK
Hi,
I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.
Best Regards,
Terry Guo
All-Star
25756 Points
7019 Posts
Re: Gregorian Calendar vs. Subscriber Calendar week calculations
Dec 13, 2013 04:25 AM|hans_v|LINK
And when does the first week of 2014 start? And 2015, 2016 ect.....? Basicly, what is the logic behind this?
In general, when you do this kind of calculations, you better store thes values as dates. So instead of storing 201323, I would store 2013/11/29 (the first day of week 23) Calculating a period of 13 weeks in SQL is just a matter of using the dataadd method The conversion from/to a weeknumber only takes place in your code (C# or VB.NET). But then you need the logic behind it
None
0 Points
4 Posts
Re: Gregorian Calendar vs. Subscriber Calendar week calculations
Dec 23, 2013 06:14 PM|kunalmundada|LINK
Hi,
We would definately need more information on this. As hans_v said it is better to use datetime instead of strings if you want to perform datetime calculations. What is the logic behind using such a calendar? What is the expectation? Do you need help writing the LINQ query to append data for a 13 week period?
Thanks,
Kunal