Week starting hour gives previous week date instead of current week

In our project we are maintaining a customers weekly spend. For that every week start we are resetting a customer limit.

Whenever customer doing a transaction we are updating his weekly spend by using the below query.

UPDATE SUMMARY 
SET WEEKLIMIT = (
       SELECT NVL (SUM (AMT / 100), 0) 
       FROM TRANSACTION 
       WHERE MOBILENO = :mobileNumber 
       AND TRUNC(TXNDT) BETWEEN (TRUNC (SYSTIMESTAMP, 'IW') - 1 ) AND TRUNC (SYSTIMESTAMP)
) WHERE MOBILENO = :mobileNumber 

But the problem is customer who are doing transaction in sunday 00:00:01 to 01:00:00 hours, the above query updating the previous week limits
instead of current week.

SUMMARY

MOBILENUMBER  AMT TXNDATE    
0000000000    10000 26-12-2019 09:05:34
0000000000    10000 28-12-2019 11:05:34
0000000000    10000 29-12-2019 00:01:35

When I run this query in sunday first hour it returning

200

But from next hour

it returning only

100

Why the starting hour of week gives the previous week data.

Here is my NLS parameters

NLS_LANGUAGE		ENGLISH
NLS_TERRITORY		INDIA
NLS_CURRENCY		Rs
NLS_ISO_CURRENCY	INDIA
NLS_NUMERIC_CHARACTERS	.,
NLS_CALENDAR		GREGORIAN
NLS_DATE_FORMAT		DD-MM-RR
NLS_DATE_LANGUAGE	ENGLISH
NLS_SORT		BINARY
NLS_TIME_FORMAT		HH12:MI:SSXFF AM
NLS_TIMESTAMP_FORMAT	DD-MM-RR HH12:MI:SSXFF AM
NLS_TIME_TZ_FORMAT	HH12:MI:SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT	DD-MM-RR HH12:MI:SSXFF AM TZR
NLS_DUAL_CURRENCY	Rs
NLS_COMP		BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE

Any help will be greatly appreciated!!!!

I believe your issue lies in your date manipulation. You are doing:

TRUNC(TXNDT) BETWEEN (TRUNC (SYSTIMESTAMP, 'IW') - 1 ) AND TRUNC (SYSTIMESTAMP)

and expecting that to make the start of the week a Sunday.

However, you haven't done this correctly - your logic says "take the Monday of the current week and subtract a day from it". If your day is a Sunday, that means it first gets the previous Monday and subtracts a day to make it the previous Sunday.

What you must first do is turn the Sunday into a Monday (by adding a day), truncate it to the first day of the week (Monday), and then subtract a day.

Which means your predicate should now look like:

TRUNC(TXNDT) BETWEEN (TRUNC (SYSDATE + 1, 'IW') - 1 ) AND TRUNC (SYSDATE)

Here's a test case demonstrating the difference:

WITH dts AS (SELECT TRUNC(SYSDATE, 'mm') - 1 + LEVEL dt
             FROM   dual
             CONNECT BY LEVEL <= 13)
SELECT dt,
       to_char(dt, 'Dy') day_of_week,
       TRUNC(dt, 'iw') monday_start_week,
       TRUNC(dt, 'iw') - 1 your_sunday_start_week,
       TRUNC(dt + 1, 'iw') - 1 actual_sunday_start_week
FROM   dts;

DT          DAY_OF_WEEK MONDAY_START_WEEK YOUR_SUNDAY_START_WEEK ACTUAL_SUNDAY_START_WEEK
----------- ----------- ----------------- ---------------------- ------------------------
01/01/2020  Wed         30/12/2019        29/12/2019             29/12/2019
02/01/2020  Thu         30/12/2019        29/12/2019             29/12/2019
03/01/2020  Fri         30/12/2019        29/12/2019             29/12/2019
04/01/2020  Sat         30/12/2019        29/12/2019             29/12/2019
05/01/2020  Sun         30/12/2019        29/12/2019             05/01/2020  <-----
06/01/2020  Mon         06/01/2020        05/01/2020             05/01/2020
07/01/2020  Tue         06/01/2020        05/01/2020             05/01/2020
08/01/2020  Wed         06/01/2020        05/01/2020             05/01/2020
09/01/2020  Thu         06/01/2020        05/01/2020             05/01/2020
10/01/2020  Fri         06/01/2020        05/01/2020             05/01/2020
11/01/2020  Sat         06/01/2020        05/01/2020             05/01/2020
12/01/2020  Sun         06/01/2020        05/01/2020             12/01/2020  <-----
13/01/2020  Mon         13/01/2020        12/01/2020             12/01/2020

You can see that for the two Sunday rows, your version returns the previous Sunday, whereas the amended version returns the current Sunday.