[T-SQL] Traversing a hierarchy |
| Posted: 03 Aug 2013 07:54 PM PDT I have a table representing parent-child relationship and NoOfBooks for that relationship. Each child can then have its own children and so on. Each record has a value for NoOfBooks ranging from 0 to 100.What i want is for a parent (we can start with any parent), identify all relationships of the parent where sum of NoOfBooks for a child is greater than a threshold value (let's say 9). But the catch is, sum of NoOfBooks for a child's [b]each[/b] parent should also be greater that the threshold value.So e.g. A-B 9A-C 10A-D 5B-E 5C-E 6E-F 12E-G 4D-H 14My Output should beA-B 9A-C 10B-E 5C-E 6E-F 12Here is the table with sample data.[code="sql"]Create table MyTable (Id INT IDENTITY(1,1) Primary Key,ParentId INT,ChildId INT,NoOfBooks INT);INSERT INTO MyTable VALUES (1000, 2000, 10);INSERT INTO MyTable VALUES (1000, 2001, 9);INSERT INTO MyTable VALUES (1000, 2002, 8);INSERT INTO MyTable VALUES (2000, 2003, 4);INSERT INTO MyTable VALUES (2001, 2003, 5);INSERT INTO MyTable VALUES (2003, 2004, 9);INSERT INTO MyTable VALUES (2000, 2005, 3);INSERT INTO MyTable VALUES (2001, 2005, 3);INSERT INTO MyTable VALUES (2004, 2005, 3);INSERT INTO MyTable VALUES (2005, 2006, 5);INSERT INTO MyTable VALUES (2002, 2007, 12);INSERT INTO MyTable VALUES (2005, 2008, 5);INSERT INTO MyTable VALUES (2002, 2008, 6);[/code][img][/img]Required output of above sample data (see attachment)[img]http://www.sqlservercentral.com/Forums/Attachment14122.aspx[/img]Note that [1000, 2002, 8], [2005, 2006, 5], [2002, 2007, 12], [2005, 2008, 5], [2002, 2008, 6] are not included in my required output because either SUM(NoOfBooks) for the child is NOT greater than the threshold value OR SUM(NoOfBooks) for each of its parent is NOT greater than the threshold value. |
| Add one MilliSecond to each row Posted: 26 Jul 2013 03:06 AM PDT Hello EveryoneI am trying something, that I am not sure is possible.I have a CreateDate column in a table, there are approx 5000 rows with the same date and time. This was done from the data load. I need to change the the time by adding one millisecond to each one, but incrementing by one from the previous row.So if I have a datetime like this:[code="sql"]RowID, CreateDate1, 2013-07-30 08:20:40:0102, 2013-07-30 08:20:40:0103, 2013-07-30 08:20:40:0104, 2013-07-30 08:20:40:0105, 2013-07-30 08:20:40:010[/code]I would like to add one millisecond to each, but incrementing from the previous row.[code="sql"]RowID, CreateDate1, 2013-07-30 08:20:40:0102, 2013-07-30 08:20:40:0113, 2013-07-30 08:20:40:0124, 2013-07-30 08:20:40:0135, 2013-07-30 08:20:40:014[/code]Here is some dummy code.[code="sql"]; WITH DifferentTimes( RowID, CreateDate)AS(SELECT TOP (500) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id])), '2013-07-30 08:20:40:010'FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2)SELECT * FROM DifferentTimes[/code]So after that, I am not sure how I can perform an update to increment the time by each row, using the previous row.Thank You in advance for all your assistance, suggestions and commentsAndrew SQLDBA |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment