php – Filter by the total of a SQL column

I have a set of data that I want to filter based on the amount of data. An example to make it clear:
I have this table

Nombre | nit | cantidad 
andres | 222 | 1.5
joseff | 555 | 2
juanca | 777 | 3.5
ivanll | 999 | 4
amxsof | 111 | 6.1

The query you need to do is to ask the rows from this table whose SUM of the quantity column is equal to or less (but as close as possible) to 13, and I would return:

Nombre | Nit | Cantidad 
joseff | 555 | 2
ivanll | 999 | 4
amxsof | 111 | 6.1

why the sum of 2 + 4 + 6.1 = 12.1what is the sum closer to 13 that can be done with the records (can the error rate close to the expected number be set?)

Google Sheets – How do I automate the creation of new tabs based on adding a new value to a column?

The new tab would be formatted based on a different template tab if you could write the code with the following variables:

"Data Entry" is the name of the tab from which I want to use the value of a new entry in column A to automatically create a new tab.

"Template" is the name of the tab from which I want the same formatting for the new tab.

postgresql – Representation and storage of logic rules in the DB column

I have two types of "elements". One that generates time series (let's call them generator element) and the other one, based on some logical rules applied to the time series, was "activated" (activatable elements).

This simplification represents the time series

CREATE TABLE data.time_series
(
    generator_element_id uuid NOT NULL,
    epoch bigint NOT NULL, -- UNIX epoch
    level integer NOT NULL, -- values from 0 to 3
    -- other fields
    -- PRIMARY KEY (generator_element_id, epoch),
    -- FOREIG KEY TO the table with the generator_elements
)

The idea is that the activatable elements are activated according to the following rules

Legend:

  • generator element GE
  • Activatable element AE
IF  (GE1 has level > 1 AND GE2 has level > 1) OR (GE3 has level > 2) for a given instant T1  THEN AE1 becames activated

I am trying to find the best way to represent these logic rules in the database as they are user configured and may contain multiple combinations of "AND" and "OR" rules

The problem, especially with this combination of AND and OR operations, is as if it were just one type of one for which I could create another table with the relationship between the different types of elements and the thresholds. But with a combination of ANDs and ORs I am at a loss.

My first thought was to store these relationships with a simple string, similar to that previously described, but using the appropriate UUIDs for the generator elements, and then having a service that processes these rules and decides whether to activate the elements or not.

I'm looking for alternatives to this approach because I don't think my approach can scale well enough and I personally don't like it …

For the context I use PostgreSQL 12.0 with Docker

To edit:

Clarification about the epochs as it was not clear. The epoch value is not included in the "definition" logic rule, which only contains the elements and their levels (in fact it contains other IDs, but just to keep these two variables simple).

For the actual calculation, the data sets are called up and separated into "bins" for each epoch. This way we apply the rule to the records for that epoch (provided there are always records for all generator elements for the given epoch).

Also add some test data as an example. In this case I use an epoch value 1581638400 that corresponds to 2020-02-14 00: 00: 00Z

INSERT INTO data.time_series
VALUES (
    '00000000-0000-0000-0000-000000000001', -- uuid for GE1
    1, --level
    1581638400 -- epoch for 2020-02-14 00:00:00Z
)

INSERT INTO data.time_series
VALUES (
    '00000000-0000-0000-0000-000000000002', -- uuid GE2
    2, --level
    1581638400 -- epoch for 2020-02-14 00:00:00Z
)


INSERT INTO data.time_series
VALUES (
    '00000000-0000-0000-0000-000000000003', -- uuid GE3
    0, --level
    1581638400 -- epoch for 2020-02-14 00:00:00Z
)

Here would be the rule given as my suggestion for the activatable element AE1:

-- Omiting the 0s for simplicity
"('000...01' > 1 AND '000...02' > 1) OR  ('000...03' > 2)"

With the dummy data, AE1 is activated when the first part of the rule has been completed

But with the way the rule is saved in my proposal, I have to create a special processing system for it …

Any help is appreciated

2013 – Change the background color in the SharePoint list based on another column without CSR

There is no way to change the background color or CSS using the workflows.

This can only be done with CSR or a custom CSS code.

However, you can create a new calculated column and display the color coding only for that column and not for the entire row. Check the following example:

Use calculated columns to add color coding to your SharePoint lists

Note::

  1. Microsoft has disabled the use (or abuse) of the calculated column in SharePoint Online and SharePoint On-Premise based on your current patch level.
  2. So you have to try to create a calculated field and add HTML using a formula. If it works, you'll need to use CSR or custom CSS.

Add HTML to a calculated column

Google Sheets – How do I count the number of non-empty cells in a row, including only the cells in a column that contain a certain value?

Here is my sample sheet:
https://docs.google.com/spreadsheets/d/1xxmTnRTaA0DPAyibbiXK00YPqhwT5NXH8kf8PtBh0jg/edit?usp=sharing

Enter the image description here

I am trying to create a formula for the "Total" column that contains the total number of non-empty cells for this row, counting only cells that are in a column that contains the value "B". (0 is not empty)

With this formula (for line 2 as an example) it is quite easy to find the sum of these columns:
=SUMIF($1:$1,"B",2:2)

However, for counting non-empty cells, I couldn't find a similarly easy way to do this. I have tried to achieve this with FILTER and COUNTIF, but I couldn't find out.

Replace the character-derived column SSIS

So I get CSV files and for some reason they come out as "123-45-9873".

Now I'm cutting out the "-", but the column is an SSN column, so "12345987 takes out the 3 and puts the" sign on the table.

Which replacement function can I use to replace the exact character?

For reference, this is what I'm doing

(DT_STR,9,1252)REPLACE(SSN,"-","")

and get "12345678

How do I automate the creation of new tabs in Google Sheets by adding a new value to a column?

The new tab would be formatted based on a different template tab if you could write the code with the following variables:

"Data Entry" is the name of the tab from which I want to use the value of a new entry in column A to automatically create a new tab.

"Template" is the name of the tab from which I want the same formatting for the new tab.

Plotting – Automatic generation of file names from column values ​​and automatic PlotLegends labels from file names?

I have a two-part question about the automatic generation of file names and plot legends, labels.

Part 1:
I usually work with very large data matrices with columns of the form:

MASTERdataset: {a-parameter, b-parameter, c-parameter, x-Var, y-Var}

An example record is below:

masterDATA = {{1200, 700, 150, 285.29323135045837`, 
    124.81439541987501`}, {1200, 700, 150, 286.60945594708426`, 
    126.30947680625`}, {1200, 700, 150, 287.92561104172626`, 
    127.73505620875001`}, {1200, 700, 150, 289.24169417515805`, 
    129.08867440106252`}, {1200, 700, 150, 290.5577029045866`, 
    130.367888590125`}, {1200, 700, 150, 291.8736347915017`, 
    131.5702602656875`}, {1200, 700, 150, 293.1894874091171`, 
    132.69336264156252`}, {1200, 700, 150, 294.5052583400343`, 
    133.7347783193125`}, {1200, 700, 150, 295.8209451736646`, 
    134.692096710125`}, {1200, 700, 150, 297.1365455140142`, 
    135.5629218201875`}, {1200, 700, 150, 298.4520569668892`, 
    136.3448594556875`}, {1200, 700, 150, 299.7674771578958`, 
    137.0355352228125`}, {1200, 700, 150, 301.0828037094413`, 
    137.63257152881252`}, {1200, 700, 150, 302.39803426883464`, 
    138.13361568262502`}, {1200, 700, 150, 303.7131664753014`, 
    138.5363069099375`}, {1200, 700, 150, 305.02819799763193`, 
    138.83831400093752`}, {1200, 700, 150, 306.34312649281645`, 
    139.0372939459375`}, {1200, 700, 150, 307.65794964948805`, 
    139.1309353780625`}, {1200, 700, 150, 308.9726651454576`, 
    139.116916108125`}, {1100, 700, 150, 285.20258900103653`, 
    136.52080780656252`}, {1100, 700, 150, 286.51872642831677`, 
    138.33050686200002`}, {1100, 700, 150, 287.8347978496311`, 
    140.07419995162502`}, {1100, 700, 150, 289.15080077907106`, 
    141.7494011668125`}, {1100, 700, 150, 290.4667327507974`, 
    143.3536446684375`}, {1100, 700, 150, 291.7825912998558`, 
    144.8844655020625`}, {1100, 700, 150, 293.0983739766935`, 
    146.339414115`}, {1100, 700, 150, 294.41407833772337`, 
    147.716046920125`}, {1100, 700, 150, 295.729701949836`, 
    149.01193080800002`}, {1100, 700, 150, 297.0452423911218`, 
    150.2246438690625`}, {1100, 700, 150, 298.36069724507706`, 
    151.35176959956252`}, {1100, 700, 150, 299.6760641116824`, 
    152.3909079801875`}, {1100, 700, 150, 300.99134059121155`, 
    153.33965928456252`}, {1100, 700, 150, 302.30652430565584`, 
    154.195645504125`}, {1100, 700, 150, 303.62161287225007`, 
    154.95648387356252`}, {1100, 700, 150, 304.93660393503`, 
    155.61981842875002`}, {1100, 700, 150, 306.2514951283887`, 
    156.1832835626875`}, {1100, 700, 150, 307.5662841177902`, 
    156.64454473943752`}, {1100, 700, 150, 308.8809685567008`, 
    157.0012554253125`}, {1200, 650, 140, 294.568511670944`, 
    152.44652887431252`}, {1200, 650, 140, 295.5572385555695`, 
    153.244130479`}, {1200, 650, 140, 296.5459073608416`, 
    153.983652730375`}, {1200, 650, 140, 297.5345169136301`, 
    154.663922498125`}, {1200, 650, 140, 298.52306602239275`, 
    155.28374823993752`}, {1200, 650, 140, 299.5115535361726`, 
    155.84197899893752`}, {1200, 650, 140, 300.49997828288434`, 
    156.33744269000002`}, {1200, 650, 140, 301.48833910612274`, 
    156.768982907625`}, {1200, 650, 140, 302.4766348644878`, 
    157.135458251875`}, {1200, 650, 140, 303.46486439545527`, 
    157.43570619850001`}, {1200, 650, 140, 304.4530265778131`, 
    157.668605535625`}, {1200, 650, 140, 305.44112027103967`, 
    157.83301574143752`}, {1200, 650, 140, 306.4291443464566`, 
    157.92780813756252`}, {1200, 650, 140, 307.41709769382004`, 
    157.95187248018752`}, {1200, 650, 140, 308.4049791815327`, 
    157.904077172125`}, {1200, 650, 140, 309.39278771614994`, 
    157.7833287685625`}, {1200, 650, 140, 310.3805221893237`, 
    157.5885189215`}, {1200, 650, 140, 311.3681814999415`, 
    157.3185465185`}, {1200, 650, 140, 312.3557645686668`, 
    156.97233222306252`}, {1200, 650, 140, 313.34327029479664`, 
    156.54877533212502`}, {1200, 650, 140, 314.33069761222396`, 
    156.046809738625`}, {1200, 650, 140, 315.3180454445702`, 
    155.4653590640625`}, {1200, 650, 140, 316.3053127179819`, 
    154.80334945506252`}, {1200, 650, 140, 317.29249838358635`, 
    154.05973203875`}, {1150, 600, 140, 298.23873411795546`, 
    164.05272120981252`}, {1150, 600, 140, 299.2273312653466`, 
    164.975212389875`}, {1150, 600, 140, 300.2158717606931`, 
    165.8410515253125`}, {1150, 600, 140, 301.20435441922945`, 
    166.64905385056252`}, {1150, 600, 140, 302.1927780834343`, 
    167.3980618443125`}, {1150, 600, 140, 303.18114157670266`, 
    168.0868989015625`}, {1150, 600, 140, 304.169443745497`, 
    168.7144114848125`}, {1150, 600, 140, 305.1576834377865`, 
    169.27944756325002`}, {1150, 600, 140, 306.14585949313545`, 
    169.7808467010625`}, {1150, 600, 140, 307.13397078169055`, 
    170.2174790450625`}, {1150, 600, 140, 308.1220161542818`, 
    170.58819542525`}, {1150, 600, 140, 309.1099944821404`, 
    170.89186707275002`}, {1150, 600, 140, 310.0979046420354`, 
    171.1273707566875`}, {1150, 600, 140, 311.08574549827347`, 
    171.293570783625`}, {1150, 600, 140, 312.0735159488727`, 
    171.38936517175`}, {1150, 600, 140, 313.06121487255257`, 
    171.4136326405625`}, {1150, 600, 140, 314.0488411653967`, 
    171.36526927356252`}, {1150, 600, 140, 315.03639373319686`, 
    171.24318086262502`}, {1150, 600, 140, 316.0238714652131`, 
    171.04625666781251`}, {1150, 600, 140, 317.011273287306`, 
    170.77342254962502`}, {1150, 600, 140, 317.99859810628294`, 
    170.42358531543752`}, {1150, 600, 140, 318.98584484300574`, 
    169.995665827125`}, {1150, 600, 140, 319.9730124318714`, 
    169.48859848168752`}, {1150, 600, 140, 320.96009978832717`, 
    168.90129872643752`}};

I partition the matrix into submatrices that correspond to the values ​​of the parameters (a, b, c) with:

SELECTfxn(data_,a_,b_,c_):=Select(data,#((1";;" 3))=={a,b,c}&)"

To plot the data or perform other manipulations, I need to delete the first three columns and assign a file name that uses the (a, b, c) parameters so that I can keep track of which one is later. So far I have done this "by hand" with many strategic copy / paste and find / replace processes. I have named the files according to the following scheme:

a1200b700c150 = SELECTfxn(masterDATA, 1200, 700, 150)((All , {4, 5}));
a1100b700c150 = SELECTfxn(masterDATA, 1100, 700, 150)((All , {4, 5}));
a1200b650c140 = SELECTfxn(masterDATA, 1200, 650, 140)((All , {4, 5}));
a1150b600c140 = SELECTfxn(masterDATA, 1150, 600, 140)((All , {4, 5}));

That costs a lot of time and is prone to errors. I would like an operation that does the following:

AUTOfileNAMES (data _): = "Execute the operation SELECTfxn (data_, a_, b_, c_) for all unique combinations of (a, b, c) and assign file names according to the values ​​(a, b, c) that are similar to those from "to" hand & # 39; example “

I am sure that this is possible, but I cannot find out. Can someone help?

Part 2:
The second part of my question is how this information can be integrated into PlotLegends.

I like the data with different groupings and look for patterns. Doing this by hand takes forever and I often make mistakes in the labels for PlotLegends. Is there a way to automatically assign legend labels using the following form:

a = 1200, b = 700, c = 150

Where do the values ​​(1200, 700, 150) come from file names of the form: file = a1200b700c150?

Here is an example of what the end product should look like:

fontsize = 16;
SAMPLEdataset = {a1200b700c150, a1100b700c150, a1200b650c140, 
   a1150b600c140};
ListPlot(SAMPLEdataset, PlotStyle -> PointSize(Large), Frame -> True, 
 Axes -> False, FrameLabel -> {"x", "y"}, 
 PlotLegends -> 
  Placed(LineLegend({"a=1200, b=700, c=150", "a=1100, b=700, c=150", 
     "a=1200,b=650, c=140", 
     "a=1150, b=600, c = 140"},(*LegendFunction(Rule)"Frame",*)
    LegendMarkerSize -> 20, LabelStyle -> Directive(Bold, fontsize), 
    LegendLayout -> "Column"), Right))

Has anyone done this before?

Thanks a lot!

database – mysql change column does not work

I want to update the comment_author field and change the type from tinytext to varchar (100).

For some reason it complains "Invalid default value for & # 39; comment_date & # 39;"

Why does it complain if I don't even try to change it?
The command I use is:

 `mysql> Alter TABLE `wp_comments` MODIFY COLUMN `comment_author` VARCHAR(100);` 

Enter image description here

Enter image description here

Thanks a lot!

Select a table and replace column fields with values ​​from multiple tables in MySQL

I am no longer familiar with MySQL. I worked in MSSQL. Currently table column fields must be selected, which are replaced by values ​​from several tables.
I tried a few questions but didn't get the right result because it has some inner options.
Please refer to the violin.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=15c6f66c363ab16a3437a3911c266bde

Please advise me on this

thank you in advance