Counting based on dates (1 Viewer)

dmorgan20 · Apr 6, 2018

Afternoon

I have a query and need to achieve the following:

- Count number of calls from agents from tbl_Genesys_Weekly
- Count number of times agents appear in tbl_ReferralsYTD based on the week commencing date from tbl_Genesys_Weekly + 7

So far I have this and its not going to plan at all:

Code:

SELECT tbl_ReferralsYTD.USERID, Count(tbl_ReferralsYTD.USERID) AS CountOfUSERID, tbl_Genesys_Weekly.Field7 AS Calls, tbl_Genesys_Weekly.WC_Date, tbl_ReferralsYTD.NOTE_EFTV_DT
FROM tbl_ReferralsYTD LEFT JOIN tbl_Genesys_Weekly ON tbl_ReferralsYTD.USERID = tbl_Genesys_Weekly.Field5
GROUP BY tbl_ReferralsYTD.USERID, tbl_Genesys_Weekly.Field7, tbl_Genesys_Weekly.WC_Date, tbl_ReferralsYTD.NOTE_EFTV_DT
ORDER BY tbl_Genesys_Weekly.WC_Date DESC;

How I want the table to be lay out:

Agent | Referral Count | Call Count | Week Commencing Date

Any help is appreciated, thank you

Minty · Apr 6, 2018

You will probably need to get the counts from two separate queries, as the grouping by date for the day count will mess with the weekly grouping.

Create a query that does the first count, then create a query that does count two.
Now create a third query to get all your agents and use a left join to bring in the matching records from the two previous saved queries.

Pat Hartman · Apr 6, 2018

Also, you can't count something you are grouping by --

Count(tbl_ReferralsYTD.USERID) AS CountOfUSERID,

The count will always be 1.

Counting based on dates (1 Viewer)

dmorgan20

Registered User.

Minty

AWF VIP

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread