none
correct date field to be selected?

    Question

  • You cannot vote on your own post
    0

    I am doing a query on finding out when a service is suspended.

    I have a created date.  I have a suspended date.  So far so good.  However some servies are suspended multiple times.  Currently I just need to know the amount of days between the created date and the first suspended date.  So I thought it might be easier to create a date difference column to get over this.  However when I ran the data I got information like this.  So how can I be certain to pick up the first row only of tpk1?  Any help would be great on this. 

    So I have records looking like this

    tpk                     start_date                                                suspended date                                               Days_diff

    1                        2013-04-16 17:09:30.680                     2013-04-17 11:21:05.420                                            1

    1                        2013-04-16 17:09:30.680                     2013-04-17 17:09:30.680                                            1     

    1                        2013-04-16 17:09:30.680                     2013-04-17 14:17:55.090                                            1

    2                        2013-04-29 13:43:42.240                     2013-04-29 13:58:01.120                                            1

    <input id="f2974d29-c88a-4f3f-b589-55d6c9590fe1_attachments" type="hidden" />
          
    Thursday, June 27, 2013 12:34 AM

Answers

  • Don't have your complete schema details, therefore guessing structure. So, try something like below query:

    ;WITH CTE_Services
    AS
    (
    	SELECT ServiceID, Min(SuspendedDate) FirstSuspendedDateTime
    	FROM Services 
    	GROUP BY ServiceID
    )
    
    SELECT S.ServiceID, S.ServiceName, S.CreatedDate, DateDiff(Day, S.CreatedDate, CS.FirstSuspendedDateTime) AS NoOfDaysServiceSuspended
    
    FROM Services AS S
    	INNER JOIN CTE_Services AS CS
    		ON S.ServiceID = CS.ServiceID

    Thursday, June 27, 2013 2:34 AM