question

n3w2sql avatar image
n3w2sql asked

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
sql-server-2008sql-server-2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
DenisT avatar image
DenisT answered
DECLARE @T1 TABLE ( Policy INT , PolicyDate DATETIME , [Status] VARCHAR(50) ); INSERT INTO @T1 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').
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.