Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Counting Items In A Hierarchical Category And Subcategories

I have two tables, one is a list of categories, the other a list of items listed in the categories. The category table is self-referencing through a ParentID column. Top-level categories have ParentID = 0.

ID int
ParentID int
CatName varchar(30)


There is a third table that links to items through ItemID, but this is not important for this problem!

What I want to do is create a stored SQL procedure. This procedure simply pulls rows from the category table and counts the number of items that are in it. This is straighforward enough using COUNT as a "virtual column", but the difficulty is counting the items in the category but also the items in any subcategories.

An end result with two top-level categories and three subcategories in each might look like:

- Category 1 (20)
---- Subcategory 1.1 (10)
---- Subcategory 1.2 (5)
---- Subcategory 1.3 (5)
- Category 2 (14)
---- Subcategory 2.1 (3)
---- Subcategory 2.2 (4)
---- Subcategory 2.3 (7)

Hence the difficult bit is getting the total number in brackets for each category with subcategories.

What I have at the moment is:

SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE Items.CategoryID = Categories.ID) AS ItemTotal
FROM Categories

What I would like is something along the lines of (pseudocode):

SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE <Item is in category or subcategory>) AS ItemTotal
FROM Categories

I don't particularly want to use temporary tables and I definitely do not want to do any of this retrieval in my application - it needs to come straight from the database.


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Counting Only Countries That Can Sell A Product Category?
In my data, I'm looking to find how many countries purchased a specific category, and how many countries total can sell the category in question.
(only certain countries sell each category)

This will probably be represented with a many to many relationship between country and category (in order to show the restriction between category and country).

How would one write a calculation to determine how many countries purchased a specific category, and how many countries total can sell the category in question?

View Replies !   View Related
Counting Items

I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:

SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1

This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?


View Replies !   View Related
Counting Items In Categories
Ive got this monster which will give me a parent categoryName and the number of records linked to a child of that category, I want to use it for a directory where the list of categories has the number of records in brackets next to them. Note: a A listing will show up in each category count it is associated with


Accommodation (10)
Real Estate(30)

Select trade_category.iCategory_Name,Listing_category.iPa rentID,count(Listing_category.iCategoryID) as num
from Listing_category,trade_category Where Listing_category.iParentID = trade_category.iCategoryID Group by
Listing_category.iParentID,trade_category.iCategor y_Name
Union ALL
Select Freecategory.sName,Listing_category.iParentID,coun t(Listing_category.iCategoryID) as num
from Listing_category,Freecategory Where Listing_category.iParentID = Freecategory.iFreeID Group by

Which Produces

Real Estate12401 12
Extreme Sports3 4

I would Like to get the same query to produce a list of all the empty records too.
ID Count
Accommodation 6112 0
Real Estate 12401 12
retail 12402 0
Extreme Sports3 4
Cycling 5 0

View Replies !   View Related
Counting Items Per Hour
I am trying to get a count of how many times a badge is entered in each hour it appears. In other words, I would like to find out how many times badge 3333 comes up in each hour of the day, the same with badge 4532.

The task at hand is to find out productivity for items processed per hour per badge number. I know I will need to take the hour out of timedate using datepart, but the results do not look right. Would anyone have any ideas on this? Thanks!

the table I have looks like this:

6112133333781267941/10/05 9:19
6112084532781275591/10/05 9:21
6111973333781265611/10/05 9:29
6111903333781261671/10/05 9:30
6111774532781253021/10/05 9:38
6111693333781257811/10/05 9:40

View Replies !   View Related
Counting Items And Returning Values
I have a table of product orders. It contains a row for "platform" and Ineed to return how many times each platform is listed in the DBExample data for platform could be:XBOXXBOXXBOXPLAYSTATIONPLAYSTATIONGAMECUBEPLAYSTATIONI'd like the data to be returned asXBOX - 3PLAYSTATION - 3GAMECUBE - 1How would I go about doing this please?

View Replies !   View Related
How Do I Do Categories And Subcategories And More Subcategories?
Hi, I have a database that will have various categories:

take this:

Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas

(it's shoes)

I have to put that in a structure that makes sense. I don't think having several tables for categories, subcategories, subsubcategories.... is the right way to do it. I'm not a design expert, so I was hoping someone might help me. How can I organize this? Any book, link, or advice is appreciated. Thank you.

View Replies !   View Related
Hierarchical Table Functions Vs Hierarchical CTE
Recently I was in need of a hierarchical tree data. I learned about CTE and how they can be used to build hierarchical data with simple syntax. I used CTE and was through with the task. Later during free time, I tried to compare CTE approach with the traditional SQL 2K Table Function approach. It was surprising to see the query costs when I ran both the modes at one go...

Query Cost (relative to batch) : 0.49%
Query Text : Select* From fn_GetTree(8);

Query Cost (relative to batch) : 99.51%
Query Text : with treedata (id, parentid, status, prevStatus, lvl) as (select ...)

What does that indicate? Does it mean that the Table Function approach is much faster than CTE? I am sure that I was not making unwanted Joinsin the CTE mode.

Can someone explain why that huge difference is there? And what the scenarios where CTE is better over Table Functions?

View Replies !   View Related
Category/Parent Category Design And Querying

I have a simple table:


I want to associate my Products to a category so that I can search by category....ok.  If I just have a simlpe table:


I can link a product to a category.  Now, if I just link a product to a single category, such as the bottom leaf category:

Self Help / Personal Development / Spiritual / Meditation

I would link a product to the Meditation category.  However if I
click on Self Help while browsing, I want to see all items underneath
Personal Development, Spiritual and Meditiation.  So my question
is is this a good way to store the product-category relationships, or
should I put many entries into CategoryProducts to keep the queries
simlpe and faster? Are they faster doing it this way? In this way there
would be 4 entries for a product in meditation.  My personal idea
is that adding all entries up a tree arm of a category path will be
cumbersome to manage, but it does solve the problem of clicking on Self
Help and seeing all products that exist within sub-categories.  I
am sure an SQL query would be able to work this out, but I dont know if
performance would be something to consider on an ecommerce site? Are
there any patterns fo rthis stuff - seems a reasonably repeatable
pattern for business sites?



View Replies !   View Related
How To Create Categories Tree With Subcategories ?
Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:
--With country
--With world
How i write in Select Command ?
SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)

View Replies !   View Related
How To Create Categories Tree With Subcategories ?
Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:
--With country
--With world
How i write in Select Command ?
SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)

View Replies !   View Related
DELETE Items Where Count(items) &>1
I cannot find an easy way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)

7fdfd 0

I want to DELETE each Row IN

SELECT doublons.serial, Count(doublons.serial) AS 2Times
FROM doublons
GROUP BY doublons.serial
HAVING Count(doublons.serial)>1

and WHERE isOK = 0

in my exemple , after deleting, my table must look like


thank you for helping

View Replies !   View Related
Looping Through Stored Procedure Inside Another Stored Procedure And Displaying The Category And Then Displaying 1 Item In Each Category
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those.  Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID   Category1      Saints2      Falcons3      Bucaneers4      Chargers5      FalconsPlayer Table:ID    CategoryID   Player                 News                                Player Last Updated1            1           Reggie Bush       Poetry in motion                                9/21/20062            1           Drew Brees         What shoulder injury?                        9/18/20063            5           Michael Vick       Break a leg, seriously.                       9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this.  I just don't know how to loop through and display it on a page.  Right now I have two datareaders in the code behind but ideally something like this, I would think the code  would go on the page itself, around the html.

View Replies !   View Related
Summing Invoice Items - The Multi-part Identifier &"items.TAX&" Could Not Be Bound
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record.  My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam  ALTER PROCEDURE [dbo].[UpdateInvoiceSummary]    @Invoice_ID intAS    DECLARE @Amount intBEGIN    SELECT     Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX    FROM         InvoiceItems AS items    GROUP BY Invoice_ID    HAVING      (Invoice_ID = @Invoice_ID)    Update Invoices SET Amount = items.Amount    WHERE Invoice_ID =@Invoice_IDEND

View Replies !   View Related
Hierarchical Design
I am designing database that will store clinic and doctor information.

1) A clinic can have doctors and staff members.
2) A clinic can belong to another clinic.
3) A doctor can practice on his/her own practice/clinic and still belong to another clinic.

I will email my current design if needed.


Stephen Cantoria

View Replies !   View Related
Hierarchical XML Import?

Can anyone point me at a tutorial or sample that shows how to use IS for importing an xml file containing hierarchically arranged records ?

I have a file which contains multiple orders , the orders contain multiple line items.. the file also contains an element with details of the file source etc...

So, I want to make an insert in the FileLog table an then make inserts into the orders table .. then make inserts into the OrderItems table which will have the foreign key from the orders table in the records...

if you get what I mean...

But I have searched hign and low and can't see any info on how to load anything but a very flat xml file structure...



View Replies !   View Related
Hierarchical Cumulative Sum
I have a table consisting of 3 columns: Parent varchar(50), Child varchar(50), Pop int.

The table is setup as follows:

Parent Child Pop
Europe France 0
France Paris 1
New York New York City 10
North America United States 0
North America Canada 0
United States New York 0
United States Washington 0
Washington Redmond 200
Washington Seattle 100
World Europe 0
World North America 0

