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
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').
14 People are following this question.
