All Questions
Tagged with recursive-query sql-server
523 questions
0
votes
1
answer
95
views
CTE query for hierarchical data excluding some records despite having full hierarchy [closed]
I have a table that stores hierarchical data, where each record may have a parent_item_code referencing another record in the same table. I'm using a Common Table Expression (CTE) to build the full ...
- 3
0
votes
2
answers
81
views
How to find the predecessor of the predecessor
I am trying to use an SQL-query to find the predecessor municipality of a municipality or, if available, other predecessor municipalities.
Here is my input table (MUNICIPALMERGE):
municipality_id
...
0
votes
0
answers
56
views
Calculate rolling adjusted date based on date from parent row - lag / recursion
Given the data set below, I need to calculate a rolling AdjustedHireByDate. Each AdjustedHireByDate should be @NumWeeks weeks from the previous row's AdjustedHireByDate (if they have the same parent) ...
- 772
0
votes
2
answers
83
views
Suppress Duplicates in SQL Server Recursive Query
I've got a data structure where a record represents a person. A person can have a relationship to another person. There are different types of relationship so it's possible that there can be multiple ...
- 165
0
votes
1
answer
78
views
Get all hierarchy in SQL Server
I have a table like this:
ID_employee
ID_Manager
1
1
2
1
3
1
4
2
5
3
6
5
7
5
8
5
(1 is a boss)
What I would like is a result like:
ID_employee
ID_Manager
8
5
8
3
8
1
7
5
7
3
7
1
6
5
6
3
6
1
5
3
5
1
4
...
- 27
0
votes
1
answer
112
views
Get sibling and parents with recursive SQL Server query
I have this table
Id
ParentId
1
NULL
2
1
3
1
4
NULL
5
4
6
8
7
9
Each child (Id) can have only one parent.
How to retrieve parents and sibling for a given child Id, it means:
SELECT `Here the code`
...
- 517
-1
votes
1
answer
125
views
Recursive CTE and performance issue when anchor member is a large table
I have made some edits so that the post is clearer.
I have a large table with data like below.
The goal is to be able to retrieve the original ID and location associated with the original ID given ...
- 55
-1
votes
3
answers
86
views
Conditional recursive query in SQL Server
I have these 2 tables:
TABLE 1
Id
Shortname
ParentId
C1
Child1
P1
P1
Parent1
GP1
GP1
GrandParent1
NULL
C2
Child2
P2
P2
Parent2
GP1
TABLE 2
Id
Setting
GP1
5
C2
1
The logic is the following: if we don't ...
- 517
-2
votes
1
answer
69
views
Find which stores have been transferred to which store that is currently active
I have a stores table which takes in storeid, isactive and transferstoreid.
id= the id of the store
isactive= whether the store (id column) is active or not
transferstoreid = the new store id to which ...
user24280727
-3
votes
1
answer
91
views
Parent Child table with "N" number of levels [closed]
This query:
SELECT pcn.id,
pcn.config_id,
pcnc.nombre_nivel,
pcnc.orden_nivel,
pcn.nivel_padre_id,
pcnc.empresa_id,
pcnc.proyecto_id,
pcnc.activo AS ...
- 57
0
votes
0
answers
185
views
Calculating a value in SQL using previous row's values and then using current row value for future calculations
In SQL Server, I have to perform calculations on the following table:
Row#
Period_begin_dt
Period_end_dt
Amount
Period_Status
Time_Period
1
2023-07-01
2023-09-30
0
Delinq
Quarterly
2
2023-04-01
2023-...
0
votes
1
answer
203
views
How to do SQL recursive logic
I have this sample dataset like this ;
acctid
payseq
totalamount
acctnum
expectprice
payorder
aac1
99
1000.00
1111aa
800.00
1
aac1
99
1000.00
1111bb
800.00
2
aac1
99
1000.00
1111cc
800.00
3
aac1
99
...
- 1
0
votes
2
answers
213
views
Update chain of rows in which each row will affect next one through a computed column
In a banking DB we want to hold the transactions and remainder amount of accounts after each transaction in a table named 'Operation' like this:
CREATE TABLE Operation (
Id int PRIMARY KEY,
...
- 3,293
1
vote
1
answer
139
views
Is recursive variable self-assignment valid?
I found some T-SQL code, and i'm trying to figure out if it is:
an elegant solution that i never knew about
a horrible abomination that happens to work, and should absolutely be removed
or somewhere ...
- 258k
0
votes
0
answers
67
views
SQL Multiple Calculation in Same & Next Row
I have use case to do multiple steps of calculation in SQL Server.
Basically, I need to fetch the result from previous calculation, re-calculate in current row, then send the value to the next row.
I ...
- 1