Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 2 years ago. Improve this question Need to calculate consecutive hours. Here is the data This the client has a limit of the amount of consecutive hours allowed. Here is the
Tag: recursive-query
Recursive query in Oracle until a parent in the hierarchy meets a condition?
I have a table like the following: And I’d like to be able to fetch each ID with its corresponding VALUE_ID. I want to do it in such a way that if a row has a VALUE_ID as NULL, it “inherits” the VALUE_ID of the first parent above it in the hierarchy, that has a VALUE_ID as NOT NULL. So
Get direct hierarchy in SQL without siblings of searched id
I have a pretty simple table called Types which contians ID, ParentID and Name. What I need is to get the full hierarchy from an ID, but without including the siblings of the searched ID. I have written the following SQL, which gives me the full hierarchy of the table (with the topmost parent being id ‘246’): So far, so
Split multi-month records into individual months
I have data in a table in this format – where date range is multi-month: I want to create a view/ insert into a new table – the above record broken by month as shown below: Please advise. Answer Just another option using a CROSS APPLY and an ad-hoc tally table Example Returns
CTE Recursive Query to Grandparents
I have following table: What I need to get is SELECT query that will return only rows that have ISDELETED 0 and ISINEDIT 0 and whose parents or grandparents are 0 as well I currently have: But for some reason it returns double rows Answer You need to add the same isdeleted = 0 AND isinedit = 0 predicate to
How to list out management levels from Postgres table
I have a table with the following fields: Id, manager_id, and candidate_name The manager_id will point at id which allows me to reference the management chain. I want to generate an output like the …
RECURSIVE QUERY – PARENT/CHILD
Say I have a table like so: id Parentid childid ——————————– 01 null 02 02 01 03 03 02 04 …
Select all the years from a range without having a years table
I have selected the current year with I want to print a list of all the years from 1990 to the current year without having to create a table of the list of years, is there a way to do it or I must create a table to do this? Answer or
How to get the root record in Oracle database
Starting from the generic node (or leaf) how to get the root node? How to get the root msg_id? Answer You were on the right path, but you are missing two essential ingredients. First, to indicate the starting point, you need to use the start with clause. Obviously, it will be something like start with id = <input value>. You
Is branch pruning possible for recursive cte query
This is inspired by question Retrieve a list of lists in one SQL statement – I have come up with a solution, but I have doubts on its efficiency. To restate the problem: we have 2 Tables: Person and …