asked on

need calculation for avg number of counts per day.

need calculation for avg number of counts per day.

I have 3 fields

Create_date
status - text
nsc_id - primary id autonumber

I have about 30,00 records in table
i.e. below

12/1/2019 7:11:43 AM completed 1
12/1/2019 7:00:43 AM completed 2
12/1/2019 7:12:43 AM completed 3
12/2/2019 7:05:43 AM open 4
12/2/2019 7:11:43 AM completed 5
12/2/2019 7:00:43 AM completed 6
12/2/2019 7:12:43 AM open 7
12/2/2019 7:05:43 AM open 8

What I need:

I will be looking at all 2019 records so i need to know on the avg how many status that are "completed" for a given day

on 12/1 3 were completed
on 12/2 2 were completed

so on the avg 2.5 are completed in a given day

Thanks
fordraiders

smilieface

This should be close

with cte_cnt as (
select count(*) as dayCount
from <yourTable>
where status = "completed"
group by convert(date, Create_Date)
)
select avg(dayCount) as dayAverage
from cte_cnt

Open in new window

Scott Pletcher

SELECT AVG(completed_count) AS avg_daily_completed, COUNT(*) AS days_with_completion
FROM (
SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, create_date), 0) AS create_day,
COUNT(*) AS completed_count
FROM dbo.table_name
WHERE status = 'completed' AND
create_date >= '20190101' AND create_date < '20200101'
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, create_date), 0)
) AS query1

Fordraiders

ASKER

SCOTT, giving me a parameter prompt on DAY

Fordraiders

ASKER

this is access vba ...Not sql server sorry

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

different field names but that worked.

query1

SELECT Count(dbo_t_nsc_trackcode_assigned_DataEntry.NSC_Id) AS CountOfNSC_Id, DateValue([opened_date]) AS DateOpen
FROM assigned_DataEntry
GROUP BY DateValue([opened_date])
ORDER BY DateValue([opened_date]) DESC;

and
query2

SELECT Avg(CountPerDay.CountOfNSC_Id) AS AvgOfCountOfNSC_Id
FROM CountPerDay

Fordraiders

ASKER

thanks scott