ODBC - SQL Statement help

[Higenbo]

Hi all,

I am using a SQL statement via ODBC to query a CRM table.

I want to group the results by number of appointments in a month by company.

So my raw data example is

Company Name Appointment Date Appointment Type
Joe Bloggs 01/01/2018 Audit
Joe Bloggs 02/01/2018 Audit
Joe Bloggs 05/03/2018 Other

I want my query to just return a single line for Joe Bloggs for an Audit in January 2018. I don't care that is was over 2 days. Then to return Joe Bloggs for an Other appointment in March 2018.

The only way I could think to do this is to get my SQL statement to change the Appointment date field to EOMONTH, in this case 31/01/2018 & 31/03/2018 and I can then group by this field.

If this is the best option does anyone know how I can do this via SQL?

If there are easier options I am overlooking I'm all ears too!

Thanks in advance

 

[Joe4]

The only way I could think to do this is to get my SQL statement to change the Appointment date field to EOMONTH, in this case 31/01/2018 & 31/03/2018 and I can then group by this field.

I don't know if that would work for you either.
What if you had 1/31/2018 and 2/1/2018? Supposedly, that would be a continuation of the same audit even though it covers days in two months, right?

What makes this tricky is that you have a relationship between records within a single table, and true relational databases shouldn't be like that. Each record should be totally independent of other records in the same table.

If you are bringing the data back into Excel, it may be best to handle it in there by sorting the data by Company Name and Appointment Date, and then add a helper column to see the difference in dates, and it is one for a particular record, then indicate it is the same as the record above. You could make that a True/False formula, then filter on it to hide all the False values.

 

[Higenbo]

Hi,

Thanks for your comments and understood how the database/table has been set up.

For my purpose I just need to know the month the appointment started in. So if day one was the last working day and then there were a further 3 days in the following month I just need to know the month the appointment started.

Thanks

 

[Joe4]

Create a calculated field that only returns Month & Year, and use that instead of your Date field.
Then you can Group on that field, to filter out all the unnecessary duplicate records.