Avatar of Fordraiders
Fordraiders
Flag for United States of America 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
Microsoft AccessVBASQL

Avatar of undefined
Last Comment
Fordraiders

8/22/2022 - Mon
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Fordraiders

ASKER
this is access vba   ...Not sql server sorry
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.