Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating a Group on Data Expand / Collapse
Author
Message
Posted Sunday, January 02, 2011 2:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 11, 2011 12:49 AM
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 ?
Post #1041609
Posted Sunday, January 02, 2011 3:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 1,654, Visits: 5,208
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1041611
    Posted Sunday, January 02, 2011 4:48 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 4:06 AM
    Points: 6,932, Visits: 12,657
    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
    Post #1041615
    Posted Sunday, January 02, 2011 9:28 AM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Monday, April 11, 2011 12:49 AM
    Points: 69, Visits: 179

    Hey,

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

    Regards,
    Post #1041630
    Posted Sunday, January 02, 2011 9:43 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 4:06 AM
    Points: 6,932, Visits: 12,657
    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
    Post #1041631
    Posted Sunday, January 02, 2011 5:03 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 3:56 PM
    Points: 1,654, Visits: 5,208
    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1041677
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse