Category Archives: SQL
Using the cases.date_modified is not the same as the date a case is closed, as the case may have been modified after it was closed. Thus, the query below will return the date the case was transitioned to ‘Closed’ provided the cases.status field is being audited. select CONVERT_TZ(cases_audit.date_created,’+00:00′,’+10:00′) as ‘date_closed’, accounts.name as ‘account’, cases.case_number, cases.name […]
I’ve had a client whose Linux Server (CentOS) gradually gets slower and slower, then falls over. By logging into the MySQL command line, we were able to see that there were some long running queries that never end. show processlist; returns | Id | User | Host | db | Command | Time | State […]
At the MySQL commandline, you can enter show processlist; and see what processes are running. If you’re tracking down some problematic code and you’ve got a query (or set of queries) that don’t end, consume CPU cycles and generally bog everything down to a crawl, it can be helpful to know what time you’ve run […]
From the linux command line, these commands let you backup and restore a SugarCRM database. Update 2016-04-26: Just saw this SugarCRM KB Providing a Backup Without Sensitive Data which is really useful. Thanks Jared. Firstly, for a proper back up of SugarCRM you’ll need two ﬁles, one containing the application ﬁles, one containing the SQL database. […]
Find duplicate contact records, by contact name, in SugarCRM using MySQL.
This snippet of MySQL code shows how to concatenate two fields into one column, also shows how to reverse the concatenation and order by last name then first name.
On a CentOS 6.x linux box, typing the following will show the MySQL version mysql -v or if not the root user, then mysql -u root -p -v (Which says user the root user, prompt for the password, then get the version).
I’m needing to do a sql query to show just the opportunities from the current month, the sql snippet for the WHERE clause to do this is the following: WHERE MONTH(opportunities.date_closed) = MONTH(NOW()) and YEAR(opportunities.date_closed) = Year(now())
Every user of a database is responsible for ‘gardening’ the database, removing the ‘weeds’, watering and maintaining it. Otherwise this sort of thing occurs. Dad gets OfficeMax mail addressed ‘Daughter Killed In Car Crash’
The day is almost over, so I’ll fill you in on some of what occurred today, as it relates directly to this post by Mike at GLComputing. This was originally written as a comment to his post and kinda grew, so I’m posting it here in full. SQL 2008 Express R2 has finally let me […]
I have to thank Kevin Chieff (ACT! guru) for pointing me to this link on how to tell the difference between versions of Microsoft SQL 2005 – and the MS page it points to re the SERVERPROPERTY (Transact-SQL) command. The number of times I’ve had to work out just which version of SQL is running […]
It is now two weeks in to my new digs at Evolution Marketing – and it sure is fun. Primarily I’ve been learning how to drive ACT!, software to take care of your contact management. Yesterday and today I’ve been playing with custom tables in the ACT! MS-SQL database. While ACT! looks after contacts and […]