This is just some sample data modified a tiny bit from an example of a hierachical print out sample that is a stored procedure that allows me to pass any place and see all of that place's children/grandchildren.

I need to figure out how to write a query to show me cumulative sums (ROLLUP?) of the whole tree. So the output should basically be something like this (it can include parent and child columns too):

World Null 311
World Europe 1
Europe France 1
France Paris 1
World North America 310
North America United States 310
North America Canada 0
United States New York 10
United States Washington 300
New York New York City 10
Washington Redmond 200
Washington Seattle 100

Hopefully you understand what i'm looking for. I've tried using WITH ROLLUP and I also tried using an Inner Join but I'm not really sure what I need to do to pull this off. I seem to only be able to get it to work 1-2 levels deep but not through the whole tree.

Any help/ideas would be appreciated! Thank you.

View Replies !   View Related
Hierarchical Recursive SUM Pb

I use Hierarchical Recursive to display website tree in my report.

The website has an unknown depth this is why I use this method.

My report Layout looks like this in BIDS : click here

The following piece of code displays hits by web page :

Code Block

The next one should displays hits by parent's folder :

Code Block

But it doesn't ...

It gives me the same value as for the web page hits.

What I am trying to achieve is to consolidate data by upper level. Something like this:


Any help will be very appreciated



View Replies !   View Related
Fabricated Hierarchical Recordset
I want to use fabricated hierarchical recordset in VB6 using ADO. I wrotecode likedim rs as adodb.recordestset rs=new adodb.recordestrs.fields.append "a1",adChar,30Then in loop I putrs.addnewrs("a1") I associated this with hierarchical flexgrid I saw what I expected. Onthe next step I added liners.fields.append "a2",adChapterand this operator gave me error that I use wrong parms. Then I realized thatI should use specific connection. But with this connection adChar stopped towork also. Is it possible to resolve this problem?--Aleks Kleyn

View Replies !   View Related
Hierarchical Table (count)
Hellofor MS SQL 2000 i am having :CREATE TABLE [dbo].[Items]([id_Items] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,[id_ItemsSup] [int] NULL,[Name] [nvarchar] (100) NOT NULL,[SubItems][int] DEFAULT (0)) ON [PRIMARY]with : UPDATE [Items] SET SubItems = (SELECT COUNT(id_Items) AS ct FROM dbo.Items WHERE id_ItemsSup = 1) WHERE id_Items = 1I get how many subItems has Item = 1how can I update the Column SubItems (for each row) ?to get the total of subItems for each Item ?thank you

View Replies !   View Related
Performing A Hierarchical Query...
Hi. I'm trying to find out which "cases" have a new items added to our database. I have provided a sample layout.

ID ParentID Name CreateDate
358 2 SMITH, JOHN 3/3/2008 11:15:23 am
359 358 Invoice 3/5/2008 4:13:52 pm
360 358 Shipping 3/5/2008 5:11:09 pm
361 358 Receiving 3/6/2008 4:22:01 am

The main ID for this is 358. The invoice, shipping, and receiving items are child items. I would like to run a query that can report which cases have newly added items. This is hierarchical I guess and I'm quite lost. I hope this makes sense. Thanks for any help!

View Replies !   View Related
Creating Hierarchical Tables
I need some help creating Hierarchical Tables. The scenario will be like this, I will store different keys as parents with children. e.g. "Status" should return "Active","Inactive","Obsolete", "Title" should return "Mr", "Mrs", "Ms","Prof","Dr" etc. The list is currently growing and need a solution. All help will be appreciated.

View Replies !   View Related
Flatten Hierarchical Data

I have a problem that I am hoping somebody can help me with!

I have built a hierarchy using the adjacency list model so I have records with an id that maps to the parent record so my hierarchy looks something like this:-



Daily Express

Express Publications
Express Supplements
Daily Mail

Mail Publications
Mail Supplements

So my table would look like below:-

1 Newspapers Null
2National Newspapers 1
3 Daily Express 2
4 Express Publications 3
5 Express Supplements 3

and so on. What I would like to be able to do is flatten out the hierarchy so I get something like below where each level is in a column.

NewsPapers National Newspapers Daily Express Express Publications
NewsPapers National Newspapers Daily Express Express Supplements

Ive used CTE's for displaying the hierarchy and producing aggregate figures when joing the hierarchy to spend information but am struggling to come up with any code that would produce a flattened hierarchy.

Any help would be greatly received!



View Replies !   View Related
Import Hierarchical Data
hi folks,

I have to import hierarchical text files like:

additionally I have to eleminate doubles. what is the best way for this problem ?
I have set up a flatfilesource with two columns and a conditional split on the first column
so now I have an output with [country;city;postalcode;street] and one with [name;firstname;salutation;title;age;nickname]. How do I split this in columns, put it in a dataset keeping the relations and remove doubles ?

Iam looking forward for any helping idea.


View Replies !   View Related
Filtering Hierarchical Data

Can any body tell me how can i filter hierarichal data in a table while doing Merge replication?

I have a table having a column self referencingtothe primary key of one of thetable record. This way

aparent-child-grandchikd relation is maintained between entities.

I want tofilter this relationshipon the basis of SUSER_Sname(). I have toparttion the data for the current user

so that all of its children and grand children can also be replicated.

Did anyone have had similar problem in past.

I would really appreciate the help.


View Replies !   View Related
Hierarchical Resultset Sorting
I have a query like this

with TempCTE(id, Name, level, sortcol)
Select id, Name, 0 as level,
cast(cast( id AS BINARY(4)) as varbinary(100)) sortcol
from Table1
where id = 1

union all

Select id, Name, 0 as level,
cast(sortcol + cast( id AS BINARY(4)) as varbinary(100)) sortcol

from Table1 inner join TempCTE on = Table1.parentid

select * from TempCTE order by sortcol

My problem is I want to sort this hierarchical resultset further on name like



View Replies !   View Related
Hierarchical Data In Result Set
How can I create a function that returns hierarchical data from a table with this structure:

- CategoryID
- CategoryName
- CategoryFather

I want to bring the result set like this...

CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video |0 | 0
2 | DivX |1 | 1
3 | WMV |1 | 1
4 | Programming |0 | 0
5 | Web |4 | 1
6 | ASP.Net |5 | 2
7 | ColdFusion |5 | 2

How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.


View Replies !   View Related
Hierarchical Data Model

I would like to know best way to design the database for the following requirement. I have a collection of tree nodes. each node has a type and set of attributes and a parent node (except for the node which has no parent). node type refers to the level of the node in the tree. child node inherits the attributes from the parent node (similar to object oriented programming where derived class inherits properties of the base class). user can add/update/delete nodes from the tree and user can choose to override the attributes of the parent node in child node.
what is best way to store this type of data? should there be a separate table for each node type (level in the tree). but the problem with this approach is that we need to duplicate the columns of the parent node, because user can overwrite the parent node attributes in the child node. there can be more than one at the same level and all of them share same set of attributes. this concept is exactlylike inheritance in object oriented programming. as far as the data is concerned, there are around 15 levels, around 30K nodes and 30 attributes spread across different node levels.


View Replies !   View Related
Storing Hierarchical In A Relational Database
What is the best approach for storing hierarchical
data in a database? For example, if I need to store a tree menu system,
how would I do that allowing for the most normalization within the
database, using the least number of queries/resources when pulling the
data out, and using the least amount of overhead both in storage and


View Replies !   View Related
Hierarchical Selection Within A Select Statment
CREATE TABLE RS_A(ColA char(1), ColB varchar(10))INSERT INTO RS_AVALUES ('S', 'shakespeare')INSERT INTO RS_AVALUES ('B', 'shakespeare')INSERT INTO RS_AVALUES ('P', 'shakespeare')INSERT INTO RS_AVALUES ('S', 'milton')INSERT INTO RS_AVALUES ('P', 'milton')INSERT INTO RS_AVALUES ('B', 'shelley')INSERT INTO RS_AVALUES ('B', 'kafka')INSERT INTO RS_AVALUES ('S', 'kafka')INSERT INTO RS_AVALUES ('P', 'tennyson')SELECT * FROM RS_ANow i need a select which selects based on hierarchyif ColA = 'S', then select only that rowelse if ColA = 'B' then select only that rowelse if colA = 'P' then select only that rowSo my results should look likeS shakespeareS miltonB shelleyS kafkaP tennysonIs there a way to do this within a select statementI tried using a CASE in WHERE CLAUSE but it put out all rows whichexisted/If any of you can help me with this right away, its is greatlyappreciatedThanks in advance

View Replies !   View Related
Help With Bottom To Top Query Into Hierarchical Data
Hello to all:First let me apologize for the length of this question, I've made an attemptto include as much information as is needed to help with the question.I am having problems putting together a query to pull out an alternativehierarchical view of my data. The database is implemented under SQL Sever2000 and I am writing the front end using VB.Net and The followingis the portion of my database structure that I am having problems with. Inthis diagrameach table has a one to many relationship with the table beneath it (Theprimary key for each table is the table name with ID appended, and theforeign keys for each child table refernce the primary key in the tableabove).Deals|Contracts___|Offers || | || |__OfferDetails| |Acceptances || ||__AcceptanceDetailsNotice that an Offer and it's associated Acceptances can be in more than oneContract, the OfferDetails describe how the Offer is broken up into eachContract.While there is a one to many relationship between Contracts andOfferDetails, there would only ever be a single OfferDetail per Contract perOffer (i.e. splitting up the Offer means assigning one and only oneOfferDetail to each Contract it is assigned to). This info may not berelavant, but it can be counted on as true. This doesn't hold true with theAcceptanceDetails however, there can be multiple AcceptanceDetails perAcceptance associated with OfferDetails.I wish to establish a couple of different heierachical grids into this dataas part of my user interface:View by Deals (In this view we see only the part of Each Offer associatedwith the Deal at the top of thehierarchy):Deal1Contract1Offer1 + OfferDetails associated with Contract1Acceptance1 + AcceptanceDetails assoctaited with OfferDetails above.Deal2Contract2Offer1 + OfferDeatils assoctaited with Contract2Acceptance1 + AcceptanceDetails associated with OfferDetails above.This grid has been relativly straitforward to figure out, as the hierrchicalinformation is alreadystructured to plug it into a hierrchical grid control easily (aftercombining the Offers and OfferDetailsinto a new table).View by Offer/Acceptance (In this view we see the entire Offer+Acceptance asthe top of the hierarchy,and each Deal (and associated Contract) underneath the Offer+AcceptanceOffer1+Acceptance1 (with all the details rolled up into a summuray includedin this row).Deal1Contract1Deal2Contract2This is the grid which is causing me problems, the problem area beingputting together a query which retrieves each DealID assoctiated with theOfferID at the top of the hierarchy. I need a query which essentiallyStarts with the OfferID, pulls each OfferDetailID associated with it, thenmoves upstream tofind each ContractID assoctaed with the OfferDetailsID (saving those ID'sfor use in third level of the grid, since there can be more Contracts underthe Deal which are not associated with the Offer we're looking at), and thenmoves upstream again to finally get the DealID which forms the second levelof the hierarchy.Can somebody help me with what such a query (or queries, as I assume I'llneed one to get the DealID's and another to get the ContractID's, althoughthe former might use the latter as a sub-query) might look like? I'm not atall good with T-SQL, but I'm learning fast! I haven't been able to find anyexamples onhow to go backwards up a tree to get to a record. I'll most likely beimplementing these as stored procedures, as these grid views are integral tomy userinterface.Thanks in advance to any who take the time to reply!

View Replies !   View Related
On Delete Cascade && Hierarchical Table
for MS SQL 2000
I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE

CREATE TABLE [dbo].[Users](
[id_UsersSup] [int] NULL,
[Users] [nvarchar] (100) NOT NULL

ALTER TABLE [dbo].[Users] ADD
) REFERENCES [Users] (

but MS SQL refuse to create the foreign key
even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under

thank you for helping

View Replies !   View Related
Displaying Employees In Hierarchical View
How can Idisplay employee relationships in a tree like view with drilldown in SSRS? For instance, in the sample Adventure Works cube, the 'Dim Employee' dimension has a 'Parent Employee Key' hierarchy. Ideally, I'd like to display the employee names rather than IDs. After doing a lot of searching around, it seems thatI need to create a measure that stores a member's parent and use that to group employees, but I can't figure how to use that in my actual report for grouping. My employeehierarchy isjust like the Adventure Works 'Parent Employee Key' hierarchy, so if i can figure out how to display the employees in this cube, I'll be able to do it in my cube. The query for my dataset so far is:

WITH MEMBER [Measures].[EmployeeBoss] AS '[Dim Employee].[Parent Employee Key].CurrentMember.Parent.MEMBER_CAPTION'


[Measures].[EmployeeBoss], [Measures].[Sales Amount]



(DESCENDANTS([Dim Employee].[Parent Employee Key].[Level 02].ALLMEMBERS) )



How do I use this to properly format the results so I can drilldown on employees rather than getting a flat list?


View Replies !   View Related
Implementing Inheritance For Hierarchical Data

in my application, the data is in hierarchical format. there is a tree with set of nodes having parent child relationships. this data can be stored either through adjacency or nested set model approach. this is fine. but the issue here is that each child node inherits the properties of its parent node, parent's parent node and so on until the root node. lets say root node has two attributes A1 and A2 and they are stored in two columns in a table. but its child nodes inherits this data from its parent and it has its own extra attributes. so should I copy parent's data for the child node as two additional columns? the problem is that there are around 15 levels in the tree and the attribute list grows from top to bottom in the tree. lets say I need to find all the attributes for a leaf node in the tree (both direct and inherited), if I am not storing the inherited attributes for each node, then I need to walk-up the tree and find all the inherited attributes. there are around 30K nodes and each node has around ten attributes. xml is not option because of large volumes of data andauditing and reporting on individual nodes.what is the best way to store this type of data?my currentapproach is to have an attribute table having nodeid as a foreign keyand only store the direct and NOT the inherited attributesof the node in thetable, but this meansto find all the attributes for the node, I need to gatherthe attributes of all the parents until the root node.Ican't see any easy wayout for this.

View Replies !   View Related
TOP N Value Per Category
HelloI am using sql server 2005.I have two tables as described below.Table1UserID UserSales---------------------1 102 133 174 195 216 107 128 119 3110 2311 2412 1013 16Table2UserID Country----------------------1 Canada2 Canada3 Canada4 Canada5 Canada6 USA7 USA8 USA9 USA10 USA11 UK12 UK13 UKI want to get top 2 UserSales for each country and remaining should bedisplayed as Total as Others for that country.Can someone please help me with this query?RegardsAmit

View Replies !   View Related
Retrieving Hierarchical Data From A Single Table
I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure. Example data:ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level------------------------------------------------------------------------------------------------------------------------------------------------1                           Custom Furniture     0                                                                             02                           Boxes                     0                                                                             03                           Toys                       0                                                                             04                           Bedroom                 1                                    Custom Furniture                15                           Dining                     1                                    Custom Furniture                16                           Accessories            1                                    Custom Furniture                17                           Picture Frames        6                                    Accessories                       28                           Serving Trays           6                                    Accessories                       29                           Entertainment          1                                    Custom Furniture                110                         Planes                     3                                    Toys                                  111                         Trains                      3                                    Toys                                  112                         Boats                      3                                    Toys                                  113                         Automobiles             3                                    Toys                                  114                         Jewelry                    2                                    Boxes                                115                         Keepsake                2                                    Boxes                                116                         Specialty                 2                                    Boxes                                1Desired output:Custom Furniture     Accessories          Picture Frames          Serving Trays     Bedroom     Dining     EntertainmentBoxes     Jewelry     Keepsake     SpecialtyToys     Automobiles     Boats     Planes     Trains

View Replies !   View Related
Creating Reports Based On Hierarchical XML File
Hi,We have XML files (and its XSD) that are dump of our tree-like datastructure in memory. These XML files may potentially have unlimitedlevels of nesting because some elements can contain themselves(recursively defined).We want to build some reports using some third party reporting tool(Crystal Report, for example).The first question is - is there any way for Crystal Reports toprocess such complicated (some elements have recursive definitions)XML file directly? If not, anyone knows any other reporting tool thatcan do that job?The second question is - if we have to develop a small applicationthat converts the XML to dataset, anyone who has done similar thingbefore can give us some recommendations as to which approach is theeasiest to take (Java, .NET, etc.)? I'd appreciate it if someone canpoint me to some resources to get me started.Another approach is to convert the hierarchical XML into relationaltable set and store the data into relational database. In that case,the reporting tool can simply read from the database. I'd appreciateit if someone can let me know if there is any softeware/program thatcan do the conversion. (I heard that some database server can do theconversion from XML to relational tables, but didn't find anythingconcrete.)Any comment/recommendation is appreciated!Thank you for your help in advance!!

View Replies !   View Related
Path Betwwen Two Nodes (Hierarchical Queries)
I need sql statement in sql server to print path between any of the two nodes.All node Elements and their respective parents are saved in a table.
Thanks in advance.

When you have nothing to Loose, You'll get everything you want

View Replies !   View Related
ETL For Type 2 Slowly Changing Hierarchical Dimensions

I would like to know if there is an easy way to load Hierarchical Dimensions with Type 2usingSSIS ( or in general).

Here is my example:

There is a Hierarchy Product Group <- Product Class<- Product. (In words, Product rolls up to Product Class and Product Class Rolls up to Product Group). Say Product Group is Type 1 and the other two are Type 2. Every night, I receive a feed for each of these tables only if the record is new or a change to an existing record.

Now, when a Product Class Record is changed to assign it to a different group then I receive the feed only for Product Class (but not for Product).To load this record, I expire the old record and create a new entry with a new Surrogate Key. Then how do I automatically cascade this change to Product andmake a Type 2 change to use the new Surrogate Keyfrom Product Class?

Any ideas would be greatly appreciated.


View Replies !   View Related
Designing (or Gathering Data From): A Hierarchical Database Using SQL
Hi All,

I amattemptingto create a Visual C++ application based on displaying financial charts and am using SQL Express to store Stock information such asthe Exchanges the stocksare traded on, the indicessectors they belong to and the Closing prices for as long as I can download data for. I am not proficient in C++ nor SQL and am using this project to learn both languages as well as making myself rich beyond my wildest dreams.

I have "designed" a database with the following tables:

tblDate_ 1 columnclmDate (Primary Key, smalldatetime, NOT NULL)

tblStockExchange_ 4 column clmStockExchangeID (PK, int, NOT NULL)
clmParentID (int, null)
clmStockExchange (nvarchar(50), NOT NULL)
clmMarkets_ (FK, nchar(20), NOT NULL)

tblMarkets_1 column clmMarkets (PK, nchar(20), NOT NULL)

tblIndices_1 columnclmIndices (PK, nchar(50), NOT NULL)

tblSectors_1 columnclmSectors (PK, nchar(50), NOT NULL)

tblMarkets_Sectors 3 columns clmMarkets_SectorsID(PK, int, NOT NULL)
clmMarkets_ (FK, nchar(20), NOT NULL)
clmSectors_ (FK, nchar(50), NOT NULL)

tblSecurities_ 4 columns clmEPIC (PK, nchar(10), NOT NULL)
clmSecurity_Type (nchar(5), NOT NULL)
clmSecurty_Name (nchar(50), NOT NULL)
clmSectors_ (FK, nchar(50), NOT NULL)

tblSecurities_Indices 3 columnsclmSecurities_IndicesID (PK, int, NOT NULL)
clmEPIC_ (FK, nchar(10), NOT NULL)
clmIndices_ (FK, nchar(50), NOT NULL)

tblSecurities_Date_OHLCV8 columns clmOHLCVID (PK, int, NOT NULL)
clmEPIC_ (FK, nchar(10), NOT NULL)
clmDate_ (FK, smalldatetime, NOT NULL)
clmOpen (float, NOT NULL)
clmHigh (float, NOT NULL)
clmLow (float, NOT NULL)
clmClose (float, NOT NULL)
clmVolume (float, NOT NULL)

Why so many tables? perhaps you should put some more in...

This was the only way I could work out how to store one-to-one and one-to-many relationships required for:

- Many closing prices for many stocks
- Stocks belonging to many indices
- Stocks belonging to only one sector
- Stocks belonging to only one market (MainMarket or AIM for LSE)
- Stocks belonging to only one Exchange (I am aware of dual listed stocks but one thing at a time)

Why nchar's and not nvarchar's?

Because I didn't realise the benefits of nvarchar's until recently. How can I change this a loose the extra spaces in the cells.

Why do some tables have IDs and others don't?

I decided to put ID columns in for tables that didn't have obvious Primary Keys - if someone could explain the advantages if ID columns I would be grateful.

To the SQL Professional's eye there will be some obvious things wrong with this design and your criticism is welcome. The database I have is achieving what I would like it to do; I can plot charts using the data but I have ran into problems when trying to create a TreeView control which is what I would like to use as a navigational tool in my application.

It would seem that pulling hierarchal data from a relational database, to pass to theTreeView control,is a tricky task to say the least. I have found many articles online which discuss how to do this (using an Adjacency List Modelor Nested Set Model)but they define a fairly simple example at the beginning (based on fruit or electrical goods) butdon't appear to talk about gathering data from an existing relational database or changing an existing relationaldatabase so that it is more suited to storinghierarchal information. I have Joe Celko's - Tree and Hierachies in SQL for Smarties but sadly this fine material is a little beyond me!

Iwould likethe hierarchy to look like this:







I have written three queries to get the StockExchangeMarketSectorStock information individually from each table but am struggling with ways to put all the rows together, add left and right values (Nested Set Model) then run queries against this to get individual nodes to pass to the TreeView control. Therefore is there something I need to add to the original design?

Any help would be greatly appreciated.

View Replies !   View Related
Breakdown By Category
Let's say you a 1000 records in the Employees table, who are spread over 40 different cities.
How would you get a breakdown of how many employees in each city ?

Do I have to loop with  a  Count(*)   for each CityID, or something ?

There must be a more straightforward method.

View Replies !   View Related
HAVING (COUNT(category) &> 1) , Not Only 1 Row
i am using this code :

SELECT MAX(user) AS lastuser, category
FROM journal
GROUP BY category
HAVING (COUNT(category) > 1)

it works but returns 1 line by category >1

i need all the user (all the rows) HAVING (COUNT(category) > 1) , not only 1

if 1 category has only 1 user i must not keep it

i am not shure to be clear :-)

thank you for helping

View Replies !   View Related
Category Tables
What are the thoughts on using a categoy table? What I mean by this is to have a table used to hold simply a category identifier for other table(s).


Hardware Table
Hadware (PK) | Category (FK) | Description | Finish | etc....
P1 | Plate | 4"x4"x1/4 plate | Painted |

Category (PK)

Should this be done or simply provide a category field in the hardware table? I like the extra table because it kind of ensures that a user doens't add a plate with a category value "Plate", another user adds "plate", and another user adds "Plates", etc....

What are your thoughts if any?

Mike B

View Replies !   View Related
Category / Subcategory
Dear All,

I am doing a auction project and my requirement is

I need to show the Main category along with total count of products in it and its subcategories...

Electrical & Households (11)
Kitchen(5) Dining(6)

In the above out put the Electrical & Household is the main category and Kitchen(5) Dining(6) are subcategories..

My table is


cid Numeric Identity

Parent Numeric

Cat_Name Varchar(100)


pid Numeric Identity

Cat_Id Numeric (foreign key of tbl_cat)

Prod_Name Varchar(100)

For clear idea pls visit

Pls assist me ...



View Replies !   View Related
Top 3 For Every Category With Group By

I have a table to store salesman's performance..

as you can see at structure.jpg, Tom sold total 18 Processors (5 different modals) and sold 11 mainboards (4 different modals)

I want to report that
what are the TOP 3 Processor and Mainboard modals that Tom sold with the same query

I tried different group by methods. also subqueries.
I could't do

you see the results I need at result.jpg

View Replies !   View Related
Update Sys Category
I'm trying to remove a read only/stand by database that I believe was a messed-up attempt at log shipping. When I go through the interface it says 'cannot remove database because it is set up for replication'. If I try and 'directly update' the sys tables (yea I know you can't do that anymore) I get the error:

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

So my dilema is I just want to delete a database that was attempting to do log shippinig and is now stuck in read only/standby. How can this be done in SQL 2005?


View Replies !   View Related
A New Approach To Storing Ordered Hierarchical Data In RDBs.
Hello!I've discovered a new approach to storing ordered hierarchical data inRDBs. As far as I know there is no similar model out there and itdiffers significantly from traditional models (such as adjacency list,materialized path or the nested sets approach). A brief articledescribing the model (with accompanying sql code) can be found at

View Replies !   View Related
Guidance Needed: Loading Hierarchical XML Into Relational Tables
I've got a lot of XML like this (simplified):

Code Snippet

... 8 MORE


I need to get this into three existing SQL Server 2005 tables, each with identity columns for their primary keys:

Code Snippet






With primary and foreign keys as you'd expect, and, of course, many more columns!

How would I get this into tables through SSIS, preferably in a high-performance manner (there may be several gigabytes of XML to load).

The issue, of course, is that in order to insert an ELEMENT2 row, I need the ID from the coresponding ELEMENT1, etc.

Any ideas or pointers to articles would be welcome.

View Replies !   View Related
Trying To Select Newest Row For Each Category...
Hi everyone -

I am building from the Time Tracker Start Kit, trying to get a better feel for how MS thinks we should do things.

In my editing, I have built a new Stored Procedure, trying to pull the newest entries for a specified person.

I have the following that will return all entries for a specific person, sorted by date, newest first:

EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
ManagerUserID, TT_Categories.Abbreviation AS CatShortName
TT_EntryLog.CategoryID = TT_Categories.CategoryID
TT_EntryLog.ProjectID = TT_Projects.ProjectID
UserID = @UserID
EntryDate Desc

This will return something like:

EntryLogId Description Duration EntryDate ProjectID CategoryID CategoryName ProjectName ManagerUserID CatShortName
14Can type date in date... .00 2004-02-04 10:28:00116Pros ITS Project Management App 3 Pros
12Changed "Entry Date"... .00 2004-02-03 13:28:00116Pros ITS Project Management App 3 Pros
13Added default button ... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros
11Removed hours per p... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros
6Isn't this cool .00 2004-02-02 00:00:00229Pros Knowledge Base 3 Pros
9Added week-by-week... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros
10Fixed Update comma... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros
1Built initial framewor... 6.00 2004-01-30 00:00:00116Pros ITS Project Management App 3 Pros
5Adding up to 8 hours... 2.00 2004-01-30 00:00:00229Pros Knowledge Base 3 Pros
3Debugged - fixed a f... 1.00 2004-01-23 00:00:00229Pros Knowledge Base 3 Pros

What I would like to accomplish is to return only the newest entry for each ProjectID (so in the above example, there would only be 2 entries, EntryID 14 and 6)

Any ideas?

Thanks in advance-

View Replies !   View Related
10 Random Records, 1 From Each Category
I'm not sure if this is a completely dumb question, but please humor me:) I have a table of records, called Records, each of which has aCategory_ID that places it in a specific category; the details of thecategories are stored in another table called Category. What I need todo is retrieve a recordset that contains one record from each category,but where the records that are retrieved are random. I know how toretrieve one or more random records using "order by NewID()", but havenot been able to work out how to get one random record from eachcategory.Any assistance in this puzzler will be HUGELY appreciated!ThanksFEB

View Replies !   View Related

Copyright 2005-08, All rights reserved