Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Location
    Seattle, WA, USA
    Posts
    9

    Question Unanswered: How to get aggregate start and end dates

    Hi, I'm a bit lost on this SPROC, hoping somebody can jumpstart my brain a bit please.

    What I have is tbl_Classes, with a row for every class that's been taught at a school. Each class was taught on certain dates, stored in tbl_Class_Dates. tbl_Class_Dates stores EVERY day the class met, not just the start and end dates. However, in this one case, I want to list class history only showing start and end date... something like:

    SELECT Class_ID,
    MIN(Class_Date) AS 'Start_Date',
    MAX(Class_Date) AS 'End_Date'
    FROM tbl_Class_Dates
    WHERE ...
    GROUP BY Class_ID, Class_Date
    HAVING Class_Date > @SomeCutoffDate


    Problem is, when I run the above format, it still gives me EVERY class row, and the MAX and MIN functions don't seem to work...

    I think I'm on the wrong track here. Incidentally, the old PROC is using a CURSOR and looping through, getting the start and end date and storing it all in a temp table. I'm hoping to improve on that efficiency.

    Thanks very much for any help, or let me know if I can clarify better. Cheers!

    - james

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This gets hideously ugly, but the performance gains are worth it. I'd use:
    Code:
    CREATE TABLE class_dates (
       class_id		INT		NOT NULL
    ,  held_on		DATETIME	NOT NULL
       )
    
    INSERT INTO class_dates (class_id, held_on)
       SELECT           101, '2004-09-20'
       UNION ALL SELECT 101, '2004-09-21'
       UNION ALL SELECT 101, '2004-09-22'
       UNION ALL SELECT 101, '2004-09-23'
       UNION ALL SELECT 101, '2004-09-24'
       UNION ALL SELECT 101, '2004-10-18'
       UNION ALL SELECT 101, '2004-10-19'
       UNION ALL SELECT 101, '2004-10-20'
       UNION ALL SELECT 101, '2004-10-21'
       UNION ALL SELECT 101, '2004-10-22'
       UNION ALL SELECT 101, '2004-11-08'
       UNION ALL SELECT 101, '2004-11-09'
       UNION ALL SELECT 101, '2004-11-10'
       UNION ALL SELECT 101, '2004-11-11'
       UNION ALL SELECT 101, '2004-11-12'
    
    SELECT *
       FROM class_dates AS b			-- begin of class session
       JOIN class_dates AS e			-- end of class session
          ON (e.class_id = b.class_id
          AND e.held_on = (SELECT Min(z2.held_on)	-- last day of session
             FROM class_dates AS z2
             WHERE  z2.class_id = b.class_id
                AND b.held_on <= z2.held_on 
                AND NOT EXISTS (SELECT *		-- Class on next day?
                   FROM class_dates AS z3
                   WHERE  z3.class_id = z2.class_id
                      AND z3.held_on = DateAdd(day, 1, z2.held_on))))
       WHERE  NOT EXISTS (SELECT *			-- no class previous day
          FROM class_dates AS z1
          WHERE  z1.class_id = z1.class_id
             AND z1.held_on = DateAdd(day, -1, b.held_on))
    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there you go again, pat, answering a much more complicated question than the one asked

    the only problem with jamlove's original query is that class_date was erroneously included in the GROUP BY

    by the way, thanks for posting the create/insert syntax, it helped my set up my test table

    after i ran the insert for class 101, i also added class 102 as follows:
    Code:
    INSERT INTO class_dates (class_id, held_on)
    SELECT    class_id + 1   , dateadd(d,2,held_on)   
    from class_dates
    then i ran your humungous query, but it failed to return anything for class 102

    then i ran this:
    Code:
    select class_id
         , min(held_on) as 'start_date'
         , max(held_on) as 'end_date'
      from class_dates
    group by class_id
    results:
    101 2004-09-20 00:00:00.000 2004-11-12 00:00:00.000
    102 2004-09-22 00:00:00.000 2004-11-14 00:00:00.000

    which i think is all that was required
    Last edited by r937; 10-08-04 at 16:46.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that the results that you posted are exactly what the poster asked for, but aren't what they wanted.

    If you look at the data that I posted, it shows three week long sessions for a class in a three month period. I assumed that the poster wanted to find the begining and end of each week long session, not the first and last day that the class was offered within the period.

    You did catch a minor bug in my original posting, which I've corrected (and added better test data too) in the following snippet:
    Code:
    CREATE TABLE class_dates (
       class_id		INT		NOT NULL
    ,  held_on		DATETIME	NOT NULL
       )
    
    INSERT INTO class_dates (class_id, held_on)
       SELECT           101, '2004-09-20'
       UNION ALL SELECT 101, '2004-09-21'
       UNION ALL SELECT 101, '2004-09-22'
       UNION ALL SELECT 101, '2004-09-23'
       UNION ALL SELECT 101, '2004-09-24'
       UNION ALL SELECT 101, '2004-10-18'
       UNION ALL SELECT 101, '2004-10-19'
       UNION ALL SELECT 101, '2004-10-20'
       UNION ALL SELECT 101, '2004-10-21'
       UNION ALL SELECT 101, '2004-10-22'
       UNION ALL SELECT 101, '2004-11-08'
       UNION ALL SELECT 101, '2004-11-09'
       UNION ALL SELECT 101, '2004-11-10'
       UNION ALL SELECT 101, '2004-11-11'
       UNION ALL SELECT 101, '2004-11-12'
    
    INSERT INTO class_dates (class_id, held_on)
       SELECT 100 + class_id, DateAdd(d, 7, held_on)
          FROM class_dates
    
    SELECT *
       FROM class_dates AS b			-- begin of class session
       JOIN class_dates AS e			-- end of class session
          ON (e.class_id = b.class_id
          AND e.held_on = (SELECT Min(z2.held_on)	-- first day of "gap"
             FROM class_dates AS z2
             WHERE  z2.class_id = b.class_id
                AND b.held_on <= z2.held_on 
                AND NOT EXISTS (SELECT *		-- Class on next day?
                   FROM class_dates AS z3
                   WHERE  z3.class_id = z2.class_id
                      AND z3.held_on = DateAdd(day, 1, z2.held_on))))
       WHERE  NOT EXISTS (SELECT *			-- no class previous day
          FROM class_dates AS z1
          WHERE  z1.class_id = b.class_id
             AND z1.held_on = DateAdd(day, -1, b.held_on))
    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    slippery, slippery slope, answering questions that you guess they meant to ask

    do that at work and you risk getting "arrogant" and "uncooperative" comments on your performance appraisal

    not you personally, pat, i mean IT people in general
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    slippery, slippery slope, answering questions that you guess they meant to ask

    do that at work and you risk getting "arrogant" and "uncooperative" comments on your performance appraisal

    not you personally, pat, i mean IT people in general
    True, but we've had no comment at all from jamlove... There hasn't been any way for me to ask what they meant.

    I've been burnt both ways, for being both "too helpful" and "too literal" when interpreting questions, and at least right now I'm willing to do more work than was asked rather than literally answer the question but only leave the asker frustrated.

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, i understand, but i don't buy the "frustrated" part, at least not here on these forums

    i mean, they can always say thanks and then go on to explain their question better

    when you consider the hours we put in answering questions, and when you consider what that might be worth, i figure anybody that gets "frustrated" by an answer to a question that they actually asked needs an attitude adjustment

    not you specifically, jamlove, i mean people posting questions on forums in general
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2004
    Location
    Seattle, WA, USA
    Posts
    9

    Post workin the prob

    Hey, sorry, i was caught up for a while there on another issue..

    Anyway, I've been working with the approach Pat suggested there, a JOIN back onto the same table, tbl_Class_Dates. I'm still in the middle of it due to that other issue, so I'll let you know.

    The answer is yes, I am wanting the Start and End data of each class "session", given back in a single row:

    Class_ID.....Start_Date.....End_Date

    for every class of a given course. Most of these classes, to help clarify, are taught about once a month. So the real data I want would look something like:

    Course_Name.....Class_ID......Start_Date.....End_D ate
    Boxing...............105............4/12/2003......4/16/2003
    Boxing...............109............5/2/2003........5/19/2003


    Here is a catch though: classes are not necessarily held on EVERY day in the date range. Some classes are MWF, some are T-Tr, some are every day, etc. I think this might be the trouble I'm having with Pat's approach, but I need to work on it a little more. Any other suggestions greatly appreciated.

    Thanks!

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you modify the Z3 query to "open the window" up a bit, you can probably find a better fit. You may have to tinker with it a bit to get things set appropriately for your specific setup.

    -PatP

  10. #10
    Join Date
    Apr 2004
    Location
    Seattle, WA, USA
    Posts
    9

    I think it's working

    Rudy, I think your suggestion about my GROUP BY error might've been the trick after all...

    Code:
    select class_id
         , min(held_on) as 'start_date'
         , max(held_on) as 'end_date'
      from class_dates
    group by class_id
    having max(class_Date) > current_timestamp   -- class has not yet ended
    Thanks a million for the help! I know of a place the other approach will help me as well.

    Cheers for now, jamlove

Posting Permissions

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