Results 1 to 7 of 7

Thread: Group by + 30

  1. #1
    Join Date
    Oct 2005
    Posts
    58

    Unanswered: Group by + 30

    I want to run a select query with a group by like:

    Select MyText, MyDate
    From atest
    Group By MyText, MyDate

    However I want to group each "MyText" with the Min(MyDate) and Min(MyDate) + 30 days.

    So for example I want to select 3 records with the same "MyText" value and the 3 records have MyDate values of 01/01/2005, 01/10/2005 and 03/01/2005 I would return 2 records because the first record would be grouped with the first 2 dates with the first date showing and the second record would be only 03/01/2005 because it didn't fall within 30 days.

    How may I accomplish this?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You probably want

    GROUP BY DATEPART(mm,[date])

    If you read the sticky at the top we could probably help you better
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think he is going to need a cursor or a loop for this, since the value of each MIN() operation seems to be dependent upon the value of all the prior MIN() operations.

    A very odd request. What is the purpose?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    U2FUNNY

    That would mean every row could be part of 30 different result sets...which would be meaningless....
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think his process is:
    1) Find the minimum value.
    2) Group all values in the next 30 days with it.
    3) Find the next minum value that has not been included in a group.

    I don't see how this could be done without a loop, as it is a non-linear algorithm.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Oct 2005
    Posts
    58
    Yes, Blindman is correct in the approach. There actually other fields that will get added in so it makes more sense later. But I describe it with these 2 fields to make it easier to explain.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you would kindly read the sticky at the top of the forum and post some examples it would be a big help...in the manner that the sticky says...
    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.

Posting Permissions

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