Mysql - using REVERSE LEFT GROUP and GROUP CONCAT to find string that match except for proceeding characters

This was the problem I was trying to solve.
MySql search to find IDs that match but some the Proceed with a Zero

This is a pretty niche little issue eh!


In one of our Software applications we have users with Payroll IDs, but some had been imported with Zero's at the beginning and some without.   Probably due to some excel settings !

What I wanted to do was write a query for this to find these issues.


To solve this I used this logic

1.    I would REVERSE the Payroll Id's
2.   That way I could match the first 6 digits on the LEFT before the we'd get to a zero !
3.   GROUP those that matched
4.  COUNT them and put the highest amounts to the top
5.  Show all of the Payroll Ids so that I could scan the list easily to see which ones could be an issue.



Here's my query.



SELECT count(*), GROUP_CONCAT(identifier) Identifiers FROM eck_payroll as p
WHERE p.identifier != 0  AND p.`state` = 1
GROUP BY LEFT(REVERSE(p.identifier),6)
ORDER BY count(*) DESC;

How to batch multiple Queries together in Sequel Pro

I’d searched for a handful of things to solve this issue, and in the end it was quite simple.
So I thought I’d note it here.
Here are some of the terms I searched

• 
‘Mac automate multiple sql commands’
• 
’sequel pro run multiple queries’
• 
‘sequel pro query window mulitple commands on run’

Here’s what the problem I was trying to solve was

I had a sql statement that was crashing, however I could run in with a LIMIT of 1000


> Click in the Query window.
> Add your query and then copy and paste the amount of rows you need. 

> Click on 'Ctrl+R'

And that’s it. 


Using OnEdit to add Date to Previous Cell in Google Sheets

Here's a nifty bit of code that will add a Date to the cell of your row when you enter data in the 3rd cell.

function onEdit(e) {

 

    var ss = e.source.getActiveSheet();

    if (ss.getName() !== 'sheet1' || e.range.columnStart !== 2) return;

    e.range.offset(0, -1)

        .setValue(e.value ? new Date() : null);

}

Or if you want to have it on a couple of sheets you could use

function onEdit(e) {

 

    var ss = e.source.getActiveSheet();

  if (ss.getName() == 'sheet1' && e.range.columnStart == 2) {

    e.range.offset(0, -1)

        .setValue(e.value ? new Date() : null);

  }

  else if (ss.getName() == 'sheet3' && e.range.columnStart == 2){

    e.range.offset(0, -1)

        .setValue(e.value ? new Date() : null);

  }

  else if (ss.getName() == 'sheet5' && e.range.columnStart == 2){

    e.range.offset(0, -1)

        .setValue(e.value ? new Date() : null);

  }

  else {

   return; 

  }

  

}

What different voice operations are there on my phone ?

What different voice operations are there on my phone ?

I have a Samsung S9 , and I thought it may be useful to others to list the ways in which I using voice commands.

Talking to your mobile to operate it and to connect to the internet is on the rise.  It’s not only often much quicker than manually operating but it also means you can use your phone hands free.  Handy if you’re Driving.

1. Google shopping list

2. Google Routines

3. Reminders

4. Sending and receiving messages

5. Sat Nav Features

6. Asking assistant to convert something for me.

7. Chatting with Google Translate

8. Dictate something to text.  Using Voice Recorder

9.  Using Pocket to Readme a Webpage

10. Help making calls hands free calls

Google Shopping List

Just add stuff to it whenever it comes into your head 

“ok google, add XXXX to my shopping list”
"show me shopping list "

You can also have multiple contributors, so everyone in the family can add to the same list. 

> To do this just click on the person icon in the top right. 

Google Routines

This is basically a wrapper for all the things you have access to in Google Assistant.  So you can string a whole heap of stuff together.

Here’s an example for you.  On a midweek night I pick up the kids and their friends from a sports activity.  I’ve programmed run this routine when I say “Ok google I’m ready to pick up”

Google then -

1. Tells me the time

2. SMS messages the parents

3. Checks my route home and tells me how long it’ll take.
4. Read my Reminder

5. Tell me a riddle ( something to amuse the kids when the get in )

6. SMS messages my wife to say I’m on the way.

There are preset Routines and you can add as many others as you'd like

1. Open Google APP

2. Go to ‘Settings’

3. Click on Google Assistant

4. Click ‘assistant’ tab

5. Click on Routines

Reminders

Using reminders at using Google assistant as a second memory is a great for someone like me who has a terrible memory.   You can set reminders on a timer, for example 
“Remind me tomorrow morning that I need to get petrol”

You can also set a reminder on location 

“remind me in Newquay Town that I need to get Petrol “

Not only that but you can just set a reminder like 

“Remember that Mike Smiths wife is called Ann”  

and then to retrieve that you’d say 

“Remind me what was Mike Smiths wife called”

Sending and receiving messages

I’ve been sending, receiving and getting read for me ; from Gmail, SMS and WhatsApp.

"Send an Email to Mum"
"Read my last email ( from XXXX ) "
"Send a SMS message to XXXX"
"Send a Whatsapp message"
"Send a Voice message"

Sat Nav Features

“Navigate to WHEREEVER”

“Get driving directions to WHEREEVER”

Tip; if you set up your personal locations in Google Maps then you can say “home”, “work”, “Mums”, ‘the Gym” etc.  This one will impress your passengers !

Asking assistant to convert something for me.

Convert Lengths, Area, Temperature, Volume, Mass & Data.  Also ( this was a revelation to me ) calculate currencies, it even does the more random ones like Czech Koruna .

"what is 15% of 135"
"how many centimetres in 2 foot"
"How much in British Pounds in 10 thousand Czech Koruna"

Google Translate

Using Google Translate is a great way to learn some basics of a language.

“What the spanish word for XXXX”

“How do you say XXXXXX  in spanish”

Podcasts and Text to Speach

Find out what Podcast that you think will be good for you to listen to.  And then use.

“Listen to XXXXX Poscast”

“Play the latest episode of XXXXXX”

“Recommend a podcast”

If you’re looking for a Text to Speech APP then I use Pocket which is awesome.  Presave your webpages that you want it to read and then when you start your journey click on the headphones and get it read back to you.

Initiate Phone Call

This one should probably be higher of the list but you can also use your phone as a phone.

Who knew eh!

Voice control helps you initiate the calls

“call a mum at home on speakerphone”

If you’ve got any call features you use then why not let me know, I’m also looking for more ways to increase my usage of Mobile phone voice assistant options.

Most of the advice I’ve wirtten about above can from Googles documentation.

https://assistant.google.com/explore