I have scenario when I have to iterate through multiple tables in quite big sqlite database. In tables I store informations about planet position on sky through years. So e.g. for Mars I have tables Mars_2000, Mars_2001 and so on. Table structure is always the same: Thing is that for certain task I need to it…
Oracle SQL to delete the records except last 7 days/1 week
I am using Oracle SQL DB for ERP JD Edwards. Dates are stored in Julian Format for this ERP. We usually use this code to convert date from Julian to normal format. decode(szupmj,0,’ ‘,to_char(to_date(1900000 + olupmj,’YYYYDDD’),’MM/DD/YYYY’)) However we have a requirement w…
Sum of column values inside Json array with group by
I have next Django model. I store JSON data in wh_data. Note: it’s data for one row – 2022-06-06. I need to calculate the sum inside stocks by grouping them by wh and by created_at so that the output is something like this I know how to group by date, but I don’t understand how to proceed wi…
Presto SQL save query results in variable
I have a database that I am querying with athena. I am using subqueries to select a subset of the data like so can I save the query results of in a variable VAR so that we need not query it again and again and also to make query look cleaner? Answer There is no such concept as variable in
Improve SQL: NOT LIKE being repeated 3 times to filter out 3 different strings
I am new at SQL so any help is appreciated. How can I improve on the following conditions in my where clause: Is there a way I can do this in one line or is there a better way of doing the same. Answer Convert the string literals to upper-case and get rid of the second line as it is
Dividing two SQL queries
I am not super familiar with SQL queries, but I am trying to divide the results of two queries, ex. # of users with at least $x / total # of users, and I keep running into an error. Here is the code: I tried to do it without the y as well, and it gave me an error still.
Removing SQL Rows from Query if two rows have an identical ID but differences in the columns
I´m currently working stuck on a SQL issue (well, mainly because I can´t find a way to google it and my SQL skills do not suffice to solve it myself) I´m working on a system where documents are edited. If the editing process is finished, users mark the document as solved. In the MSSQL database, the correspond…
SQL: partition by / window function with the combination of 2 columns, separated by todays date
I am trying to create a materialized-view in postgres, where I sum 2 columns, depending on todays date within a window function. In the below picture there is an example of the query I am trying to achieve: If today is the ‘2022-06-06’: 2022-06-05: 1+2+3+4+5 2022-06-06: 1+2+3+4+5+6+107 2022-06-07:…
Spark SQL column doesn’t exist
I am using Spark in databricks for this SQL command. In the input_data table, I have a string for the st column. Here I want to do some calculations of the string length. However, after I assign the length_s alias to the first column, I can not call it in the following columns. SQL engine gives out Column …
Need oracle query that merges two rows with user ids into one row with multiple user columns
I have data in a large table that looks like this: case_nbr event_id event_desc User_ID 201 1001 Start Discussion 201 1002 Push Agent U67800 201 1003 Accept U67800 201 1004 Transfer U67800 201 1005 Push Agent U67999 201 1006 Accept U67999 201 1007 Transfer U67999 201 1008 Push Agent U11800 201 1009 Accept U11…