Count min(date) where status='query' to where status='complete'

I am tryig to calculate how many days from one status to another and I cant figure the best way of producing the information.

I think I need a CASE DATEDIFF but im unsure of how to do this.

The table has 3 columns Policy,Date and Status. The table updates with the policy,date and status code('query') is added and when the the date the status is changed with the new statuscode 'complete'. I want to calculate how many days from when status ='query'to when status ='completed'. Any advice would be great. Thanks

more ▼

asked Feb 21, 2014 at 02:46 PM in Default

avatar image

900 22 32 39

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
       Policy INT ,
       PolicyDate DATETIME ,
       [Status] VARCHAR(50)
 VALUES  ( 1000, DATEADD(DAY, -10, GETDATE()), 'query' ),
         ( 1000, GETDATE(), 'complete' );
 SELECT  t.Policy ,
         DATEDIFF(DAY, t.PolicyDate, t2.PolicyDate) AS DiffInDays
 FROM    @T1 t
 INNER JOIN @T1 t2 ON t.Policy = t2.Policy
 WHERE   t.[Status] = 'query'
     AND t2.[Status] = 'complete';

Make sure you have the appropriate indexes! I also assumed the data types and assuming you preserve both rows ('query' and 'complete').

more ▼

answered Feb 21, 2014 at 03:51 PM

avatar image

3.5k 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 21, 2014 at 02:46 PM

Seen: 442 times

Last Updated: Feb 21, 2014 at 03:54 PM

Copyright 2018 Redgate Software. Privacy Policy