Geek Answers Handbook

CHOOSE all at once

I want to select patient_id and the date difference from the table below:

p_id TreatmentDate 15 2008-05-01 15 2008-05-03 15 2008-05-05 15 2008-05-07 16 2008-05-01 16 2008-05-03 16 2008-05-05 16 2008-05-09 16 2008-05-11 17 2008-05-03 17 2008-05-05 17 2008-05-07

I want to get this result:

 p_id Day Difference 15 6 Days 16 10 Days 17 4 Days

Do you have any suggestion that can generate this result in sql statement?

+1
sql
source share
4 answers

This should work at all

 select p_id, max(TreatmentDate) - min(TreatmentDate) from patientsTable group by p_id

more specifically for MSSQL Server

 select p_id, DATEDIFF(D, MIN(TreatmentDate), MAX(TreatmentDate)) from patientsTable group by p_id
+8
source share

MS SQL Server:

 SELECT p_id, STR(DATEDIFF(DAY, MIN(TreatmentDate), MAX(TreatmentDate))) + ' Days' AS DayDifference FROM table GROUP BY p_id
+3
source share

MS SQL:

 select p_id, datediff(d, min(TreatmentDate), max(TreatmentDate)) AS DayDifference from patientsTable group by p_id;
+2
source share

This will work:

 SELECT p_id, CONCAT(max(TreatmentDate) - min(TreatmentDate),' Days') as "Day Difference" FROM patient_info GROUP BY p_id;

Given this pattern / data:

 CREATE TABLE patient_info ( p_id INT, TreatmentDate DATE ); INSERT INTO patient_info VALUES (15,'2008-05-01'), (15,'2008-05-03'), (15,'2008-05-05'), (15,'2008-05-07'), (16,'2008-05-01'), (16,'2008-05-03'), (16,'2008-05-05'), (16,'2008-05-09'), (17,'2008-05-03'), (17,'2008-05-05'), (17,'2008-05-07'); +------+----------------+ | p_id | Day Difference | +------+----------------+ | 15 | 6 Days | | 16 | 8 Days | | 17 | 4 Days | +------+----------------+ 3 rows in set (0.00 sec)

Please let me know if you need further assistance.

+1
source share

More articles:


All Articles