calculate weeks within quarter

[Rick Stahl]

DatePart("q",[name]) works for quarters for the year (total is 4)

DatePart("ww",[name]) works for weeks of the year (total is 52)

How can I determine weeks within the quarter (should be total of 13) ?

Thanks.

 

[pietlinden]

DatePart("q",[name]) works for quarters for the year (total is 4)

DatePart("ww",[name]) works for weeks of the year (total is 52)

How can I determine weeks within the quarter (should be total of 13) ?

Thanks.

use DateDiff. It'll do that for you.

 

[Rick Stahl]

Thanks. I am not familiar with this function. Could you please help me
with how this should be set up. My guess is the interval would be 7 but not
sure what date1 or date2 should be. I need to know the number of the week
in each of the different quarters. The syntax is:
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

DatePart("q",[name]) works for quarters for the year (total is 4)

DatePart("ww",[name]) works for weeks of the year (total is 52)

How can I determine weeks within the quarter (should be total of 13) ?

Thanks.

use DateDiff. It'll do that for you.

 

[Guest]

Rick,

I'm not sure I understand what you are trying to do. Do you want to run a
query and have it return the starting day of the week, or just the
weeknumbers that fall in a particular quarter?

If you had a table (tbl_Numbers) with the numbers 0 through 365 in a field
called Number you could write a query that looks something like:

SELECT DateAdd("d",[Number],#1/1/2007#) AS SomeDate,
DatePart("q",DateAdd("d",[Number],#1/1/2007#)) AS [Quarter#],
DatePart("m",DateAdd("d",[Number],#1/1/2007#)) AS [Month#],
DatePart("ww",DateAdd("d",[Number],#1/1/2007#)) AS [Week#],
Weekday(DateAdd("d",[Number],#1/1/2007#)) AS WeekDay
FROM tbl_Numbers
WHERE DatePart("q",DateAdd("d",[Number],#1/1/2007#))=[Enter quarter]
AND Weekday(DateAdd("d",[Number],#1/1/2007#))=1
ORDER BY DateAdd("d",[Number],#1/1/2007#);

HTH
Dale

 

[Rick Stahl]

What I have is weekly payroll data that also contains attendance/performance
which I need to monitor every week. At the end of each quarter a bonus may
or may not be granted depending on attendance/performance. The way I need
to classify this is based on quarter and week. Since there are 52 weeks in
a year and 4 quarters, then each quarter would have 13 weeks. Therefore,
Quarter 1 would contain weeks 1 to 13 (Q1 Wk1 to Q1 Wk13) but so will the
other 3 quarters; for example Quarter 3 would contain weeks 1 to 13 (Q3 Wk1
to Q3 Wk13). Thanks.


I need to keep track of

Rick,

I'm not sure I understand what you are trying to do. Do you want to run a
query and have it return the starting day of the week, or just the
weeknumbers that fall in a particular quarter?

If you had a table (tbl_Numbers) with the numbers 0 through 365 in a
field
called Number you could write a query that looks something like:

SELECT DateAdd("d",[Number],#1/1/2007#) AS SomeDate,
DatePart("q",DateAdd("d",[Number],#1/1/2007#)) AS [Quarter#],
DatePart("m",DateAdd("d",[Number],#1/1/2007#)) AS [Month#],
DatePart("ww",DateAdd("d",[Number],#1/1/2007#)) AS [Week#],
Weekday(DateAdd("d",[Number],#1/1/2007#)) AS WeekDay
FROM tbl_Numbers
WHERE DatePart("q",DateAdd("d",[Number],#1/1/2007#))=[Enter quarter]
AND Weekday(DateAdd("d",[Number],#1/1/2007#))=1
ORDER BY DateAdd("d",[Number],#1/1/2007#);

HTH
Dale

------------------------------
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

DatePart("q",[name]) works for quarters for the year (total is 4)

DatePart("ww",[name]) works for weeks of the year (total is 52)

How can I determine weeks within the quarter (should be total of 13) ?

Thanks.

 

[Dale Fye]

So, what your asking is how to convert a date, which you already have in
your payroll database into a quarter and week, is that correct? If so, I
would consider doing what I mentioned in my previous post (although I've
slightly modified it below). Assuming you create tbl_Numbers as described
in my original post, this query gives you every day in the current year, its
quarter, and week within that quarter.

SELECT DateSerial(Year(Date()),1,[number]) AS SomeDate,
DatePart("q",[SomeDate]) AS [Quarter#],
DatePart("ww",[SomeDate]) AS [Week#],
[Week#]-([Quarter#]-1)*13 AS [AdjWeek#]
FROM qry_NumThru999
ORDER BY DateSerial(Year(Date()),1,[number]);

You can then join this query to your payroll table on the SomeDate field to
determine the Quarter# and AdjWeek# for any date.

HTH
Dale

What I have is weekly payroll data that also contains
attendance/performance which I need to monitor every week. At the end of
each quarter a bonus may or may not be granted depending on
attendance/performance. The way I need to classify this is based on
quarter and week. Since there are 52 weeks in a year and 4 quarters, then
each quarter would have 13 weeks. Therefore, Quarter 1 would contain
weeks 1 to 13 (Q1 Wk1 to Q1 Wk13) but so will the other 3 quarters; for
example Quarter 3 would contain weeks 1 to 13 (Q3 Wk1 to Q3 Wk13).
Thanks.


I need to keep track of

Rick,

I'm not sure I understand what you are trying to do. Do you want to run
a
query and have it return the starting day of the week, or just the
weeknumbers that fall in a particular quarter?

If you had a table (tbl_Numbers) with the numbers 0 through 365 in a
field
called Number you could write a query that looks something like:

SELECT DateAdd("d",[Number],#1/1/2007#) AS SomeDate,
DatePart("q",DateAdd("d",[Number],#1/1/2007#)) AS [Quarter#],
DatePart("m",DateAdd("d",[Number],#1/1/2007#)) AS [Month#],
DatePart("ww",DateAdd("d",[Number],#1/1/2007#)) AS [Week#],
Weekday(DateAdd("d",[Number],#1/1/2007#)) AS WeekDay
FROM tbl_Numbers
WHERE DatePart("q",DateAdd("d",[Number],#1/1/2007#))=[Enter quarter]
AND Weekday(DateAdd("d",[Number],#1/1/2007#))=1
ORDER BY DateAdd("d",[Number],#1/1/2007#);

HTH
Dale

------------------------------
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

DatePart("q",[name]) works for quarters for the year (total is 4)

DatePart("ww",[name]) works for weeks of the year (total is 52)

How can I determine weeks within the quarter (should be total of 13) ?

Thanks.