Adding a week number to an existing table

[Judith]

I have been asked to add a week number on to an invoice table, these will run from the 1^st August each year e.g.

Week No	From	To
1	01-Aug	07-Aug
2	08-Aug	14-Aug
3	15-Aug	21-Aug

This is so a report can be produced to show the number of people booking with us each week.

So far I have created a new table consisting of two fields, week number and date (365 records with week numbers 1

[Al Buchholz]

Re: Adding a week number to an existing table

I have been asked to add a week number on to an invoice table, these will run from the 1^st August each year e.g.

Week No	From	To
1	01-Aug	07-Aug
2	08-Aug	14-Aug
3	15-Aug	21-Aug

This is so a report can be produced to show the number of people booking with us each week.

So far I have created a new table consisting of two fields, week number and date (365 records with week numbers 1

Did your message get truncated?

[Stan Mathews]

Re: Adding a week number to an existing table

Judith,

I believe you are going to have to construct a table of week numbers and corresponding actual start and end dates for some number of years into the future. If you rely solely on the start

01-Aug
08-Aug
15-Aug
etc

your resulting retrieval of the week number for 03-04-someyear will be week 30 for non leap years but 31 for leap years.

[Al Buchholz]

Re: Adding a week number to an existing table

If you have 365 entries for a year with 366 entries for a leap year, you can make a set or mapped table to do what you want.

Link by the date in the invoice table to your week table to get the week number.

[Ted Giles]

Re: Adding a week number to an existing table

Week numbers are actually a specific legal entity.
You might want to rethink the logic.

[armasoft]

Re: Adding a week number to an existing table

Why not just calculate the week number? You'd probably need to store the starting date in a variable, or set it before calculating the week number

int((*days_between(date(),{1/8/17}))/7)+1

seems to do the trick.

[Stan Mathews]

Re: Adding a week number to an existing table

*days_between() is a function introduced in AA/V12 and not available in V11.

[Stan Mathews]

Re: Adding a week number to an existing table

Borrowing Jon's thought, someone see if this works or rather if it breaks down somewhere.

dt = {01/01/2018}
? int(if(dt< ctod("08/01/"+cyear(dt)),dt-ctod("08/01/"+cyear(addyears(dt,-1))),dt-ctod("08/01/"+cyear(dt)))/7)+1
= 22
dt = {07/30/2018}
? int(if(dt< ctod("08/01/"+cyear(dt)),dt-ctod("08/01/"+cyear(addyears(dt,-1))),dt-ctod("08/01/"+cyear(dt)))/7)+1
= 52
dt = {07/31/2018}
? int(if(dt< ctod("08/01/"+cyear(dt)),dt-ctod("08/01/"+cyear(addyears(dt,-1))),dt-ctod("08/01/"+cyear(dt)))/7)+1
= 53

dt = {08/01/2018}
? int(if(dt< ctod("08/01/"+cyear(dt)),dt-ctod("08/01/"+cyear(addyears(dt,-1))),dt-ctod("08/01/"+cyear(dt)))/7)+1
= 1

If anyone wants to do a visual check.

dt = {08/01/2017}
for qx = 1 to 2000
wn = int(if(dt< ctod("08/01/"+cyear(dt)),dt-ctod("08/01/"+cyear(addyears(dt,-1))),dt-ctod("08/01/"+cyear(dt)))/7)+1
save_to_file(padl(alltrim(str(wn)),2,"0")+" _ "+dtoc(dt)+crlf(),"[COLOR="#FF0000"]P:\temp\week nums.txt[/COLOR]",.T.)
dt = dt + 1
next

[armasoft]

Re: Adding a week number to an existing table

OK, how about

int((jdate(test)-jdate({1/8/17}))/7)+1

where test is the date to check against

[Stan Mathews]

Re: Adding a week number to an existing table

Doesn't that require resetting the base date each year? Not that it is wrong to do it that way.

[armasoft]

Re: Adding a week number to an existing table

Base date would need setting or calculating before/during the week number calculation.

[G Gabriel]

Re: Adding a week number to an existing table

dim start as d={8/1/17}
dim end as d
dim wk_No as n

end={8/8/17}
wk_No=min(int((end-start+7)/7),53)
?wk_No
= 2

end={8/14/17}
wk_No=min(int((end-start+7)/7),53)
?wk_No
= 2

end={8/15/17}
wk_No=min(int((end-start+7)/7),53)
?wk_No
= 3

end={8/16/17}
wk_No=min(int((end-start+7)/7),53)
?wk_No
= 3

end={7/31/18}
wk_No=min(int((end-start+7)/7),53)
?wk_No
= 53