replication – How to ship new rows from the source to a target server?

Context

We have a SQL Server ‘source’ where daily 10M rows inserted to a table. No updates. I would like to ship the new rows to a ‘target’ server, then delete those (sooner or later) on the source where disk space is limited. PK is monoton increasing identity column.

Question

Besides of programmed export import which based on administering which PK ranges which were shipped, is there any more efficient solution?. I have very limited DBA knowledge, my first idea was differential backup with recovery model simple, then some log shipping with recovery model full, but I think both cases the deletes on the ‘source’ will by applied on the ‘target’ which I do not want to happen.

mysql – Getting only 4 rows per idea from ideas table

I have made one idea submission portal for diffrent ideas submitted by school students, as i am beginner in mega database management its really hard for me to write a query that retruns only 4 rows per idea and so on

for ex: for idea id DC889 > i need only 4 records but table contans 50 records. i tiried using having function or count function but not able to find out feasible soultion.

requesting expert guidance on the same so that i can apply my own login to write the query.

python – Merge rows in a pandas DataFrame while ignoring specified values and checking for conflicts

the problem

I have a set of dataframes where each row should have a unique ID value, but sometimes imported data has multiple rows with the same ID. I want to do a groupby operation or something similar that checks each ID group for special characters indicating “no data,” checks that each group column has exactly one non-special value (which could be repeated in multiple rows) and then collapses ID groups into one row whose column values are either unique non-special column values from the group or special values in columns where no row in the group had data.

I want the code to throw an error if there are multiple different non-special values in a group column. So if the ID column label is 'id' and special characters are in 'x?' then col2 below should raise an exception

  id src col1 col2 col3
0  f   x    5    m    1
1  f   a    5    n    ?

I’ve put together the following solution but I think there must be a way simpler method. My main question is whether I can do this with more native pandas code instead of using multiple helper functions as I’ve done below.

my code

I’m currently accomplishing this with a function called merge_rows that uses three helper functions:

  • get_nonspecial_values gets all non-special values in a column
  • check_column_consistency checks if a column has exactly one non-special value
  • get_inconsistent_ids gets a (hopefully empty) set of ID values whose rows conflict with each other, like with ID value 'f' above.

I think the last two lines in get_inconsistent_ids and merge_rows could probably be done with a built-in pandas operation which would then make a bunch of the code I’ve written unnecessary, but I can’t figure out what that operation would be (I’m also not sure those are the lines to focus on when revising this code).

import pandas as pd

def get_nonspecial_values(series, specials):
    '''
    takes a series representing a column and returns all 
    values in the column that are not in specials
    '''
    counts = series.value_counts()
    return (v for v in counts.index if str(v) not in specials)

def check_column_consistency(series, specials):
    '''
    takes a series representing a column and returns True
    if there is exactly one value that is not in specials
    '''
    nonspecial = get_nonspecial_values(series, specials)
    if len(nonspecial) not in (1, 0):
        return False
    else:
        return True

def get_inconsistent_ids(df, specials, id_column):
    '''
    takes a dataframe, special values, and a column label
    returns all ID values that represent a set of rows in which
    at least one column has multiple different nonspecial values
    '''
    where_nonunique_ids = df(id_column).duplicated(keep=False)
    if not any(where_nonunique_ids):
        return ()
    else:
        rows_with_nonunique_ids = df.loc(where_nonunique_ids)
        check_group = lambda x: all(x.apply(check_column_consistency, args=(specials,)))
        where_ids_consistent = rows_with_nonunique_ids.groupby(id_column).apply(check_group)
        return (v for v in where_ids_consistent.index if not where_ids_consistent(v))

def merge_rows(df, specials='', id_column=''):
    '''
    performs the merge operation described above
    '''
    inconsistent_ids = get_inconsistent_ids(df, specials=specials, id_column=id_column)
    if len(inconsistent_ids) > 0:
        raise ValueError('found the following ID values with inconsistent rows', inconsistent_ids)
    rows_with_nonunique_ids = df.loc(df(id_column).duplicated(keep=False))
    unique_column_value = lambda x: (get_nonspecial_values(x, specials) or ('x'))(0)
    get_group_values = lambda x: x.apply(unique_column_value)
    values_by_id = rows_with_nonunique_ids.groupby(id_column).apply(get_group_values)
    return pd.DataFrame(data=values_by_id.values, columns=df.columns)

df = pd.DataFrame({'id' : ('a','b','c','c','d','d','d'),
                   'src': ('x','a','a','a','a','a','a'),
                   'col1':( 5,  5, '?','?', 5,  5, 'x'),
                   'col2':('x','n','n','x','n','x','x'),
                   'col3':( 1 , 3 ,'?', 9 ,'?', 8 ,'x')})

print(merge_rows(df, specials='x?', id_column='id'))

The code above gives me the following output

  id src col1 col2 col3
0  c   a    x    n    9
1  d   a    5    n    8

Hiding Rows based on cell value in columns

I am trying to hide rows with either conditional formatting or formula. I am looking to hide all rows that contact a zero value in column k and also hide rows with any value at all in column L. I can’t get the formula to work properly and I can’t get macro or VBA to run how I need it to…
ScreenShot

mariadb – Get rows above and below a certain row, based on two criteria SQL (with SUM)

Say I have a table like so:

+----------+---------+------+---------------------+
|student_no|level_id |points|      timestamp      |
+----------+---------+------+---------------------+
|     4    |    1    |  70  | 2021-01-14 21:50:38 |
|     3    |    2    |  90  | 2021-01-12 15:38:0  |
|     1    |    1    |  20  | 2021-01-14 13:10:12 |
|     5    |    1    |  50  | 2021-01-13 12:32:11 |
|     7    |    1    |  50  | 2021-01-14 17:15:20 |
|     8    |    1    |  55  | 2021-01-14 09:20:00 |
|    10    |    2    |  99  | 2021-01-15 10:50:38 |
|     2    |    1    |  45  | 2021-01-15 10:50:38 |
+----------+---------+------+---------------------+

What I want to do is find the total points for each person (student_no), and show 5 of these rows in a table, with a certain row (e.g. where id=5) in the middle and have the two rows above and below it (in the correct order – with highest at the top). This will be like a score board but only showing the user’s total points (over all levels) with the two above and two below. So because points could be equal, the timestamp column will also need to be used – so if two scores are equal, then the first person to get the score is shown above the other person.

I have tried this below but it is not outputting what I need.

SELECT 
    student_no, SUM(points)
FROM
    (
    (SELECT 
        student_no, SUM(points), 1 orderby
    FROM student_points a
    HAVING
        SUM(points) > (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
    ORDER BY SUM(points) ASC LIMIT 3) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 2 orderby
    FROM student_points a
    WHERE student_no = 40204123) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 3 orderby
    FROM student_points a
    HAVING
        SUM(points) <= (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
            AND student_no <> 40204123
    ORDER BY SUM(points) DESC LIMIT 3)
    ) t1
ORDER BY orderby ASC , SUM(points) DESC

This is a dbfiddle of what I am trying:
https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=5ada81241513c9a0be0b6c95ad0f2947

Google Sheets split into new rows while duplicating surrounding row entries

I am creating a spreadsheet for a service drive my non-profit is doing. As people sign up through a Google Form, the information comes in like this:

Undivided

However, to make it easier on our volunteers, it would be beneficial to have the information split up into a smaller pieces and be more uniform.

I need a way for the sheet to do the following:

  1. A way to copy the Parent’s First and last name, as well as their Phone Number and Building Pickup number (I.e. Columns A-D), while giving a separate row for each of their children (+their needs on the same row.)
  2. A process that can handle a variable amount of children
  3. An automated process of handling this information.

The sheet should look like this:
divided

I would greatly appreciate your help!

google sheets – How to split two-line rows into separate rows

Editable spreadsheet here:

https://docs.google.com/spreadsheets/d/1cHf9zC00LymZgb17J746ZUoD-YRG1UvIUQQXVlovBrE/edit?usp=sharing

You can see each non-blank row contains two lines. I’m trying to find a way to split these lines into separate rows. Columns C and I do not have two lines of text, I’m fine with sticking that text in one of the split rows or duplicating the content into both rows.

I add new data daily, so ideally a script that I could run daily to split the newest content that I have added to the bottom of the sheet.

enter image description here

google sheets – Count number of rows with a specific MONTH in the date of a specific cell and a boolean condition in another cell

Let’s say I have a number of dates and whether they occupy full or half a day…

   DATE  |         WHAT        | HALF?
...
2021-4-1 | Something Something | false
2021-4-2 | Something Something | false
2021-4-5 | Something Something | false
2021-4-19| Something Something | true
2021-5-13| Something Something | false
2021-5-24| Something Something | false
2021-9-13| Something Something | true
...

I now want for each month the amount of half-days occupied in that list.
(e.g. for April that should be 1 in the examples above, for May that should be 0)

I.e. in pseudocode, I’m looking for something like

=COUNTIF(A8:A, `MOTH of row = MONTH(E2)` AND `half of row = true`)

where E2, in this case, is e.g. January stored as a date

and A8:A contains the dates of the above table (i.e. B8:B contains the WHAT and C8:C contains the HALF?)

partitioning – Convert multiple rows into different columns – SQL

I am using teradata for some data modification. My table current looks like:

|Appl_id| Account_no|

|123 |40001|

|123 |40002|

|125 |40233|

|125 |40234|

|125 |40235|

|126 |40647|

and I want to convert this into :

|Appl_id|Acc_no1|Acc_no2|Acc_no3|

|123 |40001 |40002 | – |

|125 |40233 |40234 |40235 |

|126 |40647 | – | – |

Since the number of accounts can go upto 9, I would not be able to use min max average as a work around.Not sure if partition by would be applicable here as well.

What I think I can do is order by appl_id, Account_no and then select row1, row2, row3…but I am not sure if I can do that.

Please suggest

thanks

Excel: how to move “answers” rows into under column “questions”?

How do I move answers from other column to same question under another column, can any one guide the exact formula to achieve this? See example below what needs to be achieved as shown in picture.

Excel to move coloumn