[Solved] How do I change start of week in OpenOffice 4.1.1?

[pjbrass]

I need my week to start on Thursday because of reporting requirements. Is there a way to get this with an existing function or do I need to write one? I looked in OO and couldn't find anywhere where I could change this in the options.

[eremmel]

You post your question in Base, so I assume that you are referring to SQL functions. Base is more or less a front end to a database and it has its own 'internal' database HSQLDB v1.8 but you can work with others (external) as well. So you need to look to the capabilities of the database's SQL. I think that first day of week is Sunday and has number 1 in HSQLDB v 1.8.
So you need to have something like:
SELECT .... MOD(DAYOFWEEK(date-field)-3,7)+1 AS "myDayOfWeek" ,,,,

Other databases are using EXTRACT( DAYOFWEEK FROM date-field) and might return 0 for Sunday. So no uniform approach unfortunate. The ISO standard is 1 for Monday (see DAYOFWEEK_ISO() but also not common available)

[pjbrass]

Thank you. Yes, it's Base. Thank you for the formula. How can I use that to set the start of week in the query so when I group by week to get the weekly total of sales, I get Thursday as my week start? I should have included that in my question.

[MTP]

I think Base only uses Sunday as day 1 for the US locale. If you have a different locale setting, you might have Monday as day 1.

If you are only changing the day of the week, using mod as per eremmel is straightforward. Unfortunately if you need the WEEK function to also change (to group by week) it is more complicated - in addition to just changing the week number for thursdays, fridays, and saturdays, you also have to account for the week that overlaps the start of a new year. I think this will change the day 1 from Sunday to Thursday and group everything by week:

Code: Select all

SELECT CASEWHEN(DAYOFWEEK("PickDate")>4,WEEK("PickDate"),
		CASEWHEN(WEEK("PickDate")<>1,WEEK("PickDate")-1, WEEK(CAST(YEAR("PickDate")-1 || '-12-27' AS "DATE")))
			) AS "Week", 
	CASEWHEN(WEEK("PickDate")<>1,YEAR("PickDate"),
		CASEWHEN(MONTH("PickDate")=12,YEAR("PickDate")+1,YEAR("PickDate"))
			) AS "Year"
FROM "TableName"

[pjbrass]

Awesome! Thank you very much. I'll try that and let you know how it goes

[chrisb]

Hello pjbrass,

date arithmetic is extremely difficult to manage particularly when using HSQL 1.8.10.

the year & time period would need to be calculated before the field 'Sales' could be summed.
difficult & complicated to achieve over a number of years.

why not use 2 tables.
1. solely for the dates containing two fields 'StartDate'(thursday) & 'EndDate'(the following wednesday). this is very quick & easy to do using calc.
copy & paste to table. make 'StartDate' your primary key.

2. make your 'sales' table in base, 'ID' (auto increment primary key), 'SalesDate' & 'Amount'.

a simple query to display the required output:-

Code: Select all

select
  to_char("D"."StartDate", 'D DD MON YYYY') as "FromDate",
  sum("S"."Amount") as "SalesTotal"

from "MyDates" as "D"

left join "MySales" as "S"
on "S"."SalesDate" between "D"."StartDate" and "D"."EndDate"

group by "FromDate"

[pjbrass]

Perfect. That works

Thanks

[chrisb]

Hi,
sorry to revisit this post but this forum is a source of help & information for users of all abilities.
this post has been marked as solved and users will understandably believe that the posted solution is both accurate & reliable.

it was obvious to me that the code posted by 'MTP' was not the solution to the problem. That is the reason I previously posted a reply.

this is not a criticism of 'MTP' who does an outstanding job as a volunteer.

the user 'pjbrass' has not rigorously tested the code or may have insufficient data to do so.

'DAYOFWEEK()' produces a number for each weekday.
1=sunday, 2=monday, 3=tuesday, 4=wednesday, 5=thursday, 6=friday, 7=saturday.
we want our week to start on thursday & end on wednesday.
the sequence we need is 5, 6, 7, 1, 2, 3, 4.
that is 5, 6 & 7 of the current week followed by 1, 2, 3 & 4 from the next (following) week.

'WEEK()' produces a number for each week,
the first day of the week is always monday & the last day of the week is always sunday.
this dictates that the week number we use for the days thursday, friday , saturday & sunday is 'WEEK(current week)'.
so we need to change the WEEK number for the days monday, tuesday, & wednesday (of the following week) to that of 'WEEK(current week)'.
the week number ranges from 1 to 53. every 5 or 6 years we have a 53 week year.
upcoming years which have 53 weeks are 2015, 2020 & 2026.

the code below has been thoroughly tested using several different years including all which contain 53 weeks. I believe it to be 100% accurate.
it assumes the user has one table named 'tFirst_Day=Thurs_GroupByWeek_Sales' which contains the fields 'Date' & 'Sales'.
it answers the question posted by 'pjbrass'.
It is for use with HSQLDB 1.8.10.
In my opinion this is not a satisfactory solution to the problem because output is ambiguous & open to misinterpretation.
contains REM statements so should be run from menu:Edit>Run SQL command directly.

Edit: made alteration to code. first line 2nd case structure was 'when week("Date")=53' now reads 'when week("Date") in(52, 53)'

