change saturday and sunday to 'weekend'-CodePudding

Select TO_CHAR(smalldatetime, 'DAY') as day,
count(personID) as person
from AttendanceRecord
group by TO_CHAR(smalldatetime, 'DAY')

outputs

DAY PERSON
FRIDAY      3
SUNDAY      1
SATURDAY    2
THURSDAY    5

how do make it so saturday and sunday are counted as 'weekend'. So it should look like:

DAY PERSON
FRIDAY      3       
Weekend     3
THURSDAY    5

Edit: I update the schema and it still displays the same output

Select case TO_CHAR(smalldatetime, 'DAY')
when 'SATURDAY' || 'SUNDAY' then 'weekend'
else TO_CHAR(smalldatetime, 'DAY') end as day,
count(personID) as person
from AttendanceRecord
group by TO_CHAR(smalldatetime, 'DAY')

Update: I changed it to this and it gave me half of the goal. Weekend outputs twice

Select 
Case TO_CHAR(smalldatetime, 'DAY', 'NLS_DATE_LANGUAGE=''numeric date language''')
when '1' then 'Monday'
when '2' then 'Tuesday'
when '3' then 'Wednesday'
when '4' then 'Thursday'
when '5' then 'Friday'
when '6' then 'WEEKEND'
when '7' then 'WEEKEND'
else TO_CHAR(smalldatetime, 'DAY', 'NLS_DATE_LANGUAGE=''numeric date language''') end as day,
count(personID) as absenses
from AttendanceRecord
group by TO_CHAR(smalldatetime, 'DAY', 'NLS_DATE_LANGUAGE=''numeric date language''')

CodePudding user response：

When you use TO_CHAR(..., 'DAY') then result is padded with spaces, i.e. for Sunday you get "SUNDAY "

Try this one:

with t as (
    SELECT personID,
        CASE
        WHEN TO_CHAR(smalldatetime, 'fmDAY', 'NLS_DATE_LANGUAGE=american') IN ('SATURDAY', 'SUNDAY') THEN 'weekend'
        ELSE TO_CHAR(smalldatetime, 'fmDAY')
    END as weekDay
    FROM AttendanceRecord)
select weekday, count(personID) as person
from t
group by weekday;

Or a bit shorter:

WHEN TO_CHAR(END_TIME, 'DY', 'NLS_DATE_LANGUAGE=american') IN ('SAT', 'SUN') THEN 'weekend'

CodePudding user response：

CASE doesn’t work like that. There are 2 forms:

1. CASE expression WHEN value THEN …
2. CASE WHEN condition THEN …

You have mixed the 2 forms together.

Either:

case TO_CHAR(smalldatetime, 'DAY')
  when 'SATURDAY' then 'weekend'
  when 'SUNDAY' then 'weekend'
  else TO_CHAR(smalldatetime, 'DAY')
end

Or:

case
  when TO_CHAR(smalldatetime, 'DAY') in ('SATURDAY', 'SUNDAY') then 'weekend'
  else TO_CHAR(smalldatetime, 'DAY')
end