I am using SQL Server Management Studio 18 against SQL Server 2016. I have some material that are in batches and those batches have expiration dates that are held as strings, but are basically in the format of ‘yearmonthday’, e.g. ‘20210312’ for March 3rd, 2021. My goal is to only see …
Tag: sql
How to create partitioned table from other tables in Amazon Athena?
I am looking to create a table from an existing table in Amazon Athena. The existing table is partitioned on partition_0, partition_1, and partition_2 (all strings) and I would like this partition to carry over. Here is my code: Trying to run this gives me an error at the FROM line, saying “mismatched i…
find the consecutive values in impala
I have a data set below with ID, Date and Value. I want to flag the ID where three consecutive days has value 0. id date value 1 8/10/2021 1 1 8/11/2021 0 1 8/12/2021 0 1 8/13/2021 0 1 8/14/2021 5 2 8/10/2021 2 2 8/11/2021 3 2 8/12/2021 0 2 8/13/2021 0 2 8/14/2021 6 3 8/10/2021 3
query to get data between date ranges and the padded data around
suppose this is my data in a table in the database and this is my query to fetch data I would also like the query to return one date before and after this range. so like the dates padded. Now how can i do this efficiently. One way could be to get the dates in the ranges and then get
SQL: calculate the sum of each column and convert them into rows
My current table includes more than 100 columns and I need to calculate the sum of each column and convert them into rows. Since there are more than 100 column, it is not convenient to use the unpivote clause. Is there any other way to do that? Below is a snapshot of the original table: col1 col2 23 44 33
Bigquery – How to filter records with certain condition
I’m trying to write SQL (in BigQuery) the following in order to get a result that satisfies the following conditions. for ex: my table contains the following data I want to filter out records where it contains only value as “p” from the table. The expected result would be I have tried with t…
Remove duplicate values from comma separated variable in Oracle
I have a variable (called: all_email_list) which contains 3 email address lists altogether. (I found some similar question but not the same with a proper solution) Example: test@asd.com, test2@asd.com,test@asd.com,test3@asd.com, test4@asd.com,test2@asd.com (it can contain spaces between comas but not all the …
SQL query for entries of last N jobs
I have the following table: etc, so basically jobs keep adding entries concurrently I am looking for an efficient query to return all entries of the latest (by time) N jobs So find the first (by time) entry for each job, then using that get the latest N jobs with all their entries So for the above table and N…
Optimising filtering multiple SQL count sub queries with SQLAlchemy
I’m trying to convert raw postgres-SQL into sqlalchemy which uses a count and filter This works great and is very fast (sub 100ms – even when filtering 20 fields) I’ve written the sqlalchemy as (simplified) This produces the following SQL This works but is quite slow (+1 second). I’m n…
how do i insert value into a column and referring the previous value at the same time of that column in a Table in Oracle?
I have a table where I have to insert values in Final_value column Explanation of the task First value of the Final_value column will be = 1st value of column1 i.e 5.5 From the 2nd row we will check if column2 is Not Null a) if TRUE , then formula would be previous Final_value column* ( current column1 value/…