asked
case statement counting days through every change
I have a table with 3 columns policy, Date and Status. Everytime the policy has been updated to a new status (maximum of 4 types) it will show the new status type with the date and policy. ie [Policy] AAA123 [Date] 2014-03-01 [Status] Query. Once changed it will then show on the next row AAA123 2014-03-17 Complete. As there can be 4 types of Status I want to count how many days from the earliest date with what kind of status to the next change. The end result would show [policy] AAA123 [Comments] Query 17 - Complete or if there was 3 changes then Query 10 - No Data 7 - Completed depending on the dates of course. I understand this is not too clear but if you can give any advise I would be most grateful. Thanks This is the Sample data from the table. polid date status ABC0264 2014-02-07 Query ABC0264 2014-03-14 Completed ABC0264 2014-03-03 No Data Received ABB0566 2014-02-07 Completed ABB1122 2014-02-07 Completed Expected Output shows how many days between each status unless the status shows completed polid Status_Entry ABC0264 No Data Received 4 Query 7 Completed ABB0566 Completed ABB1122 Completed
It might be better to include some example data together with the expected output. The formatting in the question doesn't clarify what you want to see exactly
Your question is at odds with the example output, e.g. "I want to count how many days from the earliest date", but the 2nd example output "Query 10 - No Data 7 - Completed" suggests the number of days since the **last** change Anyhow lets see how we go.... Here's an example of how you could do this. I use a cross apply to self join back to the data for the last change - this gets me the date and the status text. I can use these to get a datediff and finally concatenate all the changes together. declare @yourtable table (policy char(10), [date] date, [status] varchar(10)); insert into @yourtable select 'AAA123', '1 March 2014','Query'; insert into @yourtable select 'AAA123', '10 March 2014','No data'; insert into @yourtable select 'AAA123', '17 March 2014','Complete'; insert into @yourtable select 'AAA124', '1 March 2014','Query'; insert into @yourtable select 'AAA124', '17 March 2014','Complete'; with cte as ( select yt1.Policy, isnull(cast(datediff(d, prevchange.[date], yt1.[date]) as varchar)+ ' - ','') + yt1.[status]as [change] from @yourtable yt1 outer apply (select top 1 policy, [date] from @yourtable yt2 where yt1.policy = yt2.policy and yt1.[date]>yt2.[date]) prevchange ) select distinct Policy, stuff ( ( SELECT ' ' + c2.[change] from cte c2 where c1.Policy = c2.policy FOR XML PATH ( '' ) ) , 1 , 1 , '' ) from cte c1;
Many thanks Kev, I dont understand the FOR XML PATH and STUFF but this works.Thanks again as I spent over a day trying to think on how to do this. :)
14 People are following this question.
