Week Number

[JukesK]

how can i show the week number and corresponding dates using the tax year
i tried Week(date, 0) but that shows 1-1-2012 as week 0

Eg; qc_data.date holds the date a qc inspection took place.
if i want to show that the first full week in April this year (2 april 2012 - 8 april 2012) as week 1

ie
Week||Date Range||Equipment ID||Result
1||2 april 2012 - 8 april 2012|| EID:100||Pass
2||9 april 2012 - 15 april 2012||EID:100||Fail

Many thanks in advance
KeeF

[pritaeas]

Here is what mode does.

Why don't you calculate the week for your april date, and use it to shift the values returned by WEEK().

[JukesK]

Thanks for the quick response and please forgive me for being a noob.
I dont understand how the mode works...

as i understand it, mode returns a week number and allows you to start the week with monday or sunday, but how can i offset it to start at the first full week in april this year?

[pritaeas]

mode only specifies how the week number is calculated. It says so in the first paragraph.

Subtract from a date's WEEK value the WEEK value for April 2nd and add one.

[JukesK]

so..
SELECT WEEK(Date,0) As 'Week Number)', WEEK('2012-04-02',0) as 'april', sum(WEEK(Date,0) - WEEK('2012-04-02',0)) As 'Fiscal'
FROM qc_data

Returns
Week Number||april||Fiscal
43||14||29

so there this weeks calander week is 43 but the financial week according to the company would be week 30!!

Cheers Pritaeas you've been a great help if i understood it right..

[pritaeas]

Make sure you check it's correctness. For months before April the result can be negative, so you have to adjust those too, based on the number of weeks from the previous year.