CODEWITHSUNDEEP
sqloracle-database
user13475995
user13475995

Reputation:

SQL: Getting the values of all weekdays of a certain calendar week

I am trying to get the value of bought items of all weekdays of a certain calendar week.

select to_char(angelegt_am, 'Day') angelegt_am,
sum(menge) menge
from fadorders_out
group by to_char(angelegt_am, 'Day');

this Query is giving me all values of the year but i don't know how to change it so i get the data from a certain single week.

Upvotes: 0

Views: 48

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

Solutions like

where to_char(angelegt_am,'IW') = 44 
group by to_char(angelegt_am, 'Day')

have a problem, they return grouped values from all years, not only the current year.

One solution could be this one:

select to_char(angelegt_am, 'Day') angelegt_am,
       sum(menge) menge
from   fadorders_out
where to_char(angelegt_am, 'IYYY-"W"IW') = '2020-W44'
group by to_char(angelegt_am, 'Day')

Getting the date from a Week (according to ISO-8601) is not trivial, for example 2021-01-04 is Week 53 of year 2020. The year from ISO-Week can be different to the actual year.

For proper conversion I use these functions:

CREATE OR REPLACE FUNCTION ISOWeek2Date(YEAR INTEGER, WEEK INTEGER) RETURN DATE DETERMINISTIC IS
    res DATE;
BEGIN
    IF WEEK > 53 OR WEEK < 1 THEN
        RAISE VALUE_ERROR;      
    END IF;
    res := NEXT_DAY(TO_DATE( YEAR || '0104', 'YYYYMMDD' ) - 7, 'MONDAY') + ( WEEK - 1 ) * 7;
    IF TO_CHAR(res, 'fmIYYY') = YEAR THEN
        RETURN res;
    ELSE
        RAISE VALUE_ERROR;
    END IF;
END ISOWeek2Date;

CREATE OR REPLACE FUNCTION ISOWeek2Date(WEEK VARCHAR2) RETURN DATE DETERMINISTIC IS
BEGIN
    IF NOT REGEXP_LIKE(WEEK, '^\d{4}-?W\d{2}$') THEN
        RAISE VALUE_ERROR;      
    END IF;
    RETURN ISOWeek2Date(REGEXP_SUBSTR(WEEK, '^\d{4}'), REGEXP_SUBSTR(WEEK, '\d{2}$'));
END ISOWeek2Date;

Upvotes: 1

TheNikita
TheNikita

Reputation: 81

Actually you can extract week number from date and then filter using it:

select to_char(angelegt_am, 'Day') angelegt_am,
       sum(menge) menge
from   fadorders_out
where   to_char(angelegt_am, 'iw') = 2 -- specify your week number here
group by to_char(angelegt_am, 'Day')

But please check that you and your DB have the same view on weeks start and end dates

You can further read https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34510

Here you can find all other format arguments accepted by to_char function under the "Datetime Format Models" topic

Upvotes: 0

Chris Saxon
Chris Saxon

Reputation: 9785

Filter the data in your where clause, e.g.:

select to_char(angelegt_am, 'Day') angelegt_am,
       sum(menge) menge
from   fadorders_out
where  angelegt_am >= to_date ( :week_start_date, 'yyyy-mm-dd' ) -- change format as appropriate
and    angelegt_am < to_date ( :week_start_date, 'yyyy-mm-dd' ) + 7
group by to_char(angelegt_am, 'Day')

Upvotes: 0

Related Questions