February 6, 2010 at 1:28 pm
I'm almost there.
I just can't figure out why ID18 will point to ID17 instead of ID14, even if leafaccntnum=HRS0010001 shows up in row 14 the first time...
Would you please clarify why you point to the very first occurence for level 2 elements, but not for level three?
13 HRS001 HR Staffing North America 2 V null 3
14 HRS0010001 HR Staffing North America 2 V 7 3
15 HRS00100010000 HR Staffing North America 2 V 14 3
16 HRS001 HR Staffing US 3 V null 3
17 HRS0010001 HR Staffing US 3 V 7 3
18 HRS00100010001 HR Staffing US 3 V 17 3
February 6, 2010 at 1:56 pm
The_SQL_DBA (2/6/2010)
Did you find a working solution yet?
Well, I guess we are pretty much there with the solution. As lmu92's solution works good, and just needs to take care of the parentId...thanks though..
February 6, 2010 at 2:00 pm
lmu92 (2/6/2010)
I'm almost there.I just can't figure out why ID18 will point to ID17 instead of ID14, even if leafaccntnum=HRS0010001 shows up in row 14 the first time...
Would you please clarify why you point to the very first occurence for level 2 elements, but not for level three?
13 HRS001 HR Staffing North America 2 V null 3
14 HRS0010001 HR Staffing North America 2 V 7 3
15 HRS00100010000 HR Staffing North America 2 V 14 3
16 HRS001 HR Staffing US 3 V null 3
17 HRS0010001 HR Staffing US 3 V 7 3
18 HRS00100010001 HR Staffing US 3 V 17 3
Thanks for your efforts.
The reason for that is, every 6 digit account has many 10 digits account under it with parent Id = id of the unique 6 digit number. Similarly every unique 10 digit number will have many 14 digits numbers under it with parentID = id of the 10 digit number, that way it creates a hierarchic structure and we can select all the leaf account numbers with a specif parent.
I hope my test data presents that logic, if not this is what I meant to represent in the data. Let me know if I can make myself more clear...Thanks!!
February 6, 2010 at 2:12 pm
So, let me ask slightly different:
In the previous post we have
13 HRS001 HR Staffing North America 2 V null 3
14 HRS0010001 HR Staffing North America 2 V 7 3
15 HRS00100010000 HR Staffing North America 2 V 14 3
16 HRS001 HR Staffing US 3 V null 3
17 HRS0010001 HR Staffing US 3 V 7 3
18 HRS00100010001 HR Staffing US 3 V 17 3ID 13 and 16 have no parents. So both set to NULL. Fine.
ID 14 and 17 both have parents with leafaccntnum=HRS001, so pointing to ID7.
ID 15 has a parent with leafaccntnum=HRS0010001, so pointing to ID14. Good enough.
Why is ID 18 pointing to ID 17, since the first occurance of leafaccntnum=HRS0010001 should be ID14?
February 6, 2010 at 3:24 pm
lmu92 (2/6/2010)
So, let me ask slightly different:In the previous post we have
13 HRS001 HR Staffing North America 2 V null 3
14 HRS0010001 HR Staffing North America 2 V 7 3
15 HRS00100010000 HR Staffing North America 2 V 14 3
16 HRS001 HR Staffing US 3 V null 3
17 HRS0010001 HR Staffing US 3 V 7 3
18 HRS00100010001 HR Staffing US 3 V 17 3
ID 13 and 16 have no parents. So both set to NULL. Fine.
ID 14 and 17 both have parents with leafaccntnum=HRS001, so pointing to ID7.
ID 15 has a parent with leafaccntnum=HRS0010001, so pointing to ID14. Good enough.
Why is ID 18 pointing to ID 17, since the first occurance of leafaccntnum=HRS0010001 should be ID14?
Yes, you are right. It should be 14. It makes more sense to me now. Thanks.
February 6, 2010 at 3:43 pm
Ok, here's what I came up with:
;WITH Repeats AS
(
SELECT 1 AS parent, 6 AS Cnt UNION ALL
SELECT 2,10 AS Cnt UNION ALL
SELECT 3,14 AS Cnt
),
cte AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY CompanyName,parent ) AS ROW,
LEFT(S.Accountnumber, R.Cnt) AS leafaccntnum,
LEFT(S.Accountnumber, R.Cnt2) AS leafaccntnum2,
S.CompanyName,
CASE
WHEN RIGHT(S.Accountnumber, 8) = '00000000' THEN 1
WHEN RIGHT(s.Accountnumber, 4) = '0000' THEN 2
ELSE 3
END AS nodeDepth,
S.accountType,
parent AS parentid,
l.id AS LabelID,
0 AS currlevel
FROM Company S
INNER JOIN label l ON s.logo=l.code
CROSS JOIN
(
SELECT r1.parent, r1.cnt, ISNULL(r2.cnt,0) AS cnt2
FROM Repeats r1
LEFT OUTER JOIN Repeats r2
ON r1.parent=r2.parent+1
)R
),
cte2 AS
(
SELECT leafaccntnum,MIN(ROW) AS minrow FROM cte
GROUP BY leafaccntnum
)
SELECT
cte.leafaccntnum,
companyname,
nodeDepth,
accountType,
row as Id,
CASE WHEN parentid=1 THEN null ELSE minrow END AS parentid,
CASE WHEN parentid=1 THEN 0 ELSE minrow -row END AS IdOffset,
LabelId,
currlevel
FROM cte
LEFT OUTER JOIN cte2 ON cte2.leafaccntnum=cte.leafaccntnum2
ORDER BY CompanyName,ROW
/* result set
leafaccntnumcompanynamenodeDepthaccountTypeIdparentidIdOffsetLabelIdcurrlevel
ABC000ABC Shipping Company1A1NULL020
ABC0000000ABC Shipping Company1A21-120
ABC00000000000ABC Shipping Company1A32-120
GMILABGENERAL Merchandise Inc3V4NULL010
GMILAB1032GENERAL Merchandise Inc3V54-110
GMILAB10320164GENERAL Merchandise Inc3V65-110
HRS001HR Staffing Company1V7NULL030
HRS0010000HR Staffing Company1V87-130
HRS00100000000HR Staffing Company1V98-130
HRS001HR Staffing Europe2A10NULL030
HRS0010002HR Staffing Europe2A117-430
HRS00100020000HR Staffing Europe2A1211-130
HRS001HR Staffing North America2V13NULL030
HRS0010001HR Staffing North America2V147-730
HRS00100010000HR Staffing North America2V1514-130
HRS001HR Staffing US3V16NULL030
HRS0010001HR Staffing US3V177-1030
HRS00100010001HR Staffing US3V1814-430
*/
I added three columns: Id (starting from 1), parentId as per your requirement and IdOffset as the difference between the two.
If you insert the data into a table you should add another calculated column with Id+IdOffset to get your correct referenceId.
February 6, 2010 at 4:07 pm
This solution is perfect as it takes care of the ParentId.
So, when I write the insert statement, I would need to add the two columns Id+Idoffest as the values that would be inserted to ParentId and that it can be done during run time itself.
Thank you very much :-). Now let me start with the insert statements. I will make sure to update you on this and post any issues that I might face.
February 6, 2010 at 6:49 pm
How would my insert statement look like if I need to insert a new record and relate to its parent 6 digit number
ABC00000000001 ABC Shipping Inc 3 B
February 7, 2010 at 2:27 am
T-SQL_NewBie (2/6/2010)
How would my insert statement look like if I need to insert a new record and relate to its parent 6 digit number
ABC00000000001 ABC Shipping Inc 3 B
The concept only works if the data that will be inserted will include all required information. It is not set up to get values from any rows already inserted. I mentioned it in one of the very first posts. Unfortunately, this requirement has not been mentioned before.:(
I recommend to do it in two steps: Insert your new data (ignoring the parent id) and update that id in a second query. That way you have all Id values and you don't have to struggle with failed inserts for the parentid calculation. Edit: But, of course, handling of failed insert in general is mandatory.
Based on the code samples you have so far I'm sure you'll get it working and fine tuned.
February 7, 2010 at 8:49 am
I am going to use this script for the first insert and then any new inserts will have an update statement for the ParentId.
In that case:
1. What would my update statement look like?
2. Do I need to add the Idoffset column and one more Id column to the Accounts table?
Thanks again!!
February 7, 2010 at 11:40 am
I would not use two different strategies.
What I would recommend to do:
Take the insert without any value for parentid. Set this column to a default value that will identify parentids not set yet (e.g. -1, NULL or 0 if you still want to use your NULL values).
Use a cte to select those values including a column with left(leafaccntnum,len(leafaccntnum)-4), select the min(id's) for those shortened leafaccntnum and update your accounts table.
I described it only verbally since I think you should try it yourself rather than copying the code someone else came up with. If you have any trouble let us know (including the code you tried and where you get stuck).
February 7, 2010 at 11:54 am
OK. At least the big part of my problem of getting the parentId has been solved. I will include your recommendations while scripting the new inserts so as to get the parentID dynamically for any new inserts.
Thanks again!!
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply