Gregorian Calendar vs. Subscriber Calendar week calculations (1 Viewer)

[Portucale]

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

 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.

 

[plog]

First, the Gregorian/fiscal year issue you think you have is a red herring, its not really relevant. Because you just need the last 13 weeks of data, you could do that no matter what the time frame is...

...along as you had an actual date. But you don't. So, you cannot do this with the way you have stored your date reference (yyyyww). You need to convert that to an actual date which would allow you to use the DateDiff function.

If you can do that, then you will be able to determine which records fall into the previous 13 weeks. Otherwise this is a lost cause. You can search my previous posts for lengthy discussions about how weeks don't go evenly into years (365/7 = 52.147) which makes trying to count backwords using just weeks impossible.

 

[Portucale]

Thanks Plog,

Understand what you mean, I had some issues in excel for the Gregorian calendar hence I used a ISO formula, but not relevant in this case .

Now for this case, I do have a Calendar table running also, can you help me in getting a statement which would get the first day for a given week, let's say week 201318 first date is Nov 25th and last date is Nov 31st, any idea how could I bring the first date?
I could then populate this into my Temp Table.

Thanks for the help,

 

[Portucale]

Hi Plog,
Think I answer my question

I created the following:

(SELECT Min(Tbl_Calendar.Date) FROM Tbl_Calendar WHERE Tbl_Telephony_All.WeekNbr =Tbl_Calendar.WeekNbr  ) AS [Date] [CODE]

and seems to work, don't know how... but it gives me the first date for that week

Any different solution?

Cheers,

 

[plog]

let's say week 201318 first date is Nov 25th and last date is Nov 31st, any idea how could I bring the first date?

I don't really understand what you are trying to do. You explicitly declared day 1 of week 1, so there is nothing to calculate.

Like I said, week numbers get screwy--especially for the last day of the year you have defined, double especially on the last day of a leap year that you have defined. For this though, you don't need week numbers, you just need to take a date and count backwards 91 days. By using year/week numbers you are adding a layer of complexity that does nothing for you.

 

[Portucale]

I know sometimes I confuse myself, all is working now, as you mention I declare the first day for a given week so I should be OK.

Thanks again