SQL Event Frequency

Question

I was asked to report the frequency of contacts for our customers, that is, how many of our customers are viewed weekly, monthly, quarterly or annually.

When "frequency" is discussed in forums, they usually refer to the number of times a certain value exists in a table.

I can get the number of contacts the client had: -

select A.client_ID, A.start_date, A.Months_in_Service, B.Contacts, (B.Contacts / A.Months_in_Service) as Contacts_per_Month_in_Service from tbl_client A left outer join (select client_ID, COUNT(contact_date) as Contacts from tbl_client_contact group by client_ID) as B ON A.client_ID = B.client_ID where (A.end_date is null) 

However, I am doing my best to include complication of distribution. For example, some customers have many contacts, but maybe they had weekly contacts, now they only have an annual check.

I can agree that this is not possible, but if someone there did a similar job, I would appreciate your ideas.

Thank you very much

Answer 1

Try it. I am not sure, because I could not see the output of the test data. But try this one.

 SELECT A.client_ID, A.start_date, A.Months_in_Service, B.Contacts2, AVG(B.Contacts2 / A.Months_in_Service) as Contacts_per_Month_in_Service FROM ( select Contacts, Count(Months_in_Service) as Contacts from tbl_client ) AS A LEFT JOIN ( select client_ID, COUNT(contact_date) as Contacts2 from tbl_client_contact group by client_ID ) as B ON A.client_ID = B.client_ID WHERE (A.end_date is null) GROUP BY A.client_ID, A.start_date, A.Months_in_Service, B.Contacts, Contacts_per_Month_in_Service