Date calculation not working in Oracle SQL Case Statement?

Question

I am attempting to change the start of the week of a data set from Monday to the previous Friday. The overall data will be aggregated to a week level to look like this.

Week 1

Friday, Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday

The best I have come up with to do this is a case statement which sets Friday, Saturday and Sunday to next week's First day of the week (the system first day is Monday and last day is Sunday).

SELECT TRANSACTIONDATE,
        CASE  
        WHEN TO_CHAR(TRANSACTIONDATE, 'DAY') IN ('FRIDAY', 'SATURDAY', 
'SUNDAY') THEN (TRUNC(TRANSACTIONDATE, 'IW')+7)
        ELSE TRUNC(TRANSACTIONDATE, 'IW')
        END AS TEST
FROM TRANSACTIONS

My logic for this code is: if Fri, Sat or Sun then find the first day of the week and add 7 returning next week's first day of the week.

The problem is that the statement "TRUNC(TRANSACTIONDATE, 'IW')+7" is not adding 7 days to the week start.

When I query

select "TRUNC(TRANSACTIONDATE, 'IW')+7" from transactions

I get the next weeks week start which is correct.

This way all the days from Friday to Thursday are under the same week number.

Any ideas what's going wrong or if theres a better way to accomplish what I'm after?

Thanks, Tom

Edit**

The first data set is what I would like at achieve,

Date           Day       Week
4/1/2018       Monday    1
4/2/2018       Tuesday   1
4/3/2018       Wednesday 1
4/4/2018       Thursday  1
4/5/2018       Friday    2
4/6/2018       Saturday  2
4/7/2018       Sunday    2
4/8/2018       Monday    2
4/9/2018       Tuesday   2
4/10/2018      Wednesday 2
4/11/2018      Thursday  2

So above you can see I want Firday/Saturday/Sunday to fall into the next week.

This is the current system dates/weeks does,

Date        Day      Week
4/1/2018    Monday    1
4/2/2018    Tuesday   1
4/3/2018    Wednesday 1
4/4/2018    Thursday  1
4/5/2018    Friday    1
4/6/2018    Saturday  1
4/7/2018    Sunday    1
4/8/2018    Monday    2
4/9/2018    Tuesday   2

Can you provide sample data and desired results? I'm not sure I fully understand "I am attempting to change the start of the week of a data set from Monday to the previous Friday. " — Gordon Linoff

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2018-05-02T10:32:50

The problem is TO_CHAR(TRANSACTIONDATE, 'DAY')

When you use 'DAY' then the weekday name is padded with space characters. Try SELECT '<'||TO_CHAR(DATE '2018-05-07', 'DAY')||'>' from dual; to see the effect.
Function TO_CHAR(..., 'DAY') returns the weekday name according to current user session NLS_DATE_LANGUAGE settings. This might be english or not.

Change your expression for example to

TO_CHAR(TRANSACTIONDATE, 'fmDAY', 'NLS_DATE_LANGUAGE=american') IN ('FRIDAY', 'SATURDAY', 'SUNDAY')

Date calculation not working in Oracle SQL Case Statement?

Week 1

1 Answers