Is it possible to group records based on a predefined date range differences (e.g. 30 days) based on the start_date of a row and the end_date of the previous row for non-consecutive dates? I want to take the min(start_date) and max(end_date) of each group. I tried the lead and lag function with partition by i…
Tag: sql
Failed to execute query. Error: String or binary data would be truncated in table xdbo.user_info’, column ‘uid’
I have problem inserting values in my SQL server database on Azure, I am getting the following error: I don’t understand where I am wrong, I just created the server, and I am trying to experiment but cant fix this. Creating the table works fine, the only thing that doesn’t work is the second comma…
PostgreSQL: Sub-Query on one table
My table looks (simplified) like this: It records filenames “File”; sometimes, a file (here BBB) is replaced by a newer one (here CCC”. First show all records, that have been replaced: OK, brings up line 2. Now I need to add a subquery column QQ, which shows the File of the substitute record…
Calculate total revenue made from each item broken down by country and day
I have a table which consists: id country Date item_name price ae3u2 USA 27/12/2018 budget 1.99 bf5d8 India 31/12/2018 everything 34.99 dc8a4 USA 22/01/2019 cars …
Convert multiple self JOINs to window function or subqueries (SQL)
I have a query with several self joins: Here is a sample table where in yellow I marked the record that is within the boundaries of the where condition: Here is the expected result view: Basically I filter one table with several consecutive conditions and show the result as a table. Is there a way, using wind…
insert Into and Union of Tables
I’m using MS-Access. I have a table A looks like : I need to insert into a table B that looks like: Can I do it with an SQL instruction? Answer If the id field in table B is an Autonumber and ordering of the values is important, then you can use keep in mind that the value field name
JOOQ “IN” Query throws null pointer exception
When we try to fetch data with Null values field(TABLE_NAME.COLUMN_NAME.in(null)) with IN clause getting null pointer exception. Maybe because of this. In the database, we can provide null in IN clause. There is an existing “won’t fix” issue in jooq https://github.com/jOOQ/jOOQ/issues/3867 T…
Select query on MYSQL table taking long time and getting timed out
I have a mysql table with 2 million rows, when I’m running any select query on the table it’s taking long time to execute and ultimately it does not return any result. I have tried running select query from both Mysql Workbench and terminal, it’s the same issue happening. Below is the table:…
How to sum few colums values for each type and calculate percentage?
I have below query which gives me data with three columns – type, amount and total for current week using week_number column. Below is the output I get back as of now with above query: Problem Statement Now I need to calculate the percentage for each type by using below formula: For each type (sum(total…
Get Row based off Date and previous row
I’ve got the following sample data and what I’m trying to do is based off the DateChanged value is get the appropriate Amount from the linked table along with the previous row. The results would then display as How could this be done? What I have so far is this but wondering is there’s a bet…