none
How to get differenct between First and Last Date in SQL Server

    Question

  • Dear All I have two tables

    1. Comments (ID,Comment, CurrentStatus)

    2. Comment Detail(ID,CommentID,Dated,Status)

    ON every status change I Enter new record in Comment Detail.

    I need a query in which I can check the Total Number of records group by Status and showing  difference between Dates.

    Like The result should be

    Example

    20 Records Resolved on Time (within 3 days)

    10 Records Resolved with delay (more then 3 days)

    any one help me in this regard ?

    Thanks :)


    khalid

    Monday, September 02, 2013 10:18 AM

Answers

  • hmm, from your description:

    SELECT  [Status] ,
            COUNT(*) ,
            DATEDIFF(DAY, MIN(Dated), MAX(Dated))
    FROM    [Comment Detail]
    GROUP BY [Status];

    But your desired result should be:

    WITH    DateDifference ( Resolved )
              AS ( SELECT   CASE WHEN DATEDIFF(DAY, MIN(Dated), MAX(Dated)) < 4 THEN 'on time'
                                 ELSE 'delayed'
                            END
                   FROM     [Comment Detail]
                   WHERE    [Status] = 'Resolved'
                   GROUP BY CommentID
                 )
        SELECT  Resolved ,
                COUNT(*)
        FROM    DateDifference
        GROUP BY Resolved;

    Monday, September 02, 2013 10:46 AM

All replies

  • hmm, from your description:

    SELECT  [Status] ,
            COUNT(*) ,
            DATEDIFF(DAY, MIN(Dated), MAX(Dated))
    FROM    [Comment Detail]
    GROUP BY [Status];

    But your desired result should be:

    WITH    DateDifference ( Resolved )
              AS ( SELECT   CASE WHEN DATEDIFF(DAY, MIN(Dated), MAX(Dated)) < 4 THEN 'on time'
                                 ELSE 'delayed'
                            END
                   FROM     [Comment Detail]
                   WHERE    [Status] = 'Resolved'
                   GROUP BY CommentID
                 )
        SELECT  Resolved ,
                COUNT(*)
        FROM    DateDifference
        GROUP BY Resolved;

    Monday, September 02, 2013 10:46 AM
  • Thank you very much

    khalid

    Friday, September 13, 2013 8:08 AM