0

How to count the number of weekdays for a given date range passed in where clause. For example in the below example there are 13 records however only 9 of them fall under weekday.

select * from table_name where startdate > '2019-01-01' and enddate < '2019-01-31' Date Day Asset Price 01-01-2019 Tuesday A 5 01-01-2019 Tuesday A 23 02-01-2019 Wednesday B 20 03-01-2019 Thursday C 87 04-01-2019 Friday D 34 04-01-2019 Friday D 8 05-01-2019 Saturday E 12 05-01-2019 Saturday E 56 06-01-2019 Sunday F 214 07-01-2019 Monday G 32 08-01-2019 Tuesday H 45 09-01-2019 Wednesday I 67

max092012
  • 365
  • 1
  • 5
  • 25
  • Possible duplicate of [Count days between two dates excluding weekends](https://stackoverflow.com/questions/27731712/count-days-between-two-dates-excluding-weekends). From the sound of it, the table you're operating over is noise. There's also [pure math ways to perform this calculation](https://stackoverflow.com/q/252519/812837), but quite often you want something besides just "weekdays", so wouldn't otherwise be sufficient. – Clockwork-Muse Sep 06 '19 at 22:31

2 Answers2

0
select count(*)
from table_name
where startdate > '2019-01-01' and enddate < '2019-01-31'
and Day not in ("Saturday", "Sunday")
Neeraj Agarwal
  • 1,059
  • 6
  • 5
  • No this doesn't work as this would give the number of records. I need to know only the number of weekdays. – max092012 Sep 06 '19 at 16:13
0

Is this the correct result, if you run it as is?

with table_name (date) as 
(
  values
  '01-01-2019'
, '01-01-2019'
, '02-01-2019'
, '03-01-2019'
, '04-01-2019'
, '05-01-2019'
, '06-01-2019'
, '07-01-2019'
, '08-01-2019'
, '09-01-2019'
)
select 
—- distinct date
count(distinct date)
from table_name
where dayofweek_iso(date(to_date(date, 'DD-MM-YYYY'))) < 6
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16