Query for LAST WEEK starting from monday

Black Star 0
Viewed 41

I need your help please .

I'm using SAP HANA for B1

I have a table for Invoice = OINV TO

 SELECT T0."DocNum" , T0."DocDate" FROM OINV T0

I want a query that get the invoice from last week starting from Monday . For example , we are in Friday 16/09/2022 , i want the query to show me INVOICE from last weeks starting from Monday 05/09/2022 to 11/09/2022

Regards

weekday sapb1 hana date sql
3 Answers

SQL will be like this

SELECT * FROM {tableName} WHERE {columnThatRecordsDate} > '2022-09-05' and {columnThatRecordsDate} < '2022-09-11'

edit for OP comment

SELECT * FROM {tableName} WHERE YEARWEEK(columnThatRecordsDate) = YEARWEEK(NOW() - INTERVAL 1 WEEK);
idkwhatimdoing0

There are multiple solutions to this. IMHO the easiest is to use functions WEEK or ISOWEEK. When going forward with WEEK, you also need to consider checking for the correct year, which is why I would prefer this version:

CREATE TABLE TEST (DOCNUM INTEGER, DOCDATE DATE);

INSERT INTO TEST VALUES (1, '15.9.2022');
INSERT INTO TEST VALUES (2, '8.9.2022');
INSERT INTO TEST VALUES (3, '1.9.2022');

SELECT * 
FROM TEST
WHERE ISOWEEK(CURRENT_DATE) = ISOWEEK(ADD_DAYS(DOCDATE,7))

Note, that the year is part of the output of ISOWEEK.

Mathias Kemeter0

Thanks guys , i found the solution

SELECT ADD_DAYS (CURRENT_DATE,( WEEKDAY( now() ) * -1 ) - 7 ) "StartDate",
     ADD_DAYS (CURRENT_DATE,( WEEKDAY( now() ) * -1 ) - 1 ) "EndDate" ,
T0."DocDate"
        FROM OINV T0 
        INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" 
        WHERE T0."DocDate" >= ADD_DAYS (CURRENT_DATE,
     ( WEEKDAY( now() ) * -1 ) - 7 ) 
        AND T0."DocDate" <= ADD_DAYS (CURRENT_DATE,
     ( WEEKDAY( now() ) * -1 ) - 1 ))) WITH READ ONLY
Black Star0
Related