January 2, 2011 at 2:30 am
Dear 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 :
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
I would like to group the data as follows :
ItemCode Qty TreeType Linenum
A100 5 S 0
B100 4 S 3
C100 5 N 6
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 ?
January 2, 2011 at 3:38 am
Hi 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.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 2, 2011 at 4:48 am
Here's the solution I came up with.
I'm using the "quirky update" method with safety check as described here. 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.
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_Linenum
ON #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 #tbl
WHERE STUFF(itemcode,1,1,'')='100'
ORDER BY linenum;
January 2, 2011 at 9:28 am
Hey,
How can I provide the table creation and sample data scripts ?
Regards,
January 2, 2011 at 9:43 am
martin.edward (1/2/2011)
Hey,How can I provide the table creation and sample data scripts ?
Regards,
Just have a look at my previous post... 😉
January 2, 2011 at 5:03 pm
martin.edward (1/2/2011)
Hey,How can I provide the table creation and sample data scripts ?
Regards,
Please read this article Martin : http://www.sqlservercentral.com/articles/Best+Practices/61537/
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply