I have a table with column name date of type timestamp I’m trying to delete old rows with the following query: delete from my_table where date<(now()-'30 days'::interval) but all rows are deleted. …
Tag: date
Expanding Date Ranges
I want to expand the date ranges in the below “Original Table”. The end result should be the “Resultant Table”. I know that this can be done in sql using dateadd and ctes. However, …
How to write this kind of query in Oracle
I have a MySQL database and I want to migrate all query to Oracle and since I am not an expert in Oracle SQL I am stuck here. I have query like this My question is: How to write this ORDER BY expression in Oracle SQL? Answer You can use There is no column naming format containing back ticks in
Compare two MYSQL tables and return missing dates and group by column
I have two tables. I want to compare table A with B and get the missing date from with name of the user. TABLE A TABLE B EXPECTED OUTPUT Also, Along with it can I get the count of the missing dates for each user if possible? Answer You must cross join Table B to the distinct names of Table
Count distinct per fiscal year and display all dates in query result
DB-Fiddle: CREATE TABLE customers ( id int auto_increment primary key, order_date DATE, customerID VARCHAR(255) ); INSERT INTO customers (order_date, customerID ) VALUES (“2020-01-…
Presto – how is there an alternative to to_char like postgresql?
I need to build a query in presto that could look back the trailing 70 days, the table I am working with is storing the dates in the format of ‘YYYYMMDD’. in postgresql, I can just simply write the …
SQL count distinct # of calls 6 months prior to create date
Am trying to figure out the SQL to: count # of distinct calls made on an account 6 months prior to the account being created I also need to CAST the date field. I’m thinking something like: case …
tsql to find members not contacted in past 18 months
I have a table that has ContactNumber (Bigint), MemberNumber(bigInt) and ContactDate(date). The table has millions of records (sample data image attached). I want to get all the records where member has not been contacted for last 18 months. How do I do that using tsql? Your help is highly appreciated. Thanks…
mySQL question on how to add more info to a column
Got this table containing info about customers, dates and their purchases values. Let’s say some of them bought something in November but nothing in December. Now, I am trying to have all the customers + all they spent and to use IFNULL() to the ones that have not bought anything in Dec. I am filtering …
SQL subquery with aggregate value
Sorry for the vague title, I’m a little lost here. I have two simple aggregate SQL queries, but I struggle to combine them in a functional way (likely through a subquery) in IBM DB2. Main goal is to run COUNT function in INVOICES table – but with the starting date based on a subquery MAX aggregate…