Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Location
    Buenos Aires, Argentina
    Posts
    5

    Post Unanswered: Group rows according to criteria

    Hello,

    I have a SQL Table named "Processes" with 3 rows:

    Item| Process | Date

    34 | Paint | 4/12/11
    34 | Welding | 1 /12/11
    37 | Arrange | 5/3/11
    37 | Paint |8 / 3 / 11
    37 | Pack | 12 /3/11

    I need to group it into another table the values "Item" plus a new record named "Dispersion" that shows the difference between the newest and the farthest date for a given "Item".

    By example, for the upper table the result should be:

    Item| Dispersion

    34 | 3 (=4-1)
    37 |7 (=12-5)

    I have thousands of Items in my table and each Item can have 2 or plus Processes with its date.

    Thank you very much!
    Last edited by vautrin; 06-10-11 at 20:34. Reason: change title

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT item
         , DATEDIFF(day,MAX(date),MIN(date)) AS Dispersion
      FROM daTable
    GROUP
        BY item
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Location
    Buenos Aires, Argentina
    Posts
    5

    Thanx very much!!! Issue solved

    Quote Originally Posted by r937 View Post
    Code:
    select item
         , datediff(day,max(date),min(date)) as dispersion
      from datable
    group
        by item
    thanx very much!!! Issue solved

  4. #4
    Join Date
    Jun 2011
    Location
    Buenos Aires, Argentina
    Posts
    5
    Hi r937 , it's me again...

    I wonder that if I have only one date value, the query returns "0", the same as I have two equals dates...

    I would need the query returns NULL or other value not equal to "0" when there is only 1 date to count.

    is this possible?

    thanx!

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should do it:
    Code:
    SELECT item
         , NULLIf(DATEDIFF(day, MIN([date]), MAX([date])), 0) AS Dispersion
      FROM daTable
    GROUP
        BY item
    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

  6. #6
    Join Date
    Jun 2011
    Location
    Buenos Aires, Argentina
    Posts
    5

    Problem solved.

    Thank you Wim.

    it was perfect! Problem solved.

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
  •