Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2013
    Posts
    8

    Red face Unanswered: Grouping consecutive date records

    Hello-
    I've searched and searched and can't find a solution I can use. I'm VERY new at this, hoping you can help. I have a table with the following columns:
    AssociateID
    Store
    StartDt
    EndDt
    Sales

    Any associate can work at any store, so each associate can have multiple records depending on consecutive dates and which stores they are at.

    That said, I need to group by AssociateID, Store, and then consecutive start/end dates- where a start date is the day after an end date, they need to be grouped.
    Then I also need to sum sales.
    AssociateID is mediumtext
    Store is mediumtext
    STartDt and EndDt are DateTime
    and Sales is Integer

    Example Data (Table is MUCH larger than this)
    AsspciateID Store StartDate EndDate Sales
    123456 1 1/1/2011 1/5/2011 50
    123456 1 1/6/2011 1/10/2011 100
    123456 2 1/1/2011 1/1/2011 10
    154655 1 1/6/2011 1/9/2011 15
    154655 1 1/10/2011 1/15/2011 35
    154655 3 3/30/2011 4/3/2011 200
    154655 3 4/15/2011 4/20/2011 45

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Based on your sample data, what is your required output?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    AssociateID is mediumtext
    Store is mediumtext
    STartDt and EndDt are DateTime
    and Sales is Integer
    One of my question was why did you used mediumtext datatype?
    It requires extra 3 bytes prefix to hold length.
    MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

    A TEXT column with a maximum length of 16,777,215 (224 1) characters.
    The effective maximum length is less if the value contains multi-byte characters.
    Each MEDIUMTEXT value is stored using a 3-byte length prefix that indicates the number of bytes in the value.
    I thought that using VARCHAR(use extra 1 byte for length, if length was less than 255) or CHAR might be better.
    [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

    ...
    ...

    MySQL stores VARCHAR values as a 1-byte or 2-byte length prefix plus data.
    The length prefix indicates the number of bytes in the value. A VARCHAR column uses one length byte if values require no more than 255 bytes,
    two length bytes if values may require more than 255 bytes.
    MySQL :: MySQL 5.6 Reference Manual :: 11.1.3 String Type Overview

  4. #4
    Join Date
    Dec 2013
    Posts
    8
    My desired result is:

    AsspciateID Store StartDate EndDate Sales
    123456 1 1/1/2011 1/10/2011 150
    123456 2 1/1/2011 1/1/2011 10
    154655 1 1/6/2011 1/15/2011 50
    154655 3 3/30/2011 4/3/2011 200
    154655 3 4/15/2011 4/20/2011 45

    I actually switched all the text fields to varchar.
    thank you!

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I got the results on DB2 by the following query.
    Though, you might want to make some amendment for MySQL.

    The data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     table_a
    ( associate_ID , Store , Start_Date , End_Date , Sales ) AS (
    VALUES
      ( '123456' , '1' , DATE('1/1/2011') , DATE('1/5/2011') , 50 )
    , ( '123456' , '1' , '1/06/2011' , '1/10/2011' , 100 )
    , ( '123456' , '2' , '1/01/2011' , '1/01/2011' ,  10 )
    , ( '154655' , '1' , '1/06/2011' , '1/09/2011' ,  15 )
    , ( '154655' , '1' , '1/10/2011' , '1/15/2011' ,  35 )
    , ( '154655' , '3' , '3/30/2011' , '4/03/2011' , 200 )
    , ( '154655' , '3' , '4/15/2011' , '4/20/2011' ,  45 )
    )
    SELECT * FROM table_a;
    ------------------------------------------------------------------------------
    
    ASSOCIATE_ID STORE START_DATE END_DATE   SALES      
    ------------ ----- ---------- ---------- -----------
    123456       1     2011-01-01 2011-01-05          50
    123456       1     2011-01-06 2011-01-10         100
    123456       2     2011-01-01 2011-01-01          10
    154655       1     2011-01-06 2011-01-09          15
    154655       1     2011-01-10 2011-01-15          35
    154655       3     2011-03-30 2011-04-03         200
    154655       3     2011-04-15 2011-04-20          45
    
      7 record(s) selected.

    Query:
    Code:
    SELECT associate_ID , Store
         , Start_Date   , end_date
         , (SELECT SUM(r2.sales)  AS sum_sales
             FROM  table_a AS r2
             WHERE r2.associate_ID = r1.associate_ID
               AND r2.store        = r1.store
               AND r2.start_date  >= r1.start_date
               AND r2.end_date    <= r1.end_date
           )
     FROM  (SELECT associate_ID , Store
                 , Start_Date
                 , (SELECT MIN(t3.end_date) AS end_date
                     FROM  table_a AS t3
                     WHERE NOT EXISTS
                           (SELECT *
                             FROM  table_a AS t4
                             WHERE t4.associate_ID = t3.associate_ID
                               AND t4.store        = t3.store
                               AND t4.start_date   = t3.end_date + 1 Day
                           )
                       AND t3.associate_ID = t1.associate_ID
                       AND t3.store        = t1.store
                       AND t3.end_date    >= t1.start_date
                   )
             FROM  table_a AS t1
             WHERE NOT EXISTS
                   (SELECT *
                     FROM  table_a AS t2
                     WHERE t2.associate_ID = t1.associate_ID
                       AND t2.store        = t1.store
                       AND t2.end_date     = t1.start_date - 1 Day
                   )
           ) AS r1
     ORDER BY
           associate_ID , Store
         , Start_Date
    ;

    Results:
    Code:
    ------------------------------------------------------------------------------
    
    ASSOCIATE_ID STORE START_DATE END_DATE   SUM_SALES  
    ------------ ----- ---------- ---------- -----------
    123456       1     2011-01-01 2011-01-10         150
    123456       2     2011-01-01 2011-01-01          10
    154655       1     2011-01-06 2011-01-15          50
    154655       3     2011-03-30 2011-04-03         200
    154655       3     2011-04-15 2011-04-20          45
    
      5 record(s) selected.

  6. #6
    Join Date
    Dec 2013
    Posts
    8
    Thank you!!!

  7. #7
    Join Date
    Dec 2013
    Posts
    8
    I pasted your sql code exactly, just changed around some field names as they are evolving, and am now getting an error that says it cannot find end_date in the field list.

    Code used is below. I've gone over it time and time again to see what I'm missing- if something isn't defined, but it is exactly as you wrote it, except for field name changes (and I had to remove the "Day" from the 1 day parts, but upon research online, it shouldn't make a difference for my purposes).

    I know this seems too easy for some of you- but I'm REALLY new to SQL and really really appreciate the help.
    thank you.

    SELECT Staff , Store
    , Start_Date ,End_Date,
    (SELECT SUM(r2.Sales) AS sum_Sales
    FROM group_test AS r2
    WHERE r2.Staff = r1.Staff
    AND r2.Store = r1.Store
    AND r2.Start_Date >= r1.Start_Date
    AND r2.End_Date <= r1.End_Date
    )
    FROM (SELECT Staff , Store
    , Start_Date
    , (SELECT MIN(t3.End_Date) AS End_Date
    FROM group_test AS t3
    WHERE NOT EXISTS
    (SELECT *
    FROM group_test AS t4
    WHERE t4.Staff = t3.Staff
    AND t4.Store = t3.Store
    AND t4.Start_Date = t3.End_Date + 1
    )
    AND t3.Staff = t1.Staff
    AND t3.Store = t1.Store
    AND t3.End_Date >= t1.Start_Date
    )
    FROM group_test AS t1
    WHERE NOT EXISTS
    (SELECT *
    FROM group_test AS t2
    WHERE t2.Staff = t1.Staff
    AND t2.Store = t1.Store
    AND t2.End_Date = t1.Start_Date - 1
    )
    ) AS r1
    ORDER BY
    Staff , Store
    , Start_Date

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I don't know much about MySQL.
    So, I'm not sure the following query example might solve your problem.

    Anyway,
    Please try by moving "AS End_Date" (and "AS sum_Sales").
    (By referencing manual, I thought that INTERVAL keyword might be used rather than removing "Day".)

    Code:
    SELECT Staff , Store
         , Start_Date , End_Date
         , (SELECT SUM(r2.Sales)
             FROM  group_test AS r2
             WHERE r2.Staff = r1.Staff
               AND r2.Store = r1.Store
               AND r2.Start_Date >= r1.Start_Date
               AND r2.End_Date   <= r1.End_Date
           ) AS sum_Sales
     FROM  (SELECT Staff , Store
                 , Start_Date
                 , (SELECT MIN(t3.End_Date)
                     FROM  group_test AS t3
                     WHERE NOT EXISTS
                           (SELECT *
                             FROM  group_test AS t4
                             WHERE t4.Staff = t3.Staff
                               AND t4.Store = t3.Store
                               AND t4.Start_Date = t3.End_Date + INTERVAL 1 Day 
                           )
                       AND t3.Staff = t1.Staff
                       AND t3.Store = t1.Store
                       AND t3.End_Date >= t1.Start_Date
                   ) AS End_Date
             FROM  group_test AS t1
             WHERE NOT EXISTS
                   (SELECT *
                     FROM  group_test AS t2
                     WHERE t2.Staff = t1.Staff
                       AND t2.Store = t1.Store
                       AND t2.End_Date = t1.Start_Date - INTERVAL 1 Day 
                   )
           ) AS r1
     ORDER BY
           Staff , Store
         , Start_Date 
    ;

  9. #9
    Join Date
    Dec 2013
    Posts
    8
    PERFECT- THANK YOU SO MUCH! IT WORKED!!!
    I truly appreciate the help!!!

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
  •