0
SELECT trunc(to_date(substr(CLOSEDATE,1,10), 'DD-MM-YY')),
    substr(FIXCODE, 1, 4),
    COUNT(*)
FROM opencall
WHERE (opencall.status > 15 OR opencall.status = 6)
    AND FIXCODE like 'HPS0%'
    AND trunc(to_date(substr(CLOSEDATE,1,10), 'DD-MM-YY')) BETWEEN '01-JAN-14' AND '24-DEC-14'
GROUP BY trunc(to_date(substr(CLOSEDATE,1,10),'DD-MM-YY')),
    substr(FIXCODE, 1, 4)
ORDER BY trunc(to_date(substr(CLOSEDATE,1,10), 'DD-MM-YY'))

I have this piece of code which counts the number of calls raised per day against a specific fix code.

Is there any way to exclude weekends for 2014?

While the support desk is open weekends the call numbers drop off quite heavily and i'd like to just focus on calls raised monday to friday?

Deduplicator
  • 44,692
  • 7
  • 66
  • 118
Anthony Stewart
  • 25
  • 1
  • 6

1 Answers1

1

Something like that:

...
AND TO_CHAR(to_date(substr(CLOSEDATE,1,10), 'DD-MM-YYYY'),
                         'D', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('1', '7')

With the D format, Sunday is '1', Monday is '2', Tuesday is '3' and so on. The last argument force NLS settings to ENGLISH as the first day of week in locale-dependant.

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125