like – Mysql, Getting all rows in which field ends in a specific character, and another field exists that is the same but doesn’t end in that character

I need to get all rows which end in a specific character, P for example, but in which a similar key sans the P exists. I have no idea how to approach this one in MySQL.

This is very small example, my actual data is huge with other columns also.

+------------+
|    key     |
+------------+
| value_100  |
| value_100P |
| value_101  |
| value_101  |
| value_102  |
| value_102P |
| value_103P |
| value_104P |
+------------+

The query would output,

+------------+
|    key     |
+------------+
| value_100P |
| value_102P |
+------------+

microsoft excel – VBA Hide certain Rows based on cell value?

Ok so im new to vba and i been trying with no luck.

i have some information in rows 9 to 38. i need to hide these rows based in a cell value, let’s say A8.

For Example:

If A8=3 , the only show rows 9 to 11 out of 38

if A8=9 , then only show rows 9 to 18 out of 38

If A8=0 , the hide all rows from 9 to 38.

and so on, only for rows 9 to 38.

how can i code this?
i understand this should be coded in the worksheet/ change section?

How can I get an excel formula to skip over even rows?

I am needing to create a formula that will look at every 2 rows, compare the values, and then provide a reduced value. I will be able to better explain what I am trying to do through example.

SPREADSHEET

By looking at the SPREADSHEET, you’ll 8 rows (6 with #’s, and 2 blanks). These are cells D4:D11

The formula I used is:

=IF($D5="", mid($D4, 2, 10), mid($D5, 4, 10)

Basically, for every two rows, there is either two product numbers or just one product number.

If the there are two product numbers, the number in the first first row will contain less digits than the number in the second row.

If the second row contains a number, then I want to extract 10 digits starting with digit 4. If the second row is blank, then I want to extract 10 digits from the number in the first row starting with digit two.

D4: 79285089199
D5: 20792850891993

In this case, I want to extract from the second row to get the value: 9285089199

D8:79285001420
D9:

In this case, I want to extract from the first row to get the value: 9285001420.

The formula mentioned above does this. But, I have to enter this formula manually for every 2 rows. If I try to drag the formula down, excel doesn’t follow the pattern of every 2 rows.

So, how can I get excel to understand that I need it to apply this formula for every 2 rows?

MySQL, get rows for non-matching reference or reference does not exist in other table

MySQL, select non-matching reference or reference does not exist in other table.

For example.

table: category
category_id,    category_name
1               XYZ_1
2               XYZ_2
3               XYZ_3
4               XYZ_4
5               XYZ_5
6               XYZ_6


table: type
type_id,    type_name
1           A
2           B
3           C
4           D


table: type_match
match_Id,   type_id category_id
1           2       1
2           3       1
3           4       1
4           1       1
5           2       2
6           3       2
7           2       3       
8           4       3
9           2       4

I need category_id where type_id is not 3 or category_id does not exist in type_match table:
expected result:

category_id
3  // category_id exist in type_match table but not matching type_id = 3
4  // category_id exist in type_match table but not matching type_id = 3
5  // category_id does not exist in type_match table
6  // category_id does not exist in type_match table

Please help.

worksheet function – Count mismatches between rows in google sheet

Need some help on this cause I’m getting an issue

I’ve this three columns (Time, R1 and R2) and I’m trying to count the mismatches between R1 and R2 but for each month (on the time column)

I already used a formula but I’m having an issue to add 1.

=iferror(if(EOMONTH($A64,0)=$A64,SUMPRODUCT(month(Database!$C$2:$C) = month($A64),--(Database!G$2:G <> Database!H$2:H)),""),"Error")

This part “month(Database!$C$4:$C) = month($A5)” is where I compare the information of the months, ( but I’m having an issue cause cause “month(Database!$C$4:$C)” only retrieves 4 that is the month of april)

This part “(Database!G$4:G <> Database!H$4:H)” is where I compare the columns R1 and R2

The part “EOMONTH($A5,0)=$A5” is where I take the month to based myself

Time R1 R2

2020-04-30  BA  BU

2020-04-30  BU  BA

2020-04-29  BA  BU

2020-04-29  BU  BA

2020-04-28  BA  BU

2020-04-28  AA  BA

2020-04-25  AA  BA

2020-04-22  BU  BA

2020-04-19  AA  BU

2020-04-19  AA  BA

2020-03-27  BA  AA

2020-03-27  BA  AA

2020-03-26  BU  AA

2020-03-18  BA  AA

2020-03-18  AA  BU

Simple query with JOIN on 60M rows using index takes forever in MariaDB/MySQL

Here’s a rather simple SQL query:

select source_id
from data_sources
join sources on sources.id = data_sources.source_id
where log_time + interval sources.retention_days day < current_timestamp;

There are ~800 records in sources and ~60 million records in data_sources.

The DBMS used is Maria DB:

mysqld  Ver 10.3.22-MariaDB-1ubuntu1 for debian-linux-gnu on x86_64 (Ubuntu 20.04)

Indizes are being used apparently:

+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+
|id|select_type|table       |type |possible_keys                  |key                            |key_len|ref                   |rows|Extra      |
+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+
|1 |SIMPLE     |sources     |index|PRIMARY,idx_sources_1          |index_sources_on_retention_days|4      |NULL                  |757 |Using index|
|1 |SIMPLE     |data_sources|ref  |index_data_sources_on_source_id|index_data_sources_on_source_id|5      |vse_web_dev.sources.id|445 |Using where|
+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+

Surprisingly this query takes about an hour on an 8 core Intel i7.
RAM doesn’t seem to be a problem: While the MySQL processes are causing a very high CPU load on multiple cores during execution time, there is plenty of free RAM.

Here the DDLs for the tables involved:

create table sources (
    id int auto_increment primary key,
    name varchar(255) null,
    source varchar(255) null,
    start_time varchar(255) null,
    frequency varchar(255) null,
    stop_time varchar(255) null,
    unit varchar(255) null,
    created_at datetime not null,
    updated_at datetime not null,
    type varchar(255) null,
    move_source_file tinyint(1) default 1 null,
    revision int null,
    data_freq_minute int default 1 null,
    shift_right_time_stamp tinyint(1) default 0 null,
    display_name varchar(255) null,
    source_group_id int null
);

create index idx_sources_1 on sources (id);
create index index_sources_on_source_group_id on sources (source_group_id);

create table data_sources (
    id bigint auto_increment primary key,
    source_id int null,
    log_time datetime null,
    value float null,
    created_at datetime not null,
    updated_at datetime not null
);

create index idx_data_sources_1 on data_sources (id);
create index idx_data_sources_2 on data_sources (id, source_id, log_time);
create index index_data_sources_on_log_time on data_sources (log_time);
create index index_data_sources_on_source_id on data_sources (source_id);
create index index_data_sources_on_updated_at on data_sources (updated_at);


Why is this taking so long and how to speed it up?

google sheets – How to Hide/Show Different Rows based on MULTIPLE cell values

I’ve read through countless different posts that are similar to this topic, but have yet to find something that really solves the exact issue I’m looking to fix.

My script currently reads the value of a particular cell, and depending on it, hides X amount of rows beneath. I’ve seen scripts written where any time the text appears in the column, that specific row (with the matching text) hides — but let me be clear this is NOT what I’m looking for.

The first piece of my code works flawlessly, including all of the else if statements. However, when trying to add in a separate If statement based on DIFFERENT cell, I can’t seem to get that to work.

I need to keep this in one OnEdit I believe, otherwise it would be super simple?

I think I am close, but hoping someone can point me in the right direction.

function onEdit(e){

  if(e.range.getA1Notation() === 'B13' && e.range.getSheet().getName()=='Temp1'){
    if(e.value=='Base1') {
      e.range.getSheet().showRows(14,4);
    } else if(e.value=='Base2') {
      e.range.getSheet().hideRows(15,3);
    }
    else if(e.value=='Base3') {
      e.range.getSheet().hideRows(15,3);
    }
   else if(e.value=='Base4') {
      e.range.getSheet().showRows(14,3);
   }
   else if(e.value=='Base5') {
      e.range.getSheet().hideRows(17,1);
   }
    else if(e.value!== "") {
      e.range.getSheet().hideRows(14,4);
    }
  if(e.range.getA1Notation() === 'B5' && e.range.getSheet().getName()=='Temp1'){
    if(e.value=='Base10') {
      e.range.getSheet().showRows(10,2);
    }
  }
}
}

html – Stacked inputs with rows using flexbox

.stacked-inputs,
.stacked-inputs .row {
    display: -ms-flexbox;
    display: flex;
    -ms-flex-flow: row wrap;
    flex-flow: row wrap;
    -ms-flex-align: center;
    align-items: center;
}
.stacked-inputs .row {
    width: 100%;
    margin-bottom: 10px;
}
.stacked-inputs > input,
.stacked-inputs .row > input {
    position: relative;
    -ms-flex: 1 1 0%;
    flex: 1 1 0%;
    min-width: 0;
}
<div class="stacked-inputs">
  <input type="text" placeholder="First name">
  <input type="text" placeholder="Last name">
</div>

<br /><br />

<div class="stacked-inputs">
  <div class="row">
    <input type="text" placeholder="First name">
    <input type="text" placeholder="Last name">
  </div>
  <div class="row">
    <input type="text" placeholder="Job title">
    <input type="text" placeholder="Year started">
  </div>
</div>

javascript – I want to change rows value in v-data-table

     <v-data-table :items="locations"
                    :headers="headers"
    class="elevation-1"
  >
                    
        <template slot="item" slot-scope="row">
<tr>
      <td class="text-xs-right">{{ row.item.code }}</td>
      <td class="text-xs-right">{{ row.item.name }}</td>
      <td class="text-xs-right">{{ row.item.descr }}</td>
      <td class="text-xs-right">{{ row.item.dateFrom }}</td>
      <td class="text-xs-right">{{ row.item.dateTo }}</td>
      <td :class="row.item.active === true ? row.item.active='active' : row.item.active='not active'" class="text-xs-right">{{ row.item.active }}</td>
</tr>
    </template>

      </v-data-table>`

This is my code. So, I want to set the “active” row to values “active” or “not active” wheter the value is true or false. With my code here, I always get “not active” value (I’ve tested the condition both with true or false, so my guess is I’m not getting any value in condition). Btw, I can’t use v-for in data table if that’s relevant. Thanks