All Questions
Tagged with recursive sql-server
84 questions
2
votes
1
answer
79
views
Recursively sum child's nominal to parent, on dynamic level parent-child structure
i have data look like this :
i try to sum the Nominal column to each respective parent, so it will looks like this :
i provided the sql script for the data :
SET ANSI_NULLS ON
GO
SET ...
0
votes
1
answer
215
views
Convert numbers to words | Spell out DECIMAL USD 1259.75 as 'One thousand two hundred fifty-nine dollars and seventy-five cents'
ALTER FUNCTION [dbo].[NumberToWords](@Number DECIMAL(18, 2))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Words NVARCHAR(MAX)
SELECT @Words =
COALESCE(@Words + ' ', '') +
CASE
WHEN @...
- 25
1
vote
2
answers
547
views
How can I optimize a recursive CTE inside a IVTF?
I have a recursive CTE inside a inline table-valued function. The ITVF is returning a list of IDs containing a long sequence of ancestors for a person, it usually loops back about 12 to 18 times ...
- 121
3
votes
2
answers
298
views
How to sum up the distinct Total Time of an Event ignoring duplicate overlaps in Times?
I have the following EventTimes table:
DROP TABLE IF EXISTS dbo.EventTimes;
CREATE TABLE dbo.EventTimes
(
EventTimeKeyId INT IDENTITY(1,1) PRIMARY KEY,
EventId INT NOT NULL,
StartTime TIME NOT ...
- 39.5k
-1
votes
1
answer
81
views
My recursive query runs with no end in sight. Any idea as to how to solve this?
With A as(
select client_id, convert(date,admission_date) start_date,
year(convert(date,admission_date)) start_year,
month(convert(date, admission_date)) start_month
from ...
- 21
-2
votes
1
answer
211
views
I want to make a tree of parents only in sql server
My table in the database
How can I get my result as Where child = 'chl1'?
8
votes
2
answers
679
views
How can I efficiently traverse graph data with this pattern?
I have some relations embodying a directed acyclic graph that includes patterns similar to the following:
I'm looking for an efficient way to traverse this graph data. Here is an example of the ...
- 439
0
votes
0
answers
3k
views
How to fix error "Line 25: The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
I added OPTION (MAXRECURSION 1000) at the very bottom but it was not enough. My query:
Use DatabaseName;
DECLARE @SOURCE_TABLE_NAME NVARCHAR(200) = 'WorkOrderVersion'
DECLARE @SOURCE_SCHEMA_NAME ...
- 1
1
vote
0
answers
212
views
How to write query to link complex Parent-Child relationships
We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time.
I need to link these fields together by creating a ...
- 11
8
votes
1
answer
623
views
Schema-binding with recursive scalar UDF
TL;DR; Is it a bug that SQL Server allows a scalar UDF to recursively call itself when schema-bound, but only when altered to do so using the CREATE OR ALTER syntax? Or is it a bug that other syntaxes ...
- 14.4k
3
votes
2
answers
891
views
From last id, recursively find parent id and fill column
CREATE TABLE t1
(
COL1 nvarchar(36),
COL2 nvarchar(36),
ExpectedResult nvarchar(36)
)
INSERT INTO t1 (COL1, COL2, ExpectedResult)
VALUES
('CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-...
- 33
0
votes
0
answers
225
views
Make this recursive CTE more dynamic
I have this script that enables me to extract hierarchies and the description for each level however the number of levels has to be known beforehand to define the number of select queries necessary ...
- 1
0
votes
1
answer
281
views
How does SQL Server know not to execute Anchor element in Recursion CTE for 2nd Iteration / 1st Recursion?
I've been reading up on the inner workings of CTE Recursion and I'm still not quite sure how SQL Server is implementing the Anchor element in their source code and how SQL Server Query Execution ...
- 275
1
vote
1
answer
1k
views
How to find child and grandchildren tables of a main table by order
I have this script to return all child and grandchildren tables with their level from a database . How do I change this if I only want to return the child and grandchildren tables of a particular ...
- 67
0
votes
1
answer
388
views
Generate hierarchical structure in sql server
I have a table employees
id
name
manager
1
susan
null
2
john
1
3
mary
1
4
josh
3
5
mathew
4
6
leo
2
7
peter
5
I want an output like:
id
name
hierarchy
1
susan
null
2
john
susan->john
3
...
- 183
1
vote
1
answer
2k
views
Generate dates using recursive cte
I have a table sales
product_id period_start period_end sale
----------------------------------------
1 2019-01-25 2019-02-28 100
2 2018-12-01 2020-01-01 10
3 ...
- 183
2
votes
0
answers
180
views
Problem with sysdepends while checking dependencies
I have this script to list all dependencies of all views in a database . I get the dependencies in the right order when I use sysdepends ,but I am missing a few dependencies while using sysdepends. ...
- 99
0
votes
1
answer
43
views
Retreive upper heirarchy
I have Categories stored in a single table.
id name parentId path
A1 Cat 1 0 Cat 1
A2 Cat 2 A1 Cat 1 > Cat 2
A3 Cat 3 A2 Cat 1 > Cat 2 > Cat 3
...
- 103
1
vote
3
answers
226
views
Finding the Second (time) Interval within X of the current time
I have a need to find, for a given current second and upper and lower boundary, find all of the seconds that would fall within that, wrapping at the 59 second mark.
With that word salad out of the way,...
- 9,244
0
votes
0
answers
214
views
Mysql parent child to pivot table
I want to convert my parent child table to pivot,below is my query
CREATE TABLE parent_child
with recursive cte (id, name, parentid,level) as (
select id,
name,
...
0
votes
1
answer
1k
views
Filtering a recursive CTE as view
i'm using MSSQL Server 2017 and have a CTE used in a view to get the root element of a hirarchical tree linked to one specific element.
If the CTE is used directly and the base elements are allready ...
- 3
2
votes
1
answer
782
views
Why my not-null columns become nullable in my CTE recursive query?
I have a simple hierarchical table. Let's say it has these columns:
Id, [Guid], ParentId, Title
Only ParentId is nullable. Other columns are not nullable.
I have created a recursive query to show me ...
-1
votes
1
answer
92
views
How to execute join statement dynamically without using static group id? [closed]
I work on SQL server 2014 I need to run this statement below dynamically without
using group id static
because I don't know maximum group id using may be 10,15,20
so How to run query below dynamically ...
- 101
3
votes
3
answers
451
views
Why can't unused columns and tables be ignored in a recursive CTE
Usually, SQL Server can optimize away any unused columns in the execution plan, and unused joined tables are not queried. But as soon as a recursive CTE comes into play, apparently all columns and ...
- 133
0
votes
1
answer
36
views
Getting Indirect parents
I have following different tables
LeafChilds
ChildId, ChildName
1, Child1
2, Child2
Group
GroupId, GroupName
1, Group1
2, Group2
3, All
GroupChildMapping
GroupId, ChildId
1, 1
2, 1
2, 2
...
- 9
0
votes
1
answer
7k
views
Recursive CTE to get Grandparent, parent, children [duplicate]
I have a data structure that relies on a parent/child model. The top level parent can have multiple children where they can also be parents.
Here's an example of how things can look in the tree:
...
- 309
6
votes
1
answer
2k
views
String manipulation in Recursive CTE
I am trying to make something like the following work:
WITH results(n, string ) AS (
SELECT 1,'lol'
UNION ALL
SELECT n+1, string + ' lol'
FROM results
...
- 249
2
votes
1
answer
124
views
Org chart CTE with exact index but plan shows expensive sort node
I've got a moderately large table (148 million rows) that's got pretty common org chart attributes to it. Names are changed, but it's the same idea. I was thinking of adding a hierarchyid column to ...
- 255
0
votes
3
answers
843
views
Recursive CTE with partition
I have a table like this in MS SQL SERVER 2014:
ID|Race|Lap
1 |21 |11
2 |21 |NULL
3 |21 |NULL
4 |21 |NULL
5 |29 |65
6 |29 |NULL
7 |29 |NULL
8 |29 |NULL
I am trying to fill up ...
- 476
3
votes
1
answer
2k
views
Recursive CTE Multiple Levels [duplicate]
I have a table showing which roles recursively grant access to which resources within a database. For example:
The Default_Role grants access to the App_Role, the App_Role grants access to the ...
- 43
1
vote
0
answers
80
views
Copying a dependency structure
I'm looking for performing a copy of a "dependency structure". What I mean is: there is a tree structure representing a product and its sub components. Each of this products may have one or ...
- 11
5
votes
2
answers
281
views
CTE query that fails to find ancestors
I have a table foo
with the content
So the records of the table foo can graphically be represented with this graph.
When I run a procedure that should return ancestors using @starting_id=9
(...
- 664
0
votes
1
answer
198
views
A way to process a CTE once?
I have a view that has serious performance issues, because I am linking it to a CTE that uses another view, with the same table references.
WITH
splits(ID, [NEW TEU])
AS
(SELECT s.[DTL ID] AS ...
- 295
2
votes
1
answer
1k
views
Why is my recursive CTE so much slower on Azure SQL?
I have this simple recursive CTE for a hierarchy of folders and their paths:
WITH paths AS (
SELECT Id, ParentId, Name AS [Path] FROM Folders
WHERE ParentId IS NULL
UNION ALL
SELECT ...
- 213
10
votes
1
answer
3k
views
Simplifying Function with Recursive CTE and/or Window Function
I'm trying to come up with a Recursive CTE and/or Window Function to create a function.
After days, I've boiled the function down to (pseudocode)
where I have N and B, and need to generate E:
En =...
- 232
1
vote
1
answer
510
views
Recursive Parent-Child Location Relationship Cache as View
Here is my data structure:
CREATE TABLE Locations(
LocID int IDENTITY(1,1) NOT NULL,
LocationTypeID int NOT NULL,
ParentID int NULL,
LocNum varchar(50) NULL,
LocName varchar(250) ...
- 127
3
votes
1
answer
860
views
Get the total count of every child foreign keys
I have been trying this in SQL EXPRESS 2016. Here is my problem:
I have a tree like structure DB(Parent > child > grandchild >...) and in the lower level there is a relation with another table.
...
- 43
-1
votes
2
answers
1k
views
Start at condition and stop when criteria is met
i'm new to MS SQL and i have to carry out the following task.
What i want to output is the time of the first occurence of Stateid = 4, go through the list and return the time when i meet a either ...
- 33
0
votes
2
answers
214
views
all the dependants of a stored procedure or function recursively - but shown only once
I often work with third party data professionals and they ask me to replicate whatever table(s) such and such stored procedure or function or view touches within one or more databases.
so I have ...
- 16.6k
1
vote
0
answers
114
views
How to get all parents associated to a child, one row for each association [duplicate]
I have a table with two columns, Parent and Child.
i need to get an output list showing a child and all associated parents, one parent per row.
The youngest child will show as a child in rows equal ...
- 11
9
votes
2
answers
3k
views
Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?
I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
- 463
6
votes
1
answer
666
views
Tree Node Calculation - SQL Server
I'd appreciate some help with something I am trying to achieve please.
The parts that my company make are built from sub assemblies and sub assemblies of sub assemblies.
This looks like:
...
- 63
1
vote
3
answers
128
views
Count Items not In Time Range
I'm attempting to count the number of items if they are not within 30 seconds of the first item in a "group". I'm having a hard time figuring this out.
So, I have this table:
WITH ADates AS (
...
- 387
2
votes
1
answer
2k
views
CTE Query to CONCAT text (over 10,000 rows)
I am trying to construct a CTE query to loop through rows of data and concatenate text into one line for each SampleUserNumber.
Below is what I am working with:
CREATE TABLE #SampleTable
(
...
3
votes
2
answers
5k
views
SQL Server CTE Bottom to Top Recursive with Where clause
I have an Employee Table with an EmployeeId, ManagerId and a Name field.
The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).
I found ...
- 133
22
votes
4
answers
5k
views
How does SQL recursion actually work?
Coming to SQL from other programming languages, the structure of a recursive query looks rather odd. Walk through it step by step, and it seems to fall apart.
Consider the following simple example:
...
- 409
1
vote
1
answer
2k
views
Distribute multiple payments to invoice lines
I'm having a problem allocating payments to invoice lines.
We’re using MsSql 2014
Data looks like this:
Invoice lines table (sales):
lineId invoiceId value
1 1 100
2 ...
- 11
1
vote
3
answers
3k
views
SQL recursion and cte - dates timeseries
I need to build vector(s) of time series dates (say 10) for a subset of start dates.
The following statement returns 10 consecutive dates starting from '2010-01-01'.
;with cte as
(select 1 i union ...
- 11
0
votes
2
answers
176
views
Select starting node ID in CTE
I have following table:
create table osoba(
id int primary key,
idNd int,
nazwim nvarchar(500),
)
Data looks like below:
id idNd nazwim
1 NULL grandpa
2 1 child1
3 1 child2
4 2 ...
6
votes
1
answer
2k
views
Help with CTE Aggregating Children Recursively
I'm trying to create a query to calculate the "Available to Sell" qty of all items in inventory. In this case, an item can have current available inventory, but could also be a kit, which is comprised ...
- 525