Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2012
    Posts
    12

    Unanswered: Grouping on Data Change

    I am trying to create a group on this data example

    RecordNo, Item, Date,NULL
    1, ABBB, NULL, 0 << first group
    2, ABBB, 01-06-2011,NULL << first group
    3, ABBB,NULL,NULL << 2nd group
    4, ABBB, 02-01-2011,NULL << 2nd group
    5,CAAA,NULL,NULL << 1st group
    6,CAAA,NULL,NULL << 1st group
    7,CAAA,01-01-2010,NULL << 1st group
    8,CAAA,01-01-2011,NULL << 2nd group
    9,CAAA,01-05-2012, NULL << 3rd group

    Basically 3 groups on date, by item, so for each date change/ per part the group increments

    been playing with row_number partitions, but cant seem to get what i need.

    any ideas, thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Why did you decide that the first batch of rows belong to one group and the other batch to another group?
    Code:
    1, ABBB, NULL, 0 << first group
    2, ABBB, 01-06-2011,NULL << first group
    5,CAAA,NULL,NULL << 1st group
    6,CAAA,NULL,NULL << 1st group
    7,CAAA,01-01-2010,NULL << 1st group
    
    3, ABBB,NULL,NULL << 2nd group
    4, ABBB, 02-01-2011,NULL << 2nd group
    8,CAAA,01-01-2011,NULL << 2nd group
    Why do
    7,CAAA,01-01-2010,NULL
    8,CAAA,01-01-2011,NULL
    belong to different groups?

    Once you can define that, the rest will be easy.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2012
    Posts
    12

    Dates

    basically each group consists of every row up to and including a date.
    The next row would be in the next group, until the next date and so on..

    their are sort fields that allow me to ensure that the rows are grouped together by item, recno and date

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two questions.

    (1) Why value of 4th column was 0 only for RecordNo=1, and NULL for all others.
    Is it influence to the results?

    (2) Are Date(s) in each Item increased by RecordNo?

  5. #5
    Join Date
    Feb 2012
    Posts
    12

    sample data

    The samples were just something I made up and apparently rather poorly.
    Rows have a unique record no, a part/item number a date and what i need to be a group id (currently empty in the data )

    The date is an "Item Purchase Date", the day stock will be reloaded. I need (for each item) a group, so for example, one part may not have any dates, while others can have up to 3, depending on how popular the part is.

    so if row 1 row for a part has a null date, and row 2 has a date both row 1 and row 2 are part of group 1, and subsequent row(s) are members of group 2 until the next date, and so on up to group 3.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm sorry. I have no Microsoft SQL Server.

    So, I tested the example on Mimer SQL Developers - Mimer SQL-2003 Validator

    Example 1:
    Code:
    SELECT RecordNo
         , Item
         , PurchaseDate
         , DENSE_RANK()
              OVER( PARTITION BY Item
                        ORDER BY MIN(PurchaseDate)
                                    OVER( PARTITION BY Item
                                              ORDER BY RecordNo
                                          ROWS BETWEEN CURRENT ROW
                                                   AND UNBOUNDED FOLLOWING
                                        )
                  ) AS groupNo
     FROM  ItemPurchased
     ORDER BY
           RecordNo
    ;
    Code:
    Result:
    
    The following features outside Core SQL-2003 are used:
    
    T611, "Elementary OLAP operations"
    T612, "Advanced OLAP operations"

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    Two questions.

    (1) Why value of 4th column was 0 only for RecordNo=1, and NULL for all others.
    Is it influence to the results?

    (2) Are Date(s) in each Item increased by RecordNo?
    I assumed the answer to question (2) was 'Yes', if ignored null values.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Like this???
    Code:
    DROP TABLE #foo
    GO
    
    CREATE TABLE #foo (
       RecordNo INT             NOT NULL
    ,  Item	    NVARCHAR(99)    NOT NULL
    ,  [Date]   DATETIME            NULL
       PRIMARY KEY CLUSTERED (RecordNo)
       )
    
    INSERT INTO #foo (
       RecordNo, Item, [Date]
    )  SELECT           1, 'ABBB', NULL         -- << first group
       UNION ALL SELECT 2, 'ABBB', '01-06-2011' -- << first group
       UNION ALL SELECT 3, 'ABBB', NULL         -- << 2nd group
       UNION ALL SELECT 4, 'ABBB', '02-01-2011' -- << 2nd group
       UNION ALL SELECT 5, 'CAAA', NULL         -- << 1st group
       UNION ALL SELECT 6, 'CAAA', NULL         -- << 1st group
       UNION ALL SELECT 7, 'CAAA', '01-01-2010' -- << 1st group
       UNION ALL SELECT 8, 'CAAA', '01-01-2011' -- << 2nd group
       UNION ALL SELECT 9, 'CAAA', '01-05-2012' -- << 3rd group
    
    SELECT *
    ,  (SELECT 1 + COUNT(b.[date])
          FROM #foo AS b
          WHERE  b.Item = a.Item
             AND b.RecordNo < a.RecordNo) AS 'Group'
       FROM #foo AS a
       ORDER BY a.RecordNo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Ah!
    It is easy by using COUNT([date]).

    Example 2:
    Code:
    SELECT a.*
         , COUNT([Date])
              OVER( PARTITION BY Item
                        ORDER BY RecordNo
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND 1         PRECEDING
                  ) + 1 AS 'Group'
     FROM  #foo AS a
     ORDER BY
           RecordNo
    ;

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is over partition over used?

    I swear the kids won't even know what real sql looks like anymore

    What a crutch
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The OVER PARTION clause is a great tool, but in this case it seems overly complicated to me. A simple subquery works on most SQL engines, is relatively easy to understand, and does this job nicely.

    Know your tools, use their features, but in this case I agree with Bret.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 1 was unnecessarily complicated.
    But, it worked on DB2, like

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      ItemPurchased(RecordNo , Item , PurchaseDate) AS (
    VALUES
      ( 1 , 'ABBB' , NULLIF('','') )
    , ( 2 , 'ABBB' , '01-06-2011'  )
    , ( 3 , 'ABBB' , NULLIF('','') )
    , ( 4 , 'ABBB' , '02-01-2011'  )
    , ( 5 , 'CAAA' , NULLIF('','') )
    , ( 6 , 'CAAA' , NULLIF('','') )
    , ( 7 , 'CAAA' , '01-01-2010'  )
    , ( 8 , 'CAAA' , '01-01-2011'  )
    , ( 9 , 'CAAA' , '01-05-2012'  )
    )
    SELECT RecordNo
         , Item
         , PurchaseDate
         , DENSE_RANK()
              OVER( PARTITION BY Item
                        ORDER BY MIN(PurchaseDate)
                                    OVER( PARTITION BY Item
                                              ORDER BY RecordNo
                                          ROWS BETWEEN CURRENT ROW
                                                   AND UNBOUNDED FOLLOWING
                                        )
                  ) AS groupNo
     FROM  ItemPurchased
     ORDER BY
           RecordNo
    ;
    ------------------------------------------------------------------------------
    
    RECORDNO    ITEM PURCHASEDATE GROUPNO             
    ----------- ---- ------------ --------------------
              1 ABBB -                               1
              2 ABBB 01-06-2011                      1
              3 ABBB -                               2
              4 ABBB 02-01-2011                      2
              5 CAAA -                               1
              6 CAAA -                               1
              7 CAAA 01-01-2010                      1
              8 CAAA 01-01-2011                      2
              9 CAAA 01-05-2012                      3
    
      9 record(s) selected.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 2 refers the table once.
    So, it might work more efficiently than using a subquery.

  14. #14
    Join Date
    Feb 2012
    Posts
    12
    While I see that the results are exactly what I want in Example 2, I am using MS SQL server and the
    ORDER BY RecordNo
    ROWS BETWEEN CURRENT ROW
    AND UNBOUNDED FOLLOWING

    fail the syntax check, and as such are nut supported.

    Am I doing something wrong?

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ORDER BY RecordNo
    ROWS BETWEEN CURRENT ROW
    AND UNBOUNDED FOLLOWING

    fail the syntax check, and as such are nut supported.
    It was used in Example 1
    And I wrote
    Example 1 was unnecessarily complicated.
    So, it may be better to throw out Example 1.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •