mysql – How to insert rows for each of the type column?

How to insert rows for each of the type column? If there is two type, type 1 and type 2, then I need to insert two rows and also need to change the order and id value for whole table.

current status:

CHOICE Table

id  choice  type    order
1    AA      1       1
2    BB      1       2
3    CC      1       3
4    AAA     2       4
5    BBB     2       5
6    CCC     2       6
7    DDD     2       7

Required updated table:
insert choice “000” for each type.

updated CHOICE Table

id  choice  type    order
1    000     1       1
2    AA      1       2
3    BB      1       3
4    CC      1       4
5    000     2       5
6    AAA     2       6
7    BBB     2       7
8    CCC     2       8
9    DDD     2       9

here, id and order column serialized again.

The actual table is too big, so I cannot insert by edit. Please help for this complex query. I have no clue to solve this.

sharepoint online – How to change the color of odd and even rows of a list

You can add multiple classes in additionalRowClass property in above JSON as per your requirement.

For example, to change the color of rows with gray background to blue, you need to add ms-fontColor-blue class to the first condition, like:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalRowClass": {
    "operator": ":",
    "operands": [
      {
        "operator": "==",
        "operands": [
          {
            "operator": "%",
            "operands": [
              "@rowIndex",
              2
            ]
          },
          0
        ]
      },
      "sp-css-backgroundColor-neutralBackground ms-fontColor-blue",
      {
        "operator": ":",
        "operands": [
          {
            "operator": "==",
            "operands": [
              {
                "operator": "%",
                "operands": [
                  "@rowIndex",
                  2
                ]
              },
              1
            ]
          },
          "sp-css-backgroundColor-noFill",
          ""
        ]
      }
    ]
  }
}

You can find more predefined classed you can use in JSON formatting at:

  1. Predefined classes
  2. Reusable SharePoint Online Classes for Modern UI

Note: In SharePoint JSON formatting, @rowIndex starts with 0.

select – MYSQL get rows not axisting in other table

I have following challenge (yes for me it is a challenge)

I have two tables with 1 identical data column for comparison.

One table “species” is a wide list of species and each data row has one unique id called “suid” add. to its primary id as usual.
The second table “printpipe” is a shopping cart for printouts and each
row has the suid from the species and a unique personal id from the user “guid”.

Now I want to get all rows from “species” having empty field “stockimg” and not “noimage.png” in field “stockimg” AND do not exist in table “printpipe” with same suid AND !!! having memb_guid identical to users guid I have as variable $guid

My query I tried is:
$get_allspecies = $mysqli->query(“SELECT * FROM species WHERE stockimg <> ‘noimage.png’ AND stockimg <> ” AND NOT EXISTS (SELECT * FROM printpipe WHERE printpipe.suid = species.suid AND memb_guid = ‘$guid’) ORDER BY name_sc, name_de”);

But it doesn’t work
What am I doin wrong?

Thanks for any help

Ingo

t sql – In SQL Server, what’s the best way to split multiple delimited strings into rows without a function?

I know there are numerous examples of how to split strings in SQL Server. However, they are often for a single delimited string. What’s the best way to split multiple delimited strings without using a function (SQL Server 2016)?

Here’s what I’m starting with:

GroupId     Val1      Val2
----------- --------- ------
G1           1,2,3     a,b,c
G2           4,5,6     d,e,f
G3           1,2,3,4   w,x,y,z

Here’s what I would like to end up with:

GroupId Val1    Val2
------- ------- -------
G1      1       a
G1      2       b
G1      3       c
G2      4       d
G2      5       e
G2      6       f
G3      1       w
G3      2       x
G3      3       y      
G3      4       z      

Based on everything I’ve read, I think I may need to use the XML method because it allows me to specify an order, but I haven’t been able to find an example of this method where multiple columns are split.

Here’s the T-SQL to create the source table:

DROP TABLE IF EXISTS #TestData;

CREATE TABLE #TestData(GroupId VARCHAR(10),
                       Val1 VARCHAR(100),
                       Val2 VARCHAR(100)
                       );
INSERT INTO #TestData
VALUES ('G1', '1,2,3', 'a,b,c'),
       ('G2', '4,5,6', 'd,e,f'),
       ('G3', '1,2,3,4', 'w,x,y,z');

SELECT *
FROM #TestData;

Thank you in advance!

google sheets – Counting rows based on the date it has and the current end of the week

Problem to solve:
Given 2 rows, containing the author’s name and a date, you have to count how many rows that author has.
The tricky part here is that I have to use the date row and check how many entries does that author have within the week of that date he entered.

What my data looks like:

enter image description here

Expected output:

enter image description here

Let’s hypothetically say that today is the 19th of February and it’s a Friday.
I need to count each row that has a date between 15th of February (Monday) and 21st of February (Sunday).

Next week, from 22nd of February, I need to start it over and check it again for that week and so on.

So, I’m trying to better understand how to approach a simple / stupid problem that I can’t find a solution to it.
I’ve been messing around with the QUERY() function but with no luck since I have literally no idea what lead to follow.

Thanks in advance if someone can solve it or at least point me into the right direction.

