I Have a SQL Server database with a table named Balance The table: Id Date IN OUT Balance 3345312 2022-08-07 100 50 250 5435245 2022-08-06 50 50 200 4353451 2022-08-05 0 100 200 5762454 2022-08-04 20 100 300 7634523 2022-08-03 400 100 380 5623456 2022-08-02 100 20 80 4524354 2022-08-01 0 0 0 Id = Unique Ident…
Tag: recursion
Recursively Conditionally get parent Id
I have two tables: Budget Line and Expense. They are structured in such a way that an expense must have either a parent record in the budget line table, or a parent record in the expense table. I need to select all child-most expenses for each budget line. For example – BudgetLine: Id Description 1 TEST…
Postgres – How to achieve UNION behaviour with UNION ALL?
I have a table with parent and child ids. Each parent can have multiple children and each child can have multiple children again. The data looks like this. I’d like to find all children. I can use a recursive cte with a UNION ALL. This gives me the result I’d like to achieve. However I’d lik…
Cycle detected while executing recursive query
I am using CTE recursive query to get below output but don’t know why it is throwing “cycle detected while executing recursive WITH query”. Can anyone please tell me where is wrong with my query? my query: SELECT order_id, product_id, quantity FROM cte; table/data script: Answer In the recur…
Postgres recursive queries and window functions to produce tree from table
Let’s say I have the following data. I got the request to produce a table with a single column in which categories and subcategories appear arranged like this. I don’t understand how to complete the task with the requirements established by my client (especially the first one). These are: I should…
Follow the connections : recursive query
i have two columns of numbers. the goal is starting from a number f.e. 55, to extract all ‘connected’ numbers (starting with any of those numbers, should yield the same result) in this case all numbers shown here : 55,56,35,69,60,22,47,2,26 I use the following query : but I get back only those bac…
Recursive query in tree MySQL8 / MariaDB10
I have a tree-like structure for categories on my database: create table cat( id int auto_increment primary key, url varchar(50), parent int references cat(id) ) How can i build the …
SQL SERVER: OPTION(MAXRECURSION n) not working with dynamic variables
Techies, This more or less works when the date range is between 1/1/-1/3. I have 2 problems I need to solve. The first is that SQL Server will not let me do this: OPTION (MAXRECURSION @recdays). The next, I am pulling results for 1/4, when I only want results up to 1/3. Any advice for getting this to work? I
SQL: execution model for recursive CTEs
I’m trying to understand how recursive CTEs are executed and particularly what causes them to terminate. Here’s a simple example: WITH cte_increment(n) AS ( SELECT — Select 1 0 UNION …
Using sql to recursively generate values depending on keys and column values
I have a table like this: store item value store1 item1 2 store1 item2 3 I want to use it to create the following table: store item value store1 item1 0 store1 item1 1 store1 item1 2 store1 …