We help IT Professionals succeed at work.

need calculation for avg number of  counts per day.

Fordraiders
Fordraiders asked
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
Comment
Watch Question

View Solution Only

smilieface

Commented:
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 PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:


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

Author

Commented:
SCOTT,  giving me a parameter prompt on DAY
Fordraiders

Author

Commented:
this is access vba   ...Not sql server sorry
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:

OOPS, didn't see this was Access and not SQL Server.


Googled this help:
common approach in Access is to "stack" query. You make one query, save it and name it something then you create 2nd query and have it base on the first query.


I've touched the query up as best I can for Access.  

So maybe you can create a query with the inner/subquery below, and then base a 2nd query on that?

SELECT AVG(completed_count) AS avg_daily_completed, COUNT(*) AS days_with_completion
FROM (
    SELECT
        CDATE(create_date) AS create_day,
        COUNT(*) AS completed_count
    FROM dbo.table_name
    WHERE status = 'completed' AND
        create_date >= #01/01/2019# AND create_date < #01/01/2020#
    GROUP BY CDATE(create_date)
) AS query1



Fordraiders

Author

Commented:
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

Author

Commented:
thanks scott