In MYSQL 8.0 I have two tables with following structure: I am trying to update a value of EnromentStatus in child table by joining in on Enrolment table using the TSQL below: Problem is Enrolment has multiple entries for the child enrolment so I need to do MAX(DateUpdated) to get just a single record to updat…
Output records with null values even if not present
I have a table as below in Oracle gen_id serial_code is_verified 1 fmcg Y 1 smcg Y 1 xmcg N 2 smcg Y 2 fmcg Y 2 2mcg Y 3 smcg Y 3 amcg Y Now I want the output for max gen_id which is 3 in this case and serial_code ‘smcg’ and ‘fmcg’ I can get the output easily
SQL Generate per day records between dates
I have a table that where each record has start and end dates. I need to return a record for each day between start and end date fields (including the start and end dates). Using MS SQL Server. Example: Current data Data required: Looking for recommendations. Thanks. Answer You can use recursive cte :
Inputting data into a table from two different tables
I have these 3 tables with data and then created a third table that looks like this I want to populate my new table with the Subjects who were involved in projects that were lead by Dawn Elgar (PID is 10001). Is there a way to do that across 3 tables? I am close with code that looks like this
R: “Fuzzy Match” and “Between” Statements
I am working with the R Programming Language. I have the following tables (note: all variables appear as “Factors”): I am trying to “join” (e.g. inner join) this tables on the following conditions: 1) if table_1$id “fuzzy equal” table_2$id AND 2) if table_1$date BETWEEN(tab…
How to select cumulative counts by group over time across in postgres
I have cumulative counts for two groups over time in this format: Date Group Cumulative Count 1/1/2020 A 1 1/2/2020 A 3 1/2/2020 B 1 1/3/2020 B 2 And I’d like to reshape this data into this format: Date Group Cumulative Count 1/1/2020 A 1 1/1/2020 B 0 1/2/2020 A 3 1/2/2020 B 1 1/3/2020 A 3 1/3/2020 B 2
How to transform a postgresql select with join to a json object?
I want to transform the result from a select with joins into a json object. I mean this query: should output this: (there are more fields than these. I’m just making the example simple) I found a solution this way: But I think there should be a much better way to do this. Imagine that chat_messages had …
PL/SQL CREATE PROCEDURE – Salary increase based on tenure
I have worked on this for a while but the code did not work and I could not figure out the correct solution. Did I miss something from the code? Thank you. — Question – The company wants to calculate the employees’ annual salary: –The first year of employment, the amount of salary is the base sala…
Trigger for INSERT to validate input data in SQL Server?
My problem: table dbo.student has StudentID like SV001. How can I create a trigger to check data inserted into dbo.student has a StudentID that begins with SV and the numbers in the range 000 to 100? Example: SV099 is valid id to insert, while SV101 is not valid Answer Use SQL constraints: Example : Demo in d…
how to update and append values to array in google spanner
I have a column with data type Array(String) in spanner. How can I append value to an array for update queries I’m able to update the values using this command But the problem with this update is it overrides the previous values. I want to append these values to the ones that are already present. Is the…