Code: Select all

--DEFAULTS ARE: week start = monday(dayofweek = 2) & week end = sunday(dayofweek = 1)
--we start our week on 'thursday'(dayofweek = 5) & end on 'wednesday'(dayofweek = 4)

select

case
	when dayofweek("Date") between 2 and 4 and week("Date")<>1
		then week("Date")-1
	when dayofweek("Date") between 2 and 4 and week("Date")=1 and week(cast(Year("Date")-1 || '-12-28' as date)) = 53
		then 53
	when dayofweek("Date") between 2 and 4 and week("Date")=1 --and week(cast(Year("Date")-1 || '-12-28' as date)) <> 53
		then 52
	else
		week("Date")
end
as "MyWeek",

case
	when week("Date") in(52, 53) and month("Date")=1 and dayofweek("Date") not between 2 and 4
		then year("Date")-1
	when week("Date")=1 and month("Date")=1 and dayofweek("Date") between 2 and 4
		then year("Date")-1
	else
		year("Date")
end
as "MyYear",

sum( "Sales" ) as "SalesTotal"

from "First_Day=Thurs_GroupByWeek_Sales"

group by "MyYear", "MyWeek"

order by "MyYear", "MyWeek"

[pjbrass]

Thank you. I opted to accept the lookup table option as a solution because it let me group by a week.

In trying your sql code, I ran into a problem. (Note: my table is Orders and Date field is OrderDate so I changed those in what I ran. you said run from menu edit but I didn't find that option. I ran from menu tools/sql and got the following error:

1: Set Function on non-numeric data is not allowed

[chrisb]

Hi,
i had assumed that you had gone with the code posted by 'MTP'.

open base.
click on the 'Queries' icon (which is under the heading 'Database').
click 'Create Query in SQL View' (which is under the heading 'Tasks').
a window with 'OpenOffice Base: Query Design' in its title bar will now be open.
click the toolbar icon ('SQL' with a green tick above it) or from the 'Edit' menu select the option 'Run SQL command directly'.
You are now in SQL direct mode.

paste your sql and hit the 'run query' icon or hit 'F5'.

in SQL direct mode:-
the 'Base' parser is ignored.
SQL statements are executed directly from the back-end (in your case that is 'HSQLDB 1.8.10').
when queries are saved the formatting is retained (tab stops, upper/lower case letters etc.
the sorting of data ('order by') must be made from within the SQL.
REMEMBER statements can be made. A REM statement is preceded by two minus signs '–', is greyed out in the window & ignored by the back-end
there are 3 rem statements in my code.

[pjbrass]

Thanks. Did the run and still have same error in direct mode.

Set Function on non-numeric data is not allowed in statement [SELECT CASE WHEN DAYOFWEEK( "OrderDate" ) BETWEEN 2 AND 4 AND WEEK( "OrderDate" ) <> 1 THEN WEEK( "OrderDate" ) - 1 WHEN DAYOFWEEK( "OrderDate" ) BETWEEN 2 AND 4 AND WEEK( "OrderDate" ) = 1 AND WEEK( CAST( YEAR( "OrderDate" ) - 1 || '-12-28' AS DATE ) ) = 53 THEN 53 WHEN DAYOFWEEK( "OrderDate" ) BETWEEN 2 AND 4 AND WEEK( "OrderDate" ) = 1 THEN WEEK( CAST( YEAR( "OrderDate" ) - 1 || '-12-27' AS DATE ) ) ELSE WEEK( "OrderDate" ) END AS "MyWeek", CASE WHEN WEEK( "OrderDate" ) IN ( 52, 53 ) AND MONTH( "OrderDate" ) <> 12 AND DAYOFWEEK( "OrderDate" ) NOT BETWEEN 2 AND 4 THEN YEAR( "OrderDate" ) - 1 WHEN WEEK( "OrderDate" ) = 1 AND MONTH( "OrderDate" ) <> 12 AND DAYOFWEEK( "OrderDate" ) BETWEEN 2 AND 4 THEN YEAR( "OrderDate" ) - 1 ELSE YEAR( "OrderDate" ) END AS "MyYear", SUM( "Sales" ) AS "SalesTotal" FROM "Orders" GROUP BY "MyYear", "MyWeek" ORDER BY "MyYear", "MyWeek"]

[chrisb]

i have not seen this error message before. It seems to indicate that one or more fields are not of the correct type.

right click on your table 'Orders' & select 'Edit'.

i assume you have 3 fields:-
'ID'. field type=Integer (primary key. auto value=Yes).
'OrderDate'. field type=Date.
'Sales'. field type=Decimal.

check the field type of 'OrderDate' & 'Sales'.
are they correct?
if not close the edit window.
back up your table 'Orders' by using the mouse to drag it a small distance then release the mouse button. hit create.
You now have a backup named 'Orders1'.

exit & reload base.
right click on your table 'Orders' & select 'Edit'.
set the correct field type & save.
if you have trouble setting the field type from the editor then let me know.

you may now run your query.

i notice that you use OpenOffice 4.0.1. i doubt that this is the cause of the problem but am unable to test on my machine.

[pjbrass]

OK. That was weird. My Sales field got changed from Decimal to text when I renamed it awhile back. Should have checked that. Thanks, and it works now.