Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating a Group on Data


Creating a Group on Data

Author
Message
martin.edward
martin.edward
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 179
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 ?
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2266 Visits: 7824
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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • LutzM
    LutzM
    SSCertifiable
    SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

    Group: General Forum Members
    Points: 7001 Visits: 13559
    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;





    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question
    How to post performance related questions
    Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
    martin.edward
    martin.edward
    Valued Member
    Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

    Group: General Forum Members
    Points: 69 Visits: 179
    Hey,

    How can I provide the table creation and sample data scripts ?

    Regards,
    LutzM
    LutzM
    SSCertifiable
    SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

    Group: General Forum Members
    Points: 7001 Visits: 13559
    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... ;-)



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question
    How to post performance related questions
    Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
    mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2266 Visits: 7824
    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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search