I noticed that by inserting another group higher than the existing one gave me the effect of a nested group...so what would a recursive hierarchy have done for me that is different? I read the BOL writeup and didnt come away with any sense of what these hierarchies offer, seemingly thru the use of a "parent" entry.
View 1 RepliesIf you've ever worked with CI (UPC barcodes), certain commercially distributed products can be "packaged" in certain ways as defined by suppliers. For example, buying a six-pack of Coca-Cola, each one of the cans have a barcode UPC that indicates that the particular can is to only be distributed as part of a "pack" - they can't be distributed or sold individually.
Certain products can be distributed and sold in more than one way. For example, that same six pack can be distributed and sold as a case bundle, or it can be distributed as a single six pack. Each packaging method has a certain hierarchy.you can't represent the CI model using anything less than an adjacency matrix.Now, this can be represented in SQL pretty easily - there's tons of examples online that go through the basics of how to do it and how to write the recursive CTE function to represent and return data in this format. My problem is with MDS.
In Master Data Services (MDS) you have several hierarchy types to choose from. The hierarchy type that makes the most sense to me is to use the recursive derived hierarchy. It makes sense because I want to define an entity that contains all the "acceptable" adjacencies for particular items, such that when a user looks at the data in MDS or tries to update its value, it will have certain domain-based enforcement (and thusly lookups).I think that I've crossed a bridge too far with MDS - but, perhaps one of you knows something I don't know. It looks like any hierarchy in MDS is only based in a parent - child relationship, so that's not quite what the doctor ordered here.
Hi,
I have a recursive hierarchy in SSRS2005 which contains accounts like so:
+ Revenue
+ Expense
which is grouped using details group and can be expanded like:
+ Revenue
- Revenue type
- Revenue type
- etc.
+ Expense
- Expense type
- Expense type
- etc.
It can be drilled down in five levels. This works fine in both SSAS and SSRS, but when I export the report to excel 2003, the hierarchy gets flattened, meaning that every account on the detail level (level 5) is exposed. Not what I had in mind. Rather SSRS should create a hierarchy in excel as well..
Is there a workaround for this?
does anyone know why my initial visibility expression (=Level()>1) stays in effect after generating my report? The idea was to display only a couple of levels in the hierarchy at first, so that the user could more easily decide what he wants to expand. But expand doesnt do anything after initial rendering.
The only way I could get this kind of report to work (show hierarchy and expand/collapse) was to live with initial visibility = "visible", but unfortunately, that setting shows all levels right away, not a very user friendly thing.
I am running Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I have a family table and would like to group all related members under the same familyID. This is a replication of existing business data, 14,000 rows. The familyID can be randomly assigned to any group, its sole purpose is to group the names:
declare @tv table (member varchar(255), relatedTo varchar(255))
insert into @tv
select 'John', 'Mary'union all
select 'Mary', 'Jessica' union all
select 'Peter', 'Albert' union all
[Code] ....
I would like my result to look like this:
familyID Name
1 John
1 Mary
1 Jessica
1 Fred
2 Peter
2 Albert
2 Nancy
3 Abby
4 Joe
4 Frank
Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
I have 3 tables, please find the table structure and sample data below
Table 1 : tblUserInfo
UserID | FirstName | Email | JoinedDate
1 Testuser01 User01@User.com 10-10-2013
2 Testuser10 User10@User.com 11-10-2013
3 Testuser20 User10@User.com 11-10-2013
Table 2 : tblOffers
OfferID | OfferName | ExpiryDate
1 OfferSample1 15-10-2014
2 OfferSample2 15-9-2014
3 OfferSample3 10-07-2014
Table 3 :tblOfferActivated
ActivationID | UserID | OfferID | ActivationCode | ActivatedDate
1 2 3 ABC 11-11-2013
2 2 1 CEG 13-11-2013
3 3 1 JHG 18-11-2013
4 3 2 KIU 20-11-2013
Expected Output
I want to list out the users with the first activated offer details.The OfferName Should be based on the first activated date
UserID | FirstName | Email | JoinedDate | OfferID | OfferName |ActivatedDate | ActivationCode
1 Testuser01 User01@User.com 10-10-2013 Null Null Null Null
2 Testuser10 User10@User.com 11-10-2013 3 OfferSample2 11-11-2013 ABC
3 Testuser20 User10@User.com 11-10-2013 4 offerSample3 18-11-2013 JHG
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
View 4 Replies View RelatedI am currently running a site on a shared environment with sql db. The account is with verio.com: Windows Server 2003m SQL 2000.
What we would like to do is create a back up site with a backup db. I would like this functionality so that in the event of a crash, while the current site is being repaired, or if the server is down for a prolonged period, I can simply switch to the backup site w/ db.
What would the preferable method of doing this in such an environment? Any help would be greatly appreciated. Thanks.
SQL SERVER 2000Hi allThis is my first attempt at writing a stored procedure. I have managed toget it working but its unlikely to be the best way of handling the problem.While writing it I found some things that I don't understand so if any onecould shed any light it would be much appreciated. I have posted these atthe end.Sorry about the length but I thought it might be worthwhile posting the codeThe purpose of the procedures is as follows : we have a view of lots of bitsof information that need automatically mailing to different people. eachelement of information has a name allocated against it. If we had 100 piecesof data, 50 could go to manager 1 25 could go to manager 2 and 25 to manager3 etc...Both SP's look at the same viewThe first SP generates a distinct list of managers and for each managercalls the second SPThe second SP filters the view for the data belonging to the selectedmanager, and builds an HTML mail. It then sends all the bits of informationbelonging to that manager off in an EMAIL to him/her. ( It uses a brilliantbit of code from sqldev.net to handle the mail)the first mail then repeats for all the managers in the listCODE ---- SP 1ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION_2ASbeginSET NOCOUNT ONdeclare @no_of_managers as intdeclare @current_record as intdeclare @manager_name as varchar(100)-- count how many distinct managers we need to send the mail toselect @no_of_managers = COUNT(DISTINCT manager_name) FROMdbo.vw_client_notification_email_1-- open a cursor to the same distinct listdeclare email_list cursor for select distinct manager_name fromdbo.vw_client_notification_email_1 dscopen email_list-- for each distinct manager get the managers name and pass it to the storedprocedure that generates the mail.set @current_record = 0while (@current_record) < @no_of_managersbeginfetch next from email_list into @manager_nameEXECUTE dbo.pr_admin_client_weekly_notification @manager_nameset @current_record = @current_record+1end-- close the cursorclose email_listdeallocate email_listendCODE ---- SP2ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION(@current_manager_name as varchar(100))-- a unique managers name is passed from the calling procedureas beginSET NOCOUNT ON-- declarations for use in the stored procedureDECLARE @to as varchar(100)DECLARE @entry varchar(500)DECLARE @region as varchar(100)DECLARE @type as varchar(100)DECLARE @site_ref as varchar(100)DECLARE @aborted as varchar(100)DECLARE @weblink as varchar(1000)DECLARE @manager_name as varchar(100)DECLARE @manager_email as varchar(100)DECLARE @body VARCHAR(8000)DECLARE @link varchar(150)DECLARE @web_base VARCHAR(150)-- set up a connection to the view that contains the details for the mailDECLARE email_contents cursor for select region,type,site_ref,aborted_visit,link,manager_name,manager_e mail fromvw_client_notification_email_1 where manager_name = @current_manager_nameopen email_contents--some initial textset @body = '<font color="#FF8040"><b>Reports W/E ' +convert(char(50),getdate()) + '</b></font><br><br> <a href = http://xxxx > Click here to logon to xxxxx </a><br><br> '--fetch the first matching record from the table and build the body of themessagefetch next from email_contents into@region,@type,@site_ref,@aborted,@link,@manager_na me,@manager_emailset @web_base = 'http://'set @weblink = @web_base + @linkif @aborted = 0 set @aborted = '' else set @aborted = 'ABORTED'set @body = @body + '<font size="2"><b> Region </b>' + @region+ ' <b>Type</b> ' + @type+ ' <b>Site Reference </b> <a href = "' + @weblink + '">' + @site_ref+'</a>'+ ' <b>Unique Report Reference </b>' + @link + '<br>'-- continue reading the records for this particular message and adding on tothe body of the textwhile(@@fetch_status = 0)beginfetch next from email_contents into@region,@type,@site_ref,@aborted,@link,@manager_na me,@manager_emailif @aborted = 0 set @aborted = '' else set @aborted = 'ABORTED'if (@@fetch_status = 0) set @body = @body + '<b> Region </b>' + @region+ ' <b>Type</b> ' + @type+ ' <b>Site Reference </b> <a href = "' + @weblink + '">' + @site_ref+'</a>'+ '<b>Unique Report Reference </b>' + @link + '<br>'end-- close the cursorset @body = @body + '</font>'close email_contentsdeallocate email_contents-- generate the mailDECLARE @rc int EXEC @rc = master.dbo.xp_smtp_sendmail@FROM = N'FROM ME',@TO = @manager_email,@server = N'server',@subject = N'Weekly Import',@message = @body,@type = N'text/html'endQuestionsis the way I've done it OK. I thought I would be able to do it in a singleSP but I really struggled nesting the cursor things.@@fetchstatus seems to be global, so if your using nested cursors, how doyou know which one you are refering to. If you have multiple calls to thesame SP how does it know which instance of the SP it refers to.When I first wrote it, I used a cursor in SP1 to call SP2, but I couldn'tget the while loop working - I have a feeling it was down to the @@fetchstatus in the 'calling' procedure being overwritten by the@@fetchstatus in the 'called' procedure.The whole @@fetchatus thing seems a bit odd. In the second procedure, I haveto fetch, then check, manipulate then fetch again, meaning that the samemanipulation code is written twice. thats why in the first procedure I usedthe select distint count to know how long the record set is so I only haveto run the manipulation code once. Is what I have done wrong?its possible that the body of the mail could be > 8K, is there anotherdatatype I can use to hold more than 8Kmany thanks for any help or adviceAndy
View 3 Replies View RelatedI have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
HI!
I am new in DB so I need some advices for finding the right solution.
I need to be able to make automatically any join between tables which the user choose and deliver the result.
is like making an hierarchy between all tables in DB (parent-child) and then making the select statement for the right join.
if u have any idea about how can I manage this, pls help me.
thanks a lot!
I have an Interesting situation that I'm hoping some of you experts can help me with basically I have the following hierarchy:
Net Profit
.....Gross Profit
..........Revenue
..........Direct Costs
.....Indirect Expense
Now, I have another hierarchy - separate from the above - that I need to assimilate to the hierarchy above - Example:
Expenses
.....Node a
..........Child a (Indirect Expense Type)
.....Child b (Direct Cost type)
Net Sales Adjustments
.....Child a (Revenue type)
.....Child b (Revenue type)
Depending on the type, the top most node needs to be assigned to the appropriate node in the initial hierarcy, like this:
Net Profit
....Gross Profit
........Revenue
................Net Sales Adjustments
....................Child a (Revenue type)
....................Child b (Revenue type)
........Direct Costs
...........Expenses
.................Child b (Direct Cost type)
........Indirect Expense
............Expenses
.................Node a
.....................Child a (Indirect Expense Type)
Now, I've been able to figure out how to assign the top most node and leafs if all the children have the same type (using the expan stored proc listed in the books online), but my question is on this portion:
Expenses
.....Node a
...........Child a (Indirect Expense Type)
.....Child b (Direct Cost type)
Basically, the answer is to work backwards - if a child has a type that is different than the other children, a copy of the hierarch (up to the child) needs to be made and assigned to the appropriate initial node.
I've tried modifying the expand stored proc to give me the lineage of the child and see if there's a way I can copy the node and place it appropriately - I'm brainfried at this point, and I'm hoping that someone outthere can point me in the right direction.
Thanks in advance for your time
Hai everyone.,
i need to get solution for tree hierarchy in sql is there any solution or any keyword like 'connect by prior' in oracle ..
plz help me on this..
for example:
id | FName |parentid |
1 | sandy |
2 | robert| 1
if i give the parentid 1 in where condition of a query i need the details of 'sandy'
Thanks in Advance.
B.Arul.
Hello!I have a table that looks like this:Col1; Col2; Col3; Col4; Col538; 75; 233; 916; 277038; 75; 233; 916; 277138; 75; 233; 916; 277238; 75; 233; 923; 265438; 75; 233; 923; 265538; 75; 245; 913; 245438; 75; 245; 913; 2456....And I need a query (not a procedure) that shows me this:38; NULL; NULL; NULL; NULLNULL; 75; NULL; NULL; NULLNULL; NULL; 233; NULL; NULLNULL; NULL; NULL; 916; NULLNULL; NULL; NULL; NULL; 2770NULL; NULL; NULL; NULL; 2771NULL; NULL; NULL; NULL; 2772NULL; NULL; NULL; 923; NULLNULL; NULL; NULL; NULL; 2654NULL; NULL; NULL; NULL; 2655NULL; NULL; 245; NULL; NULLNULL; NULL; NULL; 913; NULLNULL; NULL; NULL; NULL; 2454NULL; NULL; NULL; NULL; 2456....Does anybody know how i can get this result? How?Help! Thank you!SQLNullps: SQL-Server 2000
View 3 Replies View RelatedHi to All!
Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way:
[Node | ParentId | ParentType | ChildId | ChildType]
But there is this nagging little voice saying it can be better
Cheers!
Nele
Hi, I'm trying to figure out how to write I SP on the following table that will produce the results show at the bottom. My main problem is the sorting, is there a way I loop through the records in TSQL and ORDER BY SortSort where the ParentID's are the same?
Thank you.
Table example
IDParentIDSortOrderCaption
1null1Main
211Files
313Folders
422File2
512Holders
621File1
752Holder2
851Holder1
Required result example
Main
-Files
--File1
--File2
-Holders
--Holder1
--Holder2
-Folders
Hi
i tried on CTE but iam not gettng correct result plz help me
Declare @TMaster Table (MasterId int, Type varchar(100), ParentMasterId int)
Insert into @Tmaster
Select 1, 'Fixed', 14 Union All
Select 2, 'Flexible',14 union All
Select 3, 'Others',14 union All
Select 13, 'Retirement benefits',1 union All
Select 14, 'PBHeads', 0
Select * From @Tmaster
Declare @TDetails Table ( MasterId Int, Description varchar(100))
Insert into @TDetails
Select 1, 'Basic' union all
Select 1,'Conveyance' union all
Select 1,'HRA' union all
Select 1,'Special Allowance' union all
Select 2, 'Children Education' union all
Select 2,'Travel Allowance' union all
Select 2,'Medical Expenses' union all
Select 2,'Variable Spl Pay' union all
Select 3, 'Project Allowance' union all
Select 3,'Quarterly Incentive' union all
Select 3,'Shift Allowance' union all
Select 3,'Annual Component' union all
Select 13,'Provident Fund' union all
Select 13,'Gratuity' union all
Select 13,'Super annuation'
Select * From @TDetails
in group list we have to show fixed, flexible, other
in subgroups(L1) list under fixed we have to show
Basic salary, HRA, Conveyance, Special Allowance,Retirement benefits
in subgroup(L2) we have to show Gratuity, providedfund, Super annuation they are subgroups of retirementbenefits
output :
Group Subgroup(L1) Subgroup(L2)
Fixed
Basic salary
HRA
Conveyance
Special Allowance
Retirement benefits
Provident Fund
Gratuity
Super annuation
Flexible reimbursements
LTA/Group Travel
Medical expenses
Children education
variable special pay
Others
Project allowance
Performance incentive
Medical insurance
Hi all,
Ive got a bit of a problem,
I have two tables:
CATEGORY
id
name
parent_CATEGORY_id
RECORD
id
CATEGORY_id
Stock_Held_Number
ps. Ive stripped out any non relevant fields
I also have the following query (again ive stripped out the non-relevant fields)
SELECT TOP (100) PERCENT SUM(dbo.RECORD.Stock_Held_Number) AS TotalStock, CATEGORY.Name AS FundName
FROM CATEGORY
LEFT OUTER JOIN dbo.RECORD ON CATEGORY.ID = dbo.RECORD.CATEGORY_id
GROUP BY CATEGORY.Name, RECORD.Stock_Held_Number
At the moment its grouping all the CATEGORIES and giving me a sum total for each which is great.
The problem I have is the CATEGORY table, there is an optional join to parent CATEGORY records on the table.
What Im trying to do is to provide a fully ordered result within the CATEGORIES and I don't have a clue.
For example:
The CATEGORY table has the following values
ID Name Parent_Category_id
1 Pens
2 Animals
3 Rocks
4 Horses 2
5 Dogs 2
When I currently run the query I get:
Name TotalStock
Pens 20
Animals 30
Rocks 40
Horses 50
Dogs 60
It's technically correct because I don't want the parent to calculate the total value of the children
What Im really trying to do is order them within the hierarchy though and indent (if possible) the result so I would get
Name TotalStock
Animals 30
---Dogs 60
---Horses 50
Pens 20
Rocks 40
Does anybody have any pointers as to how I can achieve this.
Many thanks
Mike
how do i create a parameter in hierarchy?
SAMPLE
billing period:
YEAR 2007
1st quarter
jan
feb
mar
2nd quarter
april
may
june
something like that.... if the user chose the 1st quarter the report will generate a sum-up report for the 1st quarter of 2007...
kindly reply asap.. i really need to get this report working..
thanks!
I have structure:FolderId, FolderName, ParentFolderId, FullPathe.g.1, First, null, First2, Sec, 1, First/Sec.....When update happens to FolderName, i need to update all FullPaths thatare below Folder which is changing.Any ideas?Thank you.
View 3 Replies View RelatedGreetings!I could really use some suggestions on how to improve on thefollowing, it at all possible:Table 'Customer'---------------------ID GUID PK....Table 'Facility'-----------------ID GUID PKCustomerID GUID FK (FK to Customer GUID)....Table 'Rate'----------------ID PKOwnerID GUID Nullable FK (FK to Customer, Facility GUID PK)OwnerLevel INT Contraint 1-3<Rate Data>Table 'Rate' is a sparse hierarchy of data. There are 3 possiblelevels in the hierarchy as follows:OwnerID <NULL>OwnerLevel 1This indicates Global rate data.OwnerID <Customer.ID>OwnerLevel 2This indicates Customer-specific rate data.OwnerID <Facility.ID>OwnerLevel 3This indicates Facility-specific rate data.Now, a given Customer need not have an entry in the Rate table. If aCustomer does not have an entry, it is supposed to 'inherit' Globalrate data. A given Facility need not have an entry in the Rate table.If a Facility does not have an entry, it is supposed to inheritCustomer-specific rate data, and in the absence of an entry for theFacility's parent Customer, it is supposed to inherit Global ratedata.The challenge is that I want to write a view to give me back theappropriate rate record for Customer and Facility. Here's what I'vedone so far.View _Rate--------------SELECTRate.*,NULL AS TargetIDFROMRateWHERERate.OwnerID IS NULLUNIONSELECTRate.*,Customer.ID AS TargetIDFROMRateCROSS JOINCustomerWHERERate.OwnerID IS NULLOR Rate.OwnerID = Customer.IDUNIONSELECTRate.*,Facility.ID AS TargetIDFROMRateCROSS JOINFacilityWHERERate.OwnerID IS NULLOR Rate.OwnerID IN (Facility.CustomerID, Facility.ID)View view_Rate--------------------SELECT_Rate.*FROM_RateINNER JOIN(SELECTTargetID,MAX(OwnerLevel) AS OwnerLevelFROM_RateGROUP BYTargetID) AS Filtered_RateON_Rate.TargetID = Filtered_Rate.TargetIDAND _Rate.OwnerLevel = Filtered_Rate.OwnerLevelThe combination of these two views gives a resultset that contains 1record for every Target ID as follows:TargetID <NULL>OwnerID <NULL>OwnerLevel 1This indicates Global rate data established at the Global level.TargetID <Customer.ID>OwnerID <NULL>OwnerLevel 1This indicates Customer rate data for the specific Customer identifiedby Customer.ID is inherited from the Global rate data.TargetID <Customer.ID>OwnerID <Customer.ID>OwnerLevel 2This indicates Customer-specific rate data for the specific Customeridentified by Customer.ID (not inherited).TargetID <Facility.ID>OwnerID <NULL>OwnerLevel 3This indicates Facility rate data is inherited from the Global ratedata.TargetID <Facility.ID>OwnerID <Customer.ID>OwnerLevel 2This indicates Facility rate data for the specific Facility identifiedby Facility.ID is inherited from the Facility's parent Customer'sCustomer-specific rate data.TargetID <Facility.ID>OwnerID <Facility.ID>OwnerLevel 3This indicates Facility-specific rate data for the specific Facilityidentified by Facility.ID (not inherited).I know this is a lengthy post, and a complicted query scenario, butI'm not willing to accept that my solution is the best solution justyet. Please consider that I really need this functionality in a VIEWas much as possible.Thank you for your learned consideration.I eagerly await your replies.Darryll
View 2 Replies View RelatedHi everybody,
I've been reading the book MCITP Self-Paced Training Kit (Exam 70-441): Designing Database Solutions by Using Microsoft SQL Server(TM) 2005 (Self-Paced Training Kits) as a part of my preparation for exam 70-441
The book is really clear, and I could understand everything easily because I've some experience as a database developer.
Until now, there's only one thing that I didn't fully understand:
On pages 53 and 54, it mentions a type of hierarchy called: a graph hierarchy, and it gives a small example made the things even more complicated
I've many books about database design theories, but they don't use the term (graph hierarchy) any where...
So, I appreciate if you can point me to an online resource or a book that discusses this term thorougly.
Thank you
What are some strategies in categorizing items?
View 4 Replies View RelatedHello experts,
Last time I worked with SSAS and build a Cube. Because I€™m now very happy with the front-end excel 2003 or excel 2007 I thought I build my own Report with SSRS.
Now there is something I don€™t understand:
I build a Hierarchy in SSAS that a want to use in SSRS. Is there a chance to use it without any features? Have I to use parameters or something another?
In my opinion it makes any sense to build a hierarchy new, because it€™s already exists in SSAS.
Have a nice day
Alex
Hello ,
I'm facing a complicated problem and I don't think that the solution will be an easy one.
I have an SQL statement in Oracle which I need to translate it in Sql Server 2005.
select lpad(' ',5*(orderid)) || to_char(descr) as menui
from <table _name>
where MENU_ITEM not in ('test1','test2','test3') and item_parent not in ('test4,'test5,'test6')
start with <item_parent='item_parent' >
connect by prior <menu_item = item_parent and menu_name='something'; > ?(condition)
Somewhere I have read that SQL server does not support Hierarchical Sql statements. Is this true ? How am I going to do that ?
Any help will be appreciated.
Thank you
How can I get the data from a table that stores hierarchical information keeping the right order and hierarchy levels?
I also need to specify a first level....let me explain.
Table: Sites
Id (PK)
Title
Url
ParentId
If I have something like this:
1 | Test | http://localhost | NULL
2 | Sub-Test | http://localhost/subtest | 1
3 | SecondItem | http://anotherServer | NULL
4 | ChildOfSub-Test | http://localhost/subtest/child | 2
And I specify that I want all child items below the Id 2, it must return:
Id | Title | Url | ParentId | Depth
2 | Sub-Test | http://localhost/subtest | 1 | 0
4 | ChildOfSub-Test | http://localhost/subtest/child | 2 | 1
How can I do this? I've saw some examples with CTEs, but they always return all of the items.
Hi people
In have a query over two tables, and the ouptut is:
RowID | SubMarcaID | MarcaID | ProductoID
1 | 1 | 1 | 1
2 | 1 | 1 | 2
3 | 1 | 1 | NULL
4 | 3 | 1 | 3
5 | 3 | 1 | NULL
(SubMarcaID and MarcaID are the KEYS from SUBMARCA, and ProductoID is the key from PRODUCTO. Also SubMarcaID and MarcaID are foreing keys on PRODUCTO)
Now, I need a ParentRowID based on SubMarcaID and MarcaID, like a hierarchy
RowID | SubMarcaID | MarcaID | ProductoID | ParentRowID
1 | 1 | 1 | 1 | 3
2 | 1 | 1 | 2 | 3
3 | 1 | 1 | NULL | 0
4 | 3 | 1 | 3 | 5
5 | 3 | 1 | NULL | 0
How can I do this?
Code Block
SELECT ROW_NUMBER() OVER (ORDER BY PRODUCTO.MarcaID, PRODUCTO.SubMarcaID) AS RowID,
PRODUCTO.SubMarcaID,
PRODUCTO.MarcaID,
PRODUCTO.ProductoID
FROM PRODUCTO, SGP_SUBMARCA
WHERE PRODUCTO.SubMarcaID = SUBMARCA.SubMarcaID
AND PRODUCTO.MarcaID = SUBMARCA.MarcaID
GROUP
BY PRODUCTO.SubMarcaID,
PRODUCTO.MarcaID,
PRODUCTO.ProductoID
WITH
ROLLUP
HAVING (
GROUPING(PRODUCTO.ProductoID) = 1
AND GROUPING(PRODUCTO.SubMarcaID) = 0
AND GROUPING(PRODUCTO.MarcaID) = 0
)
OR (
GROUPING(PRODUCTO.SubMarcaID) = 0
AND GROUPING(PRODUCTO.MarcaID) = 0
AND GROUPING(PRODUCTO.ProductoID) = 0
)
I need to help in writing stored procedure to recursively delete Categories and ads for those categories
Simplified table views:
Category: CategoryID, Name, ParentCategoryID
Ads: AdID, Name, CategoryID
Please help
Hello Everyone,
I have a purchase order table that holds say 2 columns. PO and OrgPO. That is Purchase Order # and Original Purchase Order # respectively. Assume i have the following rows in the table.
PO OrgPO
-- ------
po1 NULL
co1 po1
co2 co1
co3 co2
po2 NULL
cpo1 po2
po3 NULL
Now what i would like to report in the output is the PO and along with the lastly generated change order for that po. For eg,
PO LastCO
-- ------
po1 co3
po2 cpo1
po3 po3
Currently i 'm using function to achieve this effect and i believe this is not the efficient way. I would like to generate this in a much efficient way. Please help me to achieve this.
Hi
I have problem is getting list of all the Tree Level Employees.here is the my table structure
Manager ~ SubOrdinate
1 ~ 2
1 ~ 3
1 ~ 4
2 ~ 5
2 ~ 6
2 ~ 7
3 ~ 8
3 ~ 9
4 ~ 10
5 ~ 11
SO ON
I NEED THE WAY , HOW I CAN GET THE HIRE
LEVELS ( EG IF I PASS MANAGER(EMPLOYEE NO) 1 IT SHOULD DISPLAY HIS 1 LEVELS AND 2, 3 SO ON LEVELS OF LEVELS
OUTPUT COULD BE ,
PASSED EMPLOYEE NO : 1
LEVEL ~ EMPLOYEE
1 ~ 2
1 ~ 3
1 ~ 4
2.1 ~ 5
2.1 ~ 6
2.1 ~ 6
3.1 ~ 7
SO ON THAT EMPLOYEE RELATED INFORMATION
( ITS LIKE MLM LEVELS)
CAN ANYBODY HELP IN THE ,
THANKS
is there any way to define a recursive cte within another cte. I have defined a recursive cte which works great for finding the different versions of a given item. What I would like to do is to define a set of items for which I want to find the different versions.
View 1 Replies View Related I have a table with a parent, child, and grandchild relationship. Can anyone help me with a query that will return the child and grandchild of a parent?
Heres my table:id pid name--------------------------------1 0 UntID2 0 Vin Number3 0 Make4 3 Model5 4 Model Number6 0 Model Year7 0 Vehicle Type8 0 Odometer MilesWhen I select 3 as the id I need these results:id pid name--------------------------------3 0 Make4 3 Model5 4 Model Number
Thanks for any help!
Ryan