MS SQL working days - the average number of cases per 1 working day

1

Hello,
I have data from several years on a daily basis. Every day there are dozens of cases that get a decision.
I need to obtain data on the average number and value of cases with a decision for one business day.
Thank you for your help

2

Welcome

Please share schema of table involved?

3

a.calendar table with data: days for each day and month of the year (yyy-mm-dd), name of days of the week (Monday, Tuesday, ..., Sunday):

|Data |DzieńTyg|DayName|Tydzień|Miesiąc|MonthName|Kwartał|Rok|
|2020-01-01 00:00:00.000|4 |środa |1 |1 |styczeń |1 |2020|
|2020-01-02 00:00:00.000|5 |czwartek |1 |1 |styczeń |1 |2020|
|2020-01-03 00:00:00.000|6 |piątek |1 |1 |styczeń |1 |2020|
|2020-01-04 00:00:00.000|7 |saturday |1 |1 |styczeń |1 |2020|
|2020-01-05 00:00:00.000|1 |sunday |2 |1 |styczeń |1 |2020|

b.table holidays for example:
2020-01-01
2020-01-06
2020-04-12
2020-04-13
2020-05-01
2020-05-03
2020-05-31
2020-06-11
2020-08-15
2020-11-01
2020-11-11
c.table with data: case, date of creation of the case, date of decision, value of the case

4

usually schema is provided as following. Especially on the case table is this accurate?

create table cases
(
casename varchar(50), 
creationdate datetime,
decisiondate  datetime, 
casevalue int 
)
1 Like
5 

SELECT
    c.date_of_decision,
    COUNT(*) AS total_decisions_for_that_day,
    AVG(c.value_of_the_case) AS avg_value_for_that_day    
FROM tablec c
WHERE  
    c.date_of_decision IS NOT NULL AND
    NOT EXISTS(
        SELECT 1
        FROM tableb b
        WHERE b.holiday_date = c.date_of_decision
    )
GROUP BY c.date_of_decision
ORDER BY c.date_of_decision
2 Likes
6

Thanks a lot!