August 22, 2008 at 2:04 pm
I have data that with 1 to n levels of parent/child relationships.
For example:
1) AD-Admins
2) AD-ADMINS, user_Tom
3) AD-Admins, SQL_Admins, user_Bill
Given that;
line1 there is no user - this is legitimate
line2 Owner/Authorizer=AD-Admins and Account is user_Tom
line3 Owner/Authorizer=SQL-Admins and Account is user_Bill
The current program wrongly chooses the top-level AD-Admin instead of the nearest superior node.
The nesting can go up to 7 levels deep before the users are identified.
The first element is the highlest level, ultimately working down to the user.
How can I associate the Closest superior node to the user (user node -1)?
Thanks much,
August 22, 2008 at 3:35 pm
can you provide table structure? if possible, provide some sample data.
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
August 25, 2008 at 9:09 am
Thank you for your time - here is more sample data
5665312Administratoramr\xx_xxxxxxxxx_xxxxxDDDDDDDDDNULLamr\sys_eamsqlACCOUNTNULLNULLNULLNULLNULLNULLNULL
5665412Administratoramr\ad_xxx xxxx xxx xxAAAAAA\MMMMMMMCreated and managed by AAAAAAamr\AD_XXX XXXX XXX XX_1AAAAAA\MMMMMMMCreated and managed by AAAAAAamr\ad_bsbsbsbACCOUNTCreated and managed by AAAAAANULLNULLNULL
5665512Administratoramr\ad_xxx xxxx xxx xxAAAAAA\MMMMMMMCreated and managed by AAAAAAamr\AD_XXX XXXX XXX XX_1AAAAAA\MMMMMMMCreated and managed by AAAAAAamr\ad_fgfdslh ACCOUNTCreated and managed by AAAAAANULLNULLNULL
In the schema hat follows note the flattened approach with 3 fields per level starting with Parent (parnt) down thru lvl7 :
data_load_id1
evdn_type_cd2
lcl_grp_nm3
parnt_acct_nm4
parnt_acct_type_nm5
parnt_grp_acct_ownr_dsc6
lvl1_chld_acct_nm7
lvl_acct_type_nm8
lvl1_grp_acct_ownr_dsc9
lvl2_chld_acct_nm10
lvl2_acct_type_nm11
lvl2_grp_acct_ownr_dsc12
lvl3_chld_acct_nm13
lvl3_acct_type_nm14
lvl3_grp_acct_ownr_dsc15
lvl4_chld_acct_nm16
lvl4_acct_type_nm17
lvl4_grp_acct_ownr_dsc18
lvl5_chld_acct_nm19
lvl5_acct_type_nm20
lvl5_grp_acct_ownr_dsc21
lvl6_chld_Acct_nm22
lvl6_acct_type_nm23
lvl6_grp_acct_ownr_dsc24
lvl7_chld_acct_nm25
lvl7_acct_type_nm26
lvl7_grp_acct_ownr_dsc27
grnt_dtm28
expr_dtm29
aprvr1_idsid30
aprvr2_idsid31
aprvr3_idsid32
cmnt_txt33
sts_ind34
cre_agnt_id35
cre_dtm36
------------------------
Regardless of the final node populated, teh preceding node (node -1) contains the data that is really needed.
Thank you
August 25, 2008 at 6:24 pm
Please see this link for etiquette on providing data and table structures: http://www.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 12:29 am
Hi there,
Sorry Tom but I agree with rbarryyoung, please rewrite youre example.
But I didn't post this message just to say that.
Actually I know a design to give tables heirarchy but, sorry, I can't give the design, company rules.
but theres another way.
Why not record your data in XML.
you can record XML data in tables by creating a field with an XML data type
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply