InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 20:07:44 GMT20http://www.sqlservercentral.com/Forums/Topic1041609-392-1.aspx[quote][b]martin.edward (1/2/2011)[/b][hr]Hey, How can I provide the table creation and sample data scripts ?Regards,[/quote]Please read this article Martin : [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]Sun, 02 Jan 2011 17:03:26 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1041609-392-1.aspx[quote][b]martin.edward (1/2/2011)[/b][hr]Hey, How can I provide the table creation and sample data scripts ?Regards,[/quote]Just have a look at my previous post... ;-)Sun, 02 Jan 2011 09:43:38 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1041609-392-1.aspxHey, How can I provide the table creation and sample data scripts ?Regards,Sun, 02 Jan 2011 09:28:58 GMTmartin.edwardhttp://www.sqlservercentral.com/Forums/Topic1041609-392-1.aspxHere's the solution I came up with.I'm using the "quirky update" method with safety check as described [b][url=http://www.sqlservercentral.com/Forums/FindPost1022471.aspx]here[/url][/b]. Please follow the link mentioned in that post and read the related article by Jeff Moden (including the posts in the discussion). The quirky update is a powerful method but there are some rules to follow.Please note that the article is currently rewritten to reflect the latest improvementes.[code="sql"]CREATE TABLE #tbl ( ItemCode CHAR(4),Qty INT,TreeType CHAR(1),Linenum INT,run_total INT)INSERT INTO #tbl (itemcode,qty,treetype,linenum)VALUES('A100', 1,'S', 0),('A101', 2 , 'I', 1),('A102', 3 , 'I', 2),('B100', 3 , 'S', 3),('B101', 2 , 'I', 4),('B102', 2 , 'I', 5),('C100', 5, 'N', 6)CREATE CLUSTERED INDEX IX_#tbl_LinenumON #tbl (Linenum DESC);DECLARE @Sequence INT = 0, @runtotal INT = 0, @grpchange CHAR(1) = ' ' ;WITH safetycheck AS( SELECT itemcode, qty, treetype, run_total, SEQUENCE = ROW_NUMBER() OVER (ORDER BY linenum DESC) FROM #tbl)UPDATE t SET @Sequence = CASE WHEN SEQUENCE = @Sequence + 1 THEN @Sequence + 1 ELSE 1/0 END, @runtotal = run_total = CASE WHEN @grpchange = LEFT(ItemCode,1) THEN @runtotal + CASE WHEN treetype ='S' THEN 0 ELSE qty END ELSE qty END, @grpchange = LEFT(ItemCode,1) -- ANCHOR COLUMN FROM SafetyCheck t WITH (TABLOCKX)OPTION (MAXDOP 1);SELECT itemcode,run_total,treetype,linenum FROM #tblWHERE STUFF(itemcode,1,1,'')='100'ORDER BY linenum;[/code]Sun, 02 Jan 2011 04:48:49 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1041609-392-1.aspxHi Martin,One thing you should try to do is alter the design so that the "I" rows reference the related "S" row - that would make this much easier.Before anyone can help you properly here though, you should provide table creation and sample data scripts in order that the volunteers on the site can provide meaningful advice.I assume you have simplified the table design for this question because it makes no reference to a master record key such as an order number or an assembly number, and while that may seem to make it simpler it prevents anyone from providing a good solution as that master data will most likely be needed.Thanks.Sun, 02 Jan 2011 03:38:35 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1041609-392-1.aspxDear Experts, I have data from a single table without an "obvious" relationship but whose data I need to group. I would like to create a group using formulas in SQL (or any other) if possible that will allow me to create some grouping for the data.The data is as follows :[quote] ItemCode Qty TreeType Linenum A100 1 S 0 A101 2 I 1 A102 3 I 2 B100 3 S 3 B101 2 I 4 B102 2 I 5 C100 5 N 6 [/quote] I would like to group the data as follows :[quote] ItemCode Qty TreeType Linenum A100 5 S 0 B100 4 S 3 C100 5 N 6 [/quote]One thing for sure is that after every 'S' treetype there is an 'I' and the item code with the 'S' is the parent item.Hence for items that come immediately under tree type 'S' are summed up and the quantity is made to the total group quantity. The Quantity of the tree type 'S' is not added up but ignored. Where the tree type is N, the data is left as it is.How can I create a group that runs such that after every 'S' tree type all the subsequent 'I' tree types are grouped together ?Sun, 02 Jan 2011 02:30:43 GMTmartin.edward