I would like to run a query that runs monthly but it will check daily if the timestamp from the records is from the previous month. If yes then it will run the query and append to the previous monthly data. So, I want to create a simple function to return TRUE if the RecordTimestamp is from the previous month…
SQL good practices: should I use associative tables in this example?
I want to link two tables: a “company” table and a “branches” table. I find two ways to do it: Method 1: Method 2: In practice, I think that both ways work the same, but professionally speaking, I think Method 2, using associative tables, would be the proper way to do it following SQL …
Conversion failed when converting the varchar value ‘ AND ID =’ to data type int
I was looking how I can parameterize table names and so I found dynamic sql queries. I finally got the proc saved, but when I execute it errors out with “Conversion failed when converting the varchar value ‘ AND ID =’ to data type int.” I have no idea what is going wrong when I try to …
create rows from columns in a apache spark dataset
I’m trying from a dataset to create a row from existing columns. Here is my case: InputDataset accountid payingaccountid billedaccountid startdate enddate 0011t00000MY1U3AAL 0011t00000MY1U3XXX 0011t00000ZZ1U3AAL 2020-06-10 00:00:00.000000 NULL And I would like to have sometthing like this accountid star…
Translating Oracle Date Functions to BigQuery
I’m having trouble translating these Oracle date functions to BigQuery because BigQuery doesn’t allow such user-friendly additions to dates/timestamps. Any help is appreciated. Below are the two date functions in Oracle and my attempted (failed) versions in BigQuery: Oracle: BigQuery attempt: Answ…
Pyspark: How to flatten nested arrays by merging values in spark
I have 10000 jsons with different ids each has 10000 names. How to flatten nested arrays by merging values by int or str in pyspark? EDIT: I have added column name_10000_xvz to explain better data structure. I have updated Notes, Input df, required output df and input json files as well. Notes: Input datafram…
Why does this inner join of two tables create duplicate rows
I have the following tables: Users Conversations Group_Members I need to select all the conversations where a user with a specific ID takes part in. Users and Group_Members are in a many-to-many relationship. Why does the following query create duplicate rows on the last select, as seen in this image? User.Id…
ETL – Update Tables with potentially new records or updated records
I am trying to accomplish the following table update via an insert for an etl process: Add in a new records that did not exists Update any records that had updates Keep all the records in place that did not change On the table I have a unique_id and an updated_date field when the record was updated ( or could
How to use T-SQL to select N rows every N rows?
I have a few hundred rows of data numbered with SELECT ROW_NUMBER() OVER (ORDER BY LastName). I need to skip 3 of those rows every 3 rows in that selection. So I need to select rows 1, 2, 3… then …
How do you Alter a column in Oracle SQL to only allow it to take in two values, either a Y or a N?
Most of the results I have seen were for MySQL. I understand that Alter Table is to be used, as well as MODIFY, but after that I am stuck My table in question is this: CREATE TABLE store_reps ( rep_id …