question

n3w2sql avatar image
n3w2sql 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
sql-server-2008sqlcase
1 comment
10 |1200

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

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
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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;
10 |1200

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

n3w2sql avatar image
n3w2sql answered
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. :)
1 comment
10 |1200

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

Dear Kev, You wrote this query very Nicely...
0 Likes 0 ·

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.