sql server - How can i get the days between results dates for each seperate pat_id? -

i'm looking days between result dates each patient: looking @ result dates result value <90.00

;with patient_results ( select * (values (1, 'ea11aee3-1d90-4602-9a37-0000007e2293', '85.10' ,'2015-12-11'), (1, '27bcd3e4-2381-4139-b420-0000025b4113', '91.50' ,'2016-01-05'), (1, 'd8969360-45d6-487b-af94-0000035f78b0', '81.00' ,'2016-07-21'), (5, '446e6413-442a-452a-bcf4-000006aa9896', '58.00' ,'2014-07-01'), (5, '00305129-bc14-4a12-8368-00000ac04a9b', '53.00' ,'2014-12-13'), (5, '96a67e53-2d6c-430b-a01f-00000ae4c37b', '42.80' ,'2015-02-01'), (5, '7c330511-3e99-488c-af5e-00000bdfa3ff', '54.00' ,'2015-07-01'), (8, '62a2806a-4969-417a-b4df-d547621cc594', '89.00' ,'2016-03-10'), (8, '3b9f4e5b-3433-4f21-850a-fc2127a24b72', '92.60' ,'2016-06-30'), (8, '1a2d780d-8c11-451c-8a64-6d49140b6232', '88.00' ,'2016-08-05') ) t (pat_id, visit_id, result_value, result_date)) 

based on above looking this:

pat_id  | visit_id                              | result_value    | result_date|   daysbetweenresultdates  1       | ea11aee3-1d90-4602-9a37-0000007e2293  | 85.10           | 2015-12-11     |  0    1       | d8969360-45d6-487b-af94-0000035f78b0  | 81.00           | 2016-07-21     |  223 5       | 446e6413-442a-452a-bcf4-000006aa9896  | 58.00           | 2014-07-01     |  0 5       | 00305129-bc14-4a12-8368-00000ac04a9b  | 53.00           | 2014-12-13     |  165 5       | 96a67e53-2d6c-430b-a01f-00000ae4c37b  | 42.80           | 2015-02-01     |  50 5       | 7c330511-3e99-488c-af5e-00000bdfa3ff  | 54.00           | 2015-07-01     |  150 8       | 62a2806a-4969-417a-b4df-d547621cc594  | 89.00           | 2016-03-10     |  0 8       | 1a2d780d-8c11-451c-8a64-6d49140b6232  | 84.00           | 2016-08-05     |  148 

i using sql server 2012, sql server management studio version 11.0.5058.0 thank you.

try this.

;with patient_results   (      select *          (values (1, 'ea11aee3-1d90-4602-9a37-0000007e2293', '85.10' ,'2015-12-11'),                  (1, '27bcd3e4-2381-4139-b420-0000025b4113', '91.50' ,'2016-01-05'),                  (1, 'd8969360-45d6-487b-af94-0000035f78b0', '81.00' ,'2016-07-21'),                  (5, '446e6413-442a-452a-bcf4-000006aa9896', '58.00' ,'2014-07-01'),                  (5, '00305129-bc14-4a12-8368-00000ac04a9b', '53.00' ,'2014-12-13'),                  (5, '96a67e53-2d6c-430b-a01f-00000ae4c37b', '42.80' ,'2015-02-01'),                  (5, '7c330511-3e99-488c-af5e-00000bdfa3ff', '54.00' ,'2015-07-01'),                  (8, '62a2806a-4969-417a-b4df-d547621cc594', '89.00' ,'2016-03-10'),                  (8, '3b9f4e5b-3433-4f21-850a-fc2127a24b72', '92.60' ,'2016-06-30'),                  (8, '1a2d780d-8c11-451c-8a64-6d49140b6232', '88.00' ,'2016-08-05') )      t (pat_id, visit_id, result_value, result_date)) select *, isnull(datediff(day, lag(result_date) over(partition pat_id order result_date), result_date), 0) daysbetweenresultdates  patient_results result_value < 90.00 

result

pat_id              visit_id                 result_value   result_date  daysbetweenresultdates 1      ea11aee3-1d90-4602-9a37-0000007e2293  85.10          2015-12-11      0 1      d8969360-45d6-487b-af94-0000035f78b0  81.00          2016-07-21      223 5      446e6413-442a-452a-bcf4-000006aa9896  58.00          2014-07-01      0 5      00305129-bc14-4a12-8368-00000ac04a9b  53.00          2014-12-13      165 5      96a67e53-2d6c-430b-a01f-00000ae4c37b  42.80          2015-02-01      50 5      7c330511-3e99-488c-af5e-00000bdfa3ff  54.00          2015-07-01      150 8      62a2806a-4969-417a-b4df-d547621cc594  89.00          2016-03-10      0 8      1a2d780d-8c11-451c-8a64-6d49140b6232  88.00          2016-08-05     148