Community
Showing results for 
Search instead for 
Do you mean 
Reply

Create SQL Query (not OLEDB2) to find notes by current month - How can I do this ?

Copper Contributor
Posts: 144
Country: USA

Create SQL Query (not OLEDB2) to find notes by current month - How can I do this ?

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 ?

 

Select
TBL_USER.USERLOGIN As [Record Manager],
Count(TBL_USER.USERLOGIN) As Count
From
TBL_CONTACT Inner Join
TBL_CONTACT_NOTE On TBL_CONTACT_NOTE.CONTACTID = TBL_CONTACT.CONTACTID
Inner Join
TBL_NOTE On TBL_CONTACT_NOTE.NOTEID = TBL_NOTE.NOTEID Inner Join
TBL_USER On TBL_USER.USERID = TBL_NOTE.CREATEUSERID
Where
TBL_NOTE.DISPLAYDATE >= '2014-06-01'
Group By
TBL_USER.USERLOGIN
Order By
Count,
[Record Manager]

 

Thanks in advance.  I will use Excel via SQL connection (not OLEDB2) to connect and display the results.

Copper Contributor
Posts: 144
Country: USA

Re: Create SQL Query (not OLEDB2) to find notes by current month - How can I do this ?

I was able to get the data out to Excel as the raw notes table; then, use Excel's filters and pivot table tools get the data I needed. Probably easier for the client so they can take advantage of Excel's filtering tools. I'll just use this statement to pull out the raw data.
Nickel Contributor
Posts: 175
Country: USA

Re: Create SQL Query (not OLEDB2) to find notes by current month - How can I do this ?

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.