ansaurus

Question

SELECT at onece

Answer 1

+8 A:

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

Vinko Vrsalovic 2009-09-23 13:01:51

Correct answere. But in SQL Server, I would use the DATEDIFF function:SELECT p_id ,[Day Difference] = DATEDIFF(D, MIN(TreatmentDate), MAX(TreatmentDate)) FROM PatientsTable GROUP BY p_id

Yoav 2009-09-23 13:10:28

Would you please tell me, if i group by p_id, how dbms access to TreatmentDate? As you know if i group p_id the result would be only 15,16,17. There willn't be TreatmentDate will it?

uzay95 2009-09-24 10:12:38

you have to do a grouping function with the treatmentdate column (like sum(), max(), min(), avg(), etc.). In this case, you will have a number representing the substraction of the maximum date from the minimum date for each p_id.

Vinko Vrsalovic 2009-09-24 10:44:04

I think with my english, i can't ask what i want.But want to try last time.What is happening when we execute "SELECT p_id,max(TreatmentDate) FROM table GROUP BY p_id;" ? I think but i'm not sure: 1- Selecting p_id and TreatmentDate. 2- While grouping them (them = p_id and TreatmentDate even we dond't add it) 3- Running Min function to find the value for second row of select result. 4- Then generating new table and to send back.I don't know in which order it is processing but if you know exact result and order please tell me.PS:I know why we are grouping and how we can.

uzay95 2009-09-24 10:59:31

h, you are asking how do databases implement group by. I think this is database dependent and I also think this is material for a separate question. The question you want to ask is "how do databases implement group by?" In that question you should explain you know what group by does but that you want to know how is it done in the various database engines. As an appetizer you can watch the execution plan

Vinko Vrsalovic 2009-09-24 11:27:35

Answer 2

+2 A:

MS SQL:

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

K Richard 2009-09-23 13:10:11

Answer 3

+3 A:

MS SQL Server:

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

Chris Doggett 2009-09-23 13:11:32

This is assuming the OP is using SQL Server.

Vinko Vrsalovic 2009-09-23 13:33:20

@Vinko: Based on the tags from his other questions, I assumed SQL Server.

Chris Doggett 2009-09-23 13:44:00

It probably is, but consider the other readers of this question. Somebody else might try using DATEDIFF in MySQL, for example. It's better to warn readers of specific solutions.

Vinko Vrsalovic 2009-09-23 14:48:30

Answer 4

+1 A:

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.

randy melder 2009-09-23 13:14:02

And this is assuming the OP is using MySQL

Vinko Vrsalovic 2009-09-23 13:34:01

You test data comprises only 11 rows. The OP's test data comprises 12 rows. You are missing the row p_id = 16, TreatmentDate = 2008-05-11.

onedaywhen 2009-09-23 14:05:58

ansaurus

tags:

views:

answers:

SELECT at onece

related questions