Category_ID Number
Parent_ID Number <----Category_ID reports to this colum
Category_Name Varchar....
MY QUERY <---I replaced the query above with my data
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy
My OUTPUT============
All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.
The above table shows a parent child relationship with the hierarchy shown in column level. for each parent (IDs 1, 9, 11), I want the first child level where the column IsAutoCalculate = 0
so for parent Id 1, the rows to be returned is of level 1 as that is the first child row of this parent with IsAutoCalculate = 0. The rows with level 2 should not be returned
For parent id 3, the rows to be returned will be with level 2 as this is the first child row of this parent with IsAutoCalculate = 0
Dear fellows, Can anybody tell me how can i apply recusive/Tree query using select statement. For example I've a table structure for an Organization as follows:
emp_id emp_name supervisor_id ---------- --------------- ------------------- 101 ZAFIAN 102 BRUNNER 101 108 CALLAHAN 102 105 RUSSO 102 110 SIM 102 103 DUELL 101 and so on
1. How can I get the above records in Hirarchical format starting from top or from anywhere else in the hierarchy?
In Oracle it can be done as follows: SELECT emp_id,emp_name,supervisor_id FROM employee_tbl CONNECT BY supervisor_id = PRIOR emp_id START WITH supervisor_id is null;
Please reply me at the following address if possible: faisal@visualsoft-inc.com
I apologize if I posted in the wrong section, but I cannot find a solution to this. I was hoping that some one can help me figure this out. I need this solution in a stored procedure VS using dataset because there are thousands of categories and it is extremely slow with a dataset. I have a category table ID intPARENTID intNAME SAMPLE DATA:
ID NAME PARENT_ID
1 ANIMALS 0
2 DOGS 1
3 CATS 1
4 Abyssinian 3
5 Persian 3
6 Rurkish Van 3
7 Dalmation 2
8 German Shepherd 2
9 Irish Setter 2
10 Bulldog 2
I need the stored proc to return results in a single record set like this: ANIMALS - DOGS - - Dalmation - - German Shepherd - - Irish Setter - - Bulldog - CATS - - Abyssinian - - Persian - - Turkish Van Seems fairly easy but I have been battling with this for days now. Thank you in advance, EL
I have searched everywhere for this and I can't find any information on it. When I use the Query Builder, in the top pane, the Filter pane, there are five columnsimension, Hierarchy, Operator, Filter Expession and Parameters.
In the dropdown for Operator, one of the choices is MDX. I cannot find any documentation on how to use this operator. I want to limit my filter to SELF_AND_AFTER, and I am hoping that this is possible using this operator. I know how to hand code it, but I have a ton of other tweaks to make to the query, so I want to use the Query Designer as much as I can.
Does anyone have any links to documentation on how to use this operator?
how recursive CTE works...Synthesise table with non-recursive CTE
;WITH Employee (ID, Name, MgrID) AS ( SELECT 1, 'Keith', NULL UNION ALL SELECT 2, 'Josh', 1 UNION ALL SELECT 3, 'Robin', 1 UNION ALL SELECT 4, 'Raja', 2 UNION ALL
[code]....
the first part query will return one row IDNameMgrIDnLevelFamily 1KeithNULL11
then this result set using after union all query .how the level + 1 condition is working ? and how its return values .
Code Snippet WITH OrganisationTree(OrganisationID, Organisation, ParentOrganisationID, [Level]) AS ( SELECT OrganisationID, Organisation, ParentOrganisationID, [Level] FROM Organisations WHERE OrganisationID = 1 UNION ALL SELECT o.OrganisationID, o.Organisation, o.ParentOrganisationID, o.[Level] FROM Organisations o INNER JOIN OrganisationTree t ON o.ParentOrganisationID = t.OrganisationID ) SELECT * FROM OrganisationTree;
I would like to have this stored in a view and to be able to apply the filter 'OrganisationID = 1' from outside the view so that I can reuse the code to fetch different organisation trees for use in more complex queries. Is this possible and if so how would I do it?
In the Microsoft SQL Server Management Studio, could anyone tell me how to enable the 'tree view' for the Summary window? It currently behaves like a file folder where I can move up and down 'folders' but can't see the tree of where I'm at.
We need to present hierarchical data on a web page, the same way thetree view shows files in Windows Explorer. Here's the catch: thattree view needs to be bound to a SQL Server database. How can this bedone?
I have a parent-child table, and i want to copy subtrees of it, so for instance this would be the starting point: (id, parentId, label) 0, null, World 1, 0, US 2, 1, NY 3, 0, UK 4, 3, London
now i want to copy object 3 (UK) and it's children, so i would get 0, null, World 1, 0, US 2, 1, NY 3, 0, UK 4, 3, London 5, 0, UK_copy 6, 5, London_copy
I have this sproc:
Code Snippet
alter proc CopyObject
(@ObjectId int,
@NewParentId int)
as
declare @NewId int,
@NewName varchar
select @NewId = max(Id) + 1 from Object
select @NewName = [Name] + 'copy' from [Object] where Id = @ObjectId
-- copy object
INSERT INTO [Object]
([Id]
,[Name]
,[ParentId]
select @NewId,
@NewName,
@NewParentId
from [Object]
where Id = @ObjectId
-- copy children and set their parent to the newly created object
declare c cursor fast_forward for
select Id
from [Object]
where ParentId = @ObjectId
declare @ChildId int
open c
fetch next from c into @ChildId
while @@fetch_status = 0
begin
exec CopyObject
@ObjectID = @ChildId,
@NewParentId = @NewId
fetch next from c into @ChildId
end
close c
deallocate c
But htis throws an error that the cursor already exists:
Msg 16915, Level 16, State 1, Procedure CopyObject, Line 66
A cursor with the name 'c' already exists.
Msg 16905, Level 16, State 1, Procedure CopyObject, Line 72
The cursor is already open.
I've tried to think of an approach without cursors, but i can't figure it out. Because on the first pass, the new parentId will be the same as the parentId of the object to be copied. But the copies of the children of this first original object should have the parentid set to id of the copied object, and so all the way down the tree.
Hai Iam new to SSRS 2005, please help me regarding below Drill Through Report Problem. Assum my problem with below example.Iam tried Hard iam not able to find the solution, Any body please help me.my real time problem is same as below functionality
If Suppose iam dispalying two columns like Country,Department,iam taking these two columns in a table, assume country column having America,south africa, individual america column having Florida state, south africa column contains capetown state, in the preview of the report iam applying drilldown to America column like + America, i need when clicking the America column i want to display Florida state under the column of Country like tree view structure
i need output like this and the same time i want to display other columns those also contain tree view structure assume other one is department column, with contains computers and sales, individualy computers contains HP, Sales contains Bikes
Country Department
+ America + Computers
---Florida -- HP
+ SothAfrica + Sales
---Capetown __Bikes
How to implement above output using drill through functionality, i tried with subreports and used all grouping formats, is possible for display output like above explain the procedure .
has anyone out there found a way to get the best of both recursive hierarchy and drill down in the same report, ie without needing to know how many levels there are in your hierarchy, still being able to report them like a tree view with collapse and expand capability at each level?
I building some reports with SSRS, and showing data in a table. The table has one Details row that forms at the end multiple rows with the tree view (+ and -).
Is there a way to open the tree view upwards and not downwards so that the children of level 2, 3 etc come over the parent row?
I've goggled it everywhere, but I don't seem to find the answer. If anyone could help me out, I would appreciated.
I added the row group to show up as the tree view in the left side of the report. But I need it to display the records in the report detail for just that group value for the records when I clicked the group value from the tree view list.
Currently, it‘s not doing that. It takes me to that page where the group value is listed but it lists all other records too (which I don't want).
I got assignment, how to make it appear in the right order .
/* DROP TABLE EMP SELECT * INTO Emp FROM ( SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b */
I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
Article(articleID,CategoryID,ArticleTitle) Categories(categoryID,ParentID,CategoryTitle) I am trying to retrieve the main category ID for a specific article ID. For example lets say I have this data: Article:
In this example I want to know who is the main category of article 3. The query should return the answer: 1 Thats because:
The article ID 3 is inside category 11. Parent for category 11 is 10. Parent for category 10 is 2. Parent for category 2 is 1 and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category. Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables) Do you have any suggestions for such query? Thanks all.
Recursive quey to show products with "custom defines fields" related by Classifications, instead of per product Hello, I’m working on a project .. .
I’m desperating due to the complex (for me and also I think for some others) sql query that I need to write, to show the products with his “custom defined fields� that are inside a ProductsFieldsByClassification table that holds this mentioned “custom defined fieds� according to the Classifications table, on where the Products can be found trought the productsClassifications table.
CustomFields can be defined and set for the products, trought his Classifications (instead of define a custom field for each product (that consume a lot of data), I decide to use it as I explain)
I will to know the properly SQL QUERY to show a list of products with the ProductsFieldsByClassifications and ProductsFieldsValuesByClassifications:
As example on a Requested ID_Classification = 16 (Torents/Games/Dreamcast/PAL), the products must be show with the ProductsFields and Values that has the DBA for the:
· requested ID_Classification
o PAL (ID_Classification: 16)
· AND all the Classifications that belongs above (trought ID_ParentClassification) that are :
o Torrents (ID_Classification: 1) that will show the products values for the “Size�
o Games (ID_Class..:4) ß this classification has no CustomFields so none from this one.
o Dreamcast (ID_Class..:14 ) that will show his ID_Classification(14) product field “Levels� value (but not “AllowSave� as not have value for any product)
Hmnn i show a graphic that i design for (feel to click over to see at correct resolution)
I also write asp.net tutorials. For those interested see my blog at http://abmartin.wordpress.com
Does anyone know how to do an sql recursion queries?
I believe it involves a view with a union.
I have a User Table and in that table i have a employee_id and a boss_id. What i'd like to do is to find all employees under a certain boss. For example,
Employee_ID Boss_ID 1 2 1 3 4 3 5 2
So if i'd like to know who are under the employee_id = 1 it will return employee_id 2 and 5 since employee 2 also is the boss of employee_id = 5.
I am wondering if there is some type of recursive query to return the values I want from the following database.
Here is the setup:
The client builds reptile cages.
Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.
The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.
PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.
Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb
Can you please help me to write a recursive query in sql server 2000?
I have got a table WORKORDER. wonum and parent are the two columns in the table. A wonum can have children.Those children can have children, so on. if i am giving a wonum,it should display all the children ,their children and so on.
Went looking for an answer but not really sure what phrases to lookfor. Just decided to post my question.I have a collection of groups which contain items. I also have acollection of users which can be assigned permissions to both groupsand individual items. If a user has permission to a group then the userhas that permission to each of the items in the group. I need a querywhich will return all the items and permission for a particular user.Here is the code for creating the tables and populating them.CREATE TABLE [Account] ([Name] VARCHAR(10))INSERT INTO [Account] VALUES ('210')INSERT INTO [Account] VALUES ('928')INSERT INTO [Account] VALUES ('ABC')CREATE TABLE [AccountGroup] ([Name] VARCHAR(10))INSERT INTO [AccountGroup] VALUES ('Group1')INSERT INTO [AccountGroup] VALUES ('Group2')CREATE TABLE [AccountGroupMembership] ([GroupName] VARCHAR(10), [AccountName] VARCHAR(10))INSERT INTO [AccountGroupMembership] VALUES ('Group1', '210')INSERT INTO [AccountGroupMembership] VALUES ('Group1', 'ABC')INSERT INTO [AccountGroupMembership] VALUES ('Group2', '928')INSERT INTO [AccountGroupMembership] VALUES ('Group2', 'ABC')CREATE TABLE [Permission] ([User] VARCHAR(10), [Item] VARCHAR(10), [ItemType] VARCHAR(1)-- 'A' for account, 'G' for account group, [ReadPerm] INT, [WritePerm] INT)INSERT INTO [Permission] VALUES ('john', '210', 'A', 1, 0)-- readaccess to 210 accountINSERT INTO [Permission] VALUES ('john', 'Group1', 'G', 1, 1)--read/write access to Group1 groupINSERT INTO [Permission] VALUES ('mary', '928', 'A', 0, 1)-- writeaccess to 928 accountThe simple querySELECT * FROM [Permission] WHERE [User] = 'john'returnsUser Item ItemType ReadPerm WritePerm---------- ---------- -------- ----------- -----------john 210 A 1 0john Group1 G 1 1but what I really want is (notice that Group1 has been replaced withthe two members of Group1)User Item ReadPerm WritePerm---------- ---------- ----------- -----------john 210 1 0john 210 1 1john ABC 1 1(Forget for the moment that 210 is listed twice with differentpermissions. I could take the result and do some sort of union to least(or most) restrictive permissions.)
Hi there,Need a little help with a certain query that's causing a lot of acidin my stomach...Have a table that stores sales measures for a given client. The salesmeasures are stored per year and there could be multiple salesmeasures every year per client. There is another field called lastupdate date. If there are multiple sales measures then need to selectthe one that's been entered last based on this field. Also, if thereis no sales measure data for current year then I need to return thelast year's data for which sales measure has been entered. Forexample: if client #1 has sales measure value of $200 for 1999 andnothing since, then I need to return $200 for any year following 1999.So the query would look something like this:SELECT client_name, sm_dollars FROM <tables>Based on the DDL at the bottom I would expect to get back: c1, 100;c2, 200The way I am doing it now is with correlated subqueries (3 to beexact) that each do an aggregate and join back to the original table.It works, but it is notoriously slow. SQL Server is scanning theindex and does a merge join which in a large query takes %95 of thetime. Here is the part of the query plan for it:| | | | | | |--MergeJoin(Inner Join, MANY-TO-MANYMERGE:([sales_measure].[client_id])=([sales_measure].[client_id]),RESIDUAL:(([sales_measure].[client_id]=[sales_measure].[client_id]AND [sales_measure].[tax_year]=[sales_measure].[tax_year]) AND[Expr1013]=[sales_measure].[last_update_date]))| | | | | | |--StreamAggregate(GROUP BY:([sales_measure].[client_id],[sales_measure].[tax_year])DEFINE:([Expr1013]=MAX([sales_measure].[last_update_date])))| | | | | | | |--MergeJoin(Inner Join, MERGE:([sales_measure].[client_id],[Expr1010])=([sales_measure].[client_id], [sales_measure].[tax_year]),RESIDUAL:([sales_measure].[client_id]=[sales_measure].[client_id] AND[sales_measure].[tax_year]=[Expr1010]))| | | | | | ||--Stream Aggregate(GROUP BY:([sales_measure].[client_id])DEFINE:([Expr1010]=MAX([sales_measure].[tax_year])))| | | | | | | ||--Index Scan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)| | | | | | ||--Index Scan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)| | | | | | |--IndexScan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)There are two indexes on sales measure table:sales_measure_pk - sales_measure_id (primary key) clusteredsales_measure_idx1 - client_id, tax_year, last_update_date, sm_dollarssales_measure table has 800,000 rows in it.Here is the rest of the DDL:IF OBJECT_ID('dbo.client') IS NOT NULLDROP TABLE dbo.clientGOcreate table dbo.client (client_idintidentityprimary key,client_namevarchar(100)NOT NULL)GOIF OBJECT_ID('dbo.sales_measure') IS NOT NULLDROP TABLE dbo.sales_measureGOcreate table dbo.sales_measure(sales_measure_idintidentityprimary key,client_idintNOT NULL,tax_yearsmallintNOT NULL,sm_dollarsmoneyNOT NULL,last_update_datedatetimeNOT NULL)GOCREATE INDEX sales_measure_idx1 ON sales_measure (client_id, tax_year,last_update_date, sm_dollars)GOINSERT dbo.client(client_name)SELECT'c1' UNION SELECT 'c2' UNION SELECT 'c3'GOINSERTdbo.sales_measure(client_id, tax_year, sm_dollars,last_update_date)SELECT1, 2004, 100, '1/4/2004'UNIONSELECT2, 2003, 100, '1/3/2004'UNIONSELECT 2, 2004, 150, '1/4/2004'UNIONSELECT2, 2004, 200, '1/5/2004'The view that I use to calculate sales measures:CREATE VIEW sales_measure_vw ASSELECTsm.*FROM sales_measure smINNER JOIN (SELECT sm2.client_id, sm2.tax_year,MAX(sm2.last_update_date) as last_update_dateFROM sales_measure sm2INNER JOIN (SELECT sm4.client_id, MAX(sm4.tax_year)as tax_yearFROM sales_measure sm4 GROUP BYsm4.client_id) sm3on sm3.client_id = sm2.client_idand sm3.tax_year = sm2.tax_yearGROUP BY sm2.client_id, sm2.tax_year ) sm1ON sm.client_id = sm1.client_id ANDsm.tax_year = sm1.tax_year ANDsm.last_update_date = sm1.last_update_dateAny advice on how to tame this would be appreciated. Also, any adviceon the indexes would help as well.ThanksBob
I'm trying to get from the first table bellow to the second one using recursive queries. Could you help me on this issue? I want to be able to group by project and employee id and then if the dates overlap then to get the min and max dates in one row
I've been struggling with this for while. It doesn't seem that hard, but I can't seem get the results right. I've tried several SELECT queries and also played with CTE but I'm quite a novice in that dept. I have searched this forum and read many similar posts but I can't seem to get it quite right.
Suppose I have a table (Categories) with parent categories and sub categories with the following example data:
Can someone suggest the most efficient way to get an appropriate result set that I could use to display each Parent Category followed by each appropriate Sub Category on a web page?
I have a set of data and I need to actually add a column to the result set based on information contained within the query. For example,
SalesmanID SalesBudget SalesRegion Product Amount 1 1000 USA Soap 100.50 1 1000 USA Milk 50.50 5 2000 OZ Bread 2.50 1 1000 USA Bread 2.50 3 350 OZ Nappies 4.50
Notice that Salesbudget is related to the salesmanid and doesn't change. I need to be able to add a column to this query that sum the total budget for the saleregion so output looks like this
SalesmanID SalesBudget SalesRegion Product Amount RegionBudget 1 1000 USA Soap 100.50 3500 1 1000 USA Milk 50.50 3500 5 2000 OZ Bread 2.50 2350 2 1500 USA Bread 2.50 3500 3 350 OZ Nappies 4.50 2350
So I need to do a sum distinct on salesbudget by region and append to my query as a column, is this possible? If so how?
It is important that I do it this way as I will have parameters in my query that might restrict which lines are returned. For instance show all bread sales and that way I only what to sum region budgets for these.
I could do it with 2 queries but needs to be 1 as I am using a matrix report that does'nt contain a sum distinct function and is contrained by scope.
i have two tables, one called P_TAXONOMY with these columns:
IDObject TaxID DB1 259
and one hierarchical table called (TAXONOMY) with this columns:
TaxID TaxIDParent tax_name taxlevel 259 256 P.O 3
256 4 A 2
4 1 B 1
1 0 C 0
i get the TaxID from P_TAXONOMY and i have to look for recursively its father (level 0) in the TAXONOMY table; I'm pretty sure the solution to this involves some type of recursive query but if anyone here can help me out I'd really appreciate it.
i have the next query but only for 3 levels, i need it for n levels (from any leaf)
select stf.tax_name from TAXONOMY stf, (select st0.TaxIDParent from TAXONOMY st0, (select st.TaxIDParent from P_TAXONOMY spt, TAXONOMY st where spt.TaxID = st.TaxID ) as u where st0.TaxID = u.TaxIDParent ) as cons where stf.TaxID = cons.TaxIDParent group by tax_name
Thanx Diego Bayona.
pd: for example: the root of TaxID = 259 (P_TAXONOMY) is 1 (TAXONOMY)
Hello. I have a question. I have a table that keeps track of claims being made by clients (like insurance claims). There is a table which has 2 fields. Field1 and Field2. Field1 has the original claim number. If an adjustment is made to the claim, a new claim number will be generated. For example if a person pays $100 and files a claim, he is issued a claim number which goes into Field1. If that person spends some more money towards the same issue and then files another claim then he is given a new claim number which is stored in Field2.
Field1--------------Field2 -------------------------------- 100--------------------150 150--------------------200 200--------------------250 250--------------------Null (No more adjustments made) and so on.
So in the query I will supply a claim number (say 100). I need to get the history of all the claims attached to it. In this example 100, 150, 200, 250.
Also if the claim number provided is 200 I need to get the original claim number (which is 100) meaning I need to back track and forward track too. I need this in SQL Server 2000.
