I have two tables: MainTable and MyTable. MyTable has unique ControlNo and ID. I need to add very first EffDate from MainTable to MyTablebased on ID and ControlNo. For that I need to look at …
Tag: recursive-query
How to capture first row in a grouping and subsequent rows that are each a minimum of 15 days apart?
Assume a given insurance will only pay for the same patient visiting the same doctor once in 15 days. If the patient comes once, twice, or twenty times within those 15 days to the doctor, the doctor …
Postgresql: more than one row returned by a subquery used as an expression?
I have a query to find the youngest generation in a family tree. This is my database ID NAME PARENT_ID 1 A 0 2 B 1 3 C 1 4 D 2 5 E 3 6 F 3 7 G …
Expanding Date Ranges
I want to expand the date ranges in the below “Original Table”. The end result should be the “Resultant Table”. I know that this can be done in sql using dateadd and ctes. However, …
Postgresql Serial Daily Count of Records
I am trying to get a total count of records from 1st Jan till date, without skipping dates and returning 0 for dates that have no records. I have tried the following: orders is an example table and …
Using global list in recursive SQL query to avoid visted nodes
I have a self-referential table User: Note that there are circular references. I want to get all the followers, and followers of the followers, and so on of a user such that all the followers are presented in a flattened list, with their respective depths For Adam: Problem I want to avoid the rows 3 and 4, which represents two
How to convert data into this form in SQL :
Input: I have to Convert data from input to output. where we trying if for id freq is n then create n rows of that id. Output: Answer In Presto, one option uses sequence() and a lateral join to generate the rows:
How to determine the number of days in a month for a given Date Range?
I need to calculate using SQL Query, how many days within a given range fall into each calendar month. I have given 2 dates, which define a date range; for example 2020-01-01 to 2020-08-03. I need to find how many days in that range fall in to each month i.e. how many fall into July, and how many into August.
MySQL – Recursively list all parents and ancestors of all items in table
I have a table with a parent/child hierarchy that supports multiple (theoretically infinite) levels of nesting: I am trying to build a query that produces a concatenated list of every item’s parent items up until the highest parent in the tree: Based on a number of other answers here I have constructed the following MySQL query: See Fiddle here: http://sqlfiddle.com/#!9/48d276f/902/0
Create recursive query in Snowflake with a Left Join condition?
I am trying to create a recursive query that relies on a LEFT JOIN condition, but I am not sure if it is possible, especially in Snowflake. I have three tables: ITEM, ITEMHIERARCHY, and ITEMVALUE My goal is to return a list of all ITEMs with values and sub-item values rolled-up: Note that even though Item6 is a roll-up from