CODEWITHSUNDEEP
sql
uzay95
uzay95

Reputation: 16632

SELECT all at once

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

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 have this result:

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

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

Upvotes: 1

Views: 296

Answers (4)

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340256

This should work in general

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

Upvotes: 8

Chris Doggett
Chris Doggett

Reputation: 20757

MS SQL Server:

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

Upvotes: 3

randomx
randomx

Reputation: 2377

This will work:

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

Given this schema/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 more help.

Upvotes: 1

K Richard
K Richard

Reputation: 1984

MS SQL:

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

Upvotes: 2

Related Questions