User community support forum for Apache OpenOffice, LibreOffice and all the OpenOffice.org derivatives
https://forum.openoffice.org/en/forum/
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"
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"
| 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"