oracle – Select all rows that have all of the following column values

I have the following tables Genres, Films, Directors. They have the following schema:

CREATE TABLE GENRES(
    GID INTEGER PRIMARY KEY,
    GENRE VARCHAR(20) UNIQUE NOT NULL
);

CREATE TABLE Films(
    FID INTEGER PRIMARY KEY,
    Title VARCHAR(45) UNIQUE NOT NULL,
    DID INTEGER NOT NULL,
    GID INTEGER NOT NULL,
    FOREIGN KEY (DID) REFERENCES Directors(DID),
    FOREIGN KEY (GID) REFERENCES Genres(DID)
);

CREATE TABLE Directors(
    DID INTEGER PRIMARY KEY,
    First_Name VARCHAR(20) NOT NULL,
    Last_Name VARCHAR(20) NOT NULL
);

I want to write a query that will allow me to select all of Director information for every director that has made atleast one movie in the same genre(s) as another director. For example if Stanley Kubrick has made films in genres ‘Sci-Fi’, ‘Thriller’, and ‘Crime’, I want to select all the directors who have made at least 1 sci-fi AND 1 thriller AND 1 crime film.

I’ve tried the query seen below but this will give me directors who have made atleast 1 sci-fi OR 1 thriller OR 1 crime film.

SELECT DISTINCT D.DID, D.First_Name, D.Last_Name
FROM Directors D
LEFT JOIN Films F
ON F.DID = D.DID
LEFT JOIN Genres G
ON G.GID = B.GID
WHERE G.Genre IN (
  SELECT DISTINCT G1.Genre
  FROM Generes G1
  LEFT JOIN Films F1
  ON F1.GID = G1.GID
  LEFT JOIN Directors D1
  ON D1.DID = D1.DID
  WHERE D1.First_Name = 'Stanley'
  AND D1.Last_Name = 'Kubrick'
);

Additionally, I am not able to check before hand which Genres the director in question has been involved with. The query should work with the only given information being the Directors First and Last name.

machine learning – Which model to apply on such panel data with so may rows but for each unique id rows are 6-8 rows per unique id?

I am new to such panel data where I have multiple observation for same ID in different Quarter and I am not sure what kind of machine learning algorithm I can apply.

I have data from Q1-18 till Q4-2020

I have 2,000,000 rows and 200,000 unique id and 20 columns

For each id I have only 6-8 past quarter values, max quarter for each id are 8 quarters and for some id I have only 6 quarters where few quarter value are not available for that id

Below is the basic idea of what my data set look like

Quarter – respective business quarter for that year

Target – is the sales volume in ratio

I am trying to Predict – Target column for 2021 Q1 quarter

I have 8-10 different numeric columns and state , quarter and ID as category columns

I would appreciate if someone could suggest me what kind of modelling could be performed

enter image description here

Retrieve date ranges from multiple rows

I’m struggling to find an optimal way of achieving this without lots of individual queries and PHP, I’m sure there must be an easier way of doing it.

To simplify we have the following table structure:

CREATE TABLE `log` (
  id INT UNSIGNED AUTO_INCREMENT,
  userid INT,
  itemid INT,
  eventname VARCHAR(30),
  timecreated INT
);

timecreated is a unix timestamp, eventname will be one of 2 statuses (assigned or unassigned).

Assuming the following data (I’ve put actual dates so it’s easier to read):

id userid itemid action timecreated
1 1 1 assigned 2020-01-01
2 1 2 assigned 2020-01-01
3 1 1 unassigned 2020-03-01
4 1 1 assigned 2020-06-01
5 1 1 unassigned 2020-06-30

The output should be:

itemid start end
1 2020-01-01 2020-03-01
2 2020-01-01
1 2020-06-01 2020-06-30

In Google Sheets, how can I multiply across two rows conditioned on whether a value exists in a given cell?

I have two tables that track sales seasonality as well as sales per person per month:

Seasonality:

Seasonality Jan% Feb % Mar%
4.5% 6.0% 7.5%

Sales:

Salesperson Jan$ Feb$ Mar$
John $1000 $1200
Jen $1350 $800

What I’d like to do is divide the sum of all months that have sales values (Jan$ – Feb$, for example) by the sum of the seasonality values through that month (Jan% – Feb%). Specifically, I’d like the sum of the seasonality to be limited, such that it won’t divide by the March seasonality number unless the March sales number has a number value (including a zero, but not a blank cell). I’d like to do this per row so that I can calculate per-salesperson seasonality.

Any thoughts on how to tackle this?

microsoft excel – How to transpose grouped columns to rows with key

I’m hoping to transpose groups of data in columns to rows, while maintaining a unique key.

Here’s what I mean:

Current data (CSV below)

Desired result

In case it helps, there are up to (but not necessarily) 25 tags per filename.

Any help would be appreciated. Thank you!

CSV of current data:

filename,tag1,confidence1,tag2,confidence2,tag3,confidence3
filename1,water,0.760709643,green,0.733112454,nature,0.721782982
filename2,interior,0.787028313,room,0.752214372,architecture,0.72691071