02-23-2016 11:11 AM
I need to find the number of notes each rep is putting into Act! for the current month, I used FlySpeed SQL Query to build the relationships between tables so I don't have to muck with inner and outer joins.
So far, I got this to work on the demo database. I have two questions:
1) Do I count the Note.displaydate or the note.editdate ?
2) How do I tell this query to find only the current month ?
TBL_USER.USERLOGIN As [Record Manager],
Count(TBL_USER.USERLOGIN) As Count
TBL_CONTACT Inner Join
TBL_CONTACT_NOTE On TBL_CONTACT_NOTE.CONTACTID = TBL_CONTACT.CONTACTID
TBL_NOTE On TBL_CONTACT_NOTE.NOTEID = TBL_NOTE.NOTEID Inner Join
TBL_USER On TBL_USER.USERID = TBL_NOTE.CREATEUSERID
TBL_NOTE.DISPLAYDATE >= '2014-06-01'
Thanks in advance. I will use Excel via SQL connection (not OLEDB2) to connect and display the results.
02-23-2016 01:27 PM
02-23-2016 01:35 PM
1. Based on the demo table for me, I'd suggest counting displaydate if that and editdate are the only two choices. I'd actually say createdate is the best option if you are going to count a date field.
2. To get notes created in the current month, the WHERE clause would look like
WHERE DatePart(month,TBL_NOTE.CreateDate)=DatePart(month,GetDate()) AND DatePart(year, TBL_NOTE.CreateDate)=DatePart(year,GetDate())
If you needed to change which date field, just replace the field name to the one you want. You shouldn't have to touch the rest of it. A warning about this though. If you run this on the first of the month, you won't get much of a count, since few items will have been created at that point.