Weekly and Monthly report

[jza]

I want to generate a weekly and monthly report, usually I would do a <start_date> Between <final_date> however I am think that the report could happen in the middle of the week, so I would need something like NOW() for final date. So how can I 'cut' on monday of each week?

So Report_current_week:

• Mon
  Tue
  Wed -- CUT --

Something similar but on months:
Report_previous_weeks:

• Last_week
  Two_week
  Initial_week (of the month) -- CUT --

Is it possible to do something like this, without macros?

[F3K Total]

Hi,
something like this?

Code: Select all

SELECT "Order", "Order_Date" FROM "Orders" where week("Order_Date")= week(Current_date)-1 AND year("Order_Date")= year(Current_date)

Could cause some problems at the start of a year, because the week before week 1 is given back as week 0.
Try this workaround:

Code: Select all

Select * 
    from "Orders" 
        where 
            week("Order_Date") = (SELECT 
                                     week("Order_Date")
                                     from "Orders" 
                                         where 
                                            DATEDIFF('DD', "Order_Date", CURRENT_DATE) = DAYOFWEEK(CURRENT_DATE)) 
        AND 
            year("Order_Date") = (SELECT 
                                     year("Order_Date")
                                     from "Orders" 
                                         where DATEDIFF('DD', "Order_Date", CURRENT_DATE) = DAYOFWEEK(CURRENT_DATE))

Where you can choose the week to be selected by adding 7 DAYS for each previous week e.g.

Code: Select all

...DATEDIFF('DD', "Order_Date", CURRENT_DATE) = DAYOFWEEK(CURRENT_DATE) + 7)
.
.
.
...where DATEDIFF('DD', "Order_Date", CURRENT_DATE) = DAYOFWEEK(CURRENT_DATE) + 7)

Jumping to next week happens on Sunday

[jza]

Right I thought about a simpler query like:

Code: Select all

 SELECT * from INVOICE WHERE "DATE" >= DayName("Monday") ORDER BY "DATE"

Unfortunately I couldnt really figure out how to use DayName(). I also tried with to_date() but also couldnt make it work. I might need more info here.

Another way is using something like Limit to CURRENT_WEEK.

I will try to make your query work, it didnt work after updating my tables and fields, got some errors, but I am sure is just some minor bug on my part.

[Villeroy]

DAYNAME("My Date Field")
DAYNAME(CURRENT_DATE)
DAYNAME('2012-09-30')

takes valid date values and returns the day name (Monday, Tuesday) for each given date in "My Date Field", today's date or the given date constant respectively.

DayName("Monday") returns the day names of a given date field named "Monday" (if any such field and if that field has date values).
DayName('Monday') can not return anything because the literal string 'Monday' is not a date value.

##############

WEEK(date) returns the week of this year (1-53)
YEAR(date) returns the year

Code: Select all

SELECT *
FROM "Table"
WHERE WEEK("My Date Field") = WEEK(CURRENT_DATE)
  AND  YEAR("My Date Field") = YEAR(CURRENT_DATE)

Returns all columns and the rows of "Table" where both, the week number and the year number, are the same as today's week number and year number.

For clarity you may include the calculated fields in the result set:

Code: Select all

SELECT *,
  WEEK("My Date Field")AS "Week",
  YEAR("My Date Field") AS "Year"
FROM "Table"
WHERE "Week" = WEEK(CURRENT_DATE)
  AND "Year" = YEAR(CURRENT_DATE)

Same game with year numbers only, quarter numbers and years, month numbers and years.