SQL date calculations

Question

need some help doing SQL date calculations:

In a table I have patients who are older than 18 and died from a certain disease (table a). In another table I have Patients of the same disease and the earliest date they were diagnosed with this disease (table b).

What i need to know is if 12 months has passed since they were diagnosed and when they died.

Can someone assist me in performing this date calculation.

The column in table a for date is indexdate and column is deathdate in table b for when they died.

Appreciate any help

Table A:

patientid--age--deathdate
1          20   11/05/2016
2          19   10/09/2015

Table B:

PatientID--indexdate
1          01/02/2015
2          08/03/2014

So essentially all i want to check is if 12 months has passed between indexdate and deathdate.

Im not sure what you want.. can you please post like your tables look like? — Sk1X1, Oct 28 '16 at 10:23
Could you provide table definitions and a sample query --- probably incomplete --- that indicates what you would like to get. For a single patient? For a single disease? How are diagnoses identified? Etc, etc. — Laurenz Albe, Oct 28 '16 at 10:23
repeated: http://stackoverflow.com/questions/17833176/postgresql-days-months-years-between-two-dates — McNets, Oct 28 '16 at 10:29

score 0 · Answer 1 · answered Oct 28 '16 at 10:32

0

This gives list of patients for whom 12 months passed since they were diagnosed and when they died.

SELECT A.patientID, A.patientName

FROM tableA A
INNER JOIN
(
SELECT patientID , MIN(DiagnoseDate) As EarliestDate
FROM tableB
GROUP BY patientID
) As B

ON A.patientID = B.patientID
WHERE  date_part('month',age(EarliestDate, DeathDate)) >=12

answered Oct 28 '16 at 10:32

Mudassir Hasan

28,083
20
99
133

Will give this a go. since it says >=12, I;m assuming it will also give results for those that died more than 12 months ago? – timz Oct 28 '16 at 10:46
no this gives list of patients whose date they died and earliest diagnosed date difference is 12 monthds' – Mudassir Hasan Oct 28 '16 at 11:03
what about if i want 12 months or more? – timz Oct 28 '16 at 11:28

score -1 · Answer 2 · answered Oct 28 '16 at 10:39

-1

You should be able to do that by writing a query that links the 2 tables by the patient id, then using the dateadd function in the where clause, which would be something like this example:

WHERE TableA.deathdate > (DATEADD(month, 12, TableB.indexdate))

answered Oct 28 '16 at 10:39

Webbo

92
7

There is no `dateadd()` in Postgres – Oct 28 '16 at 10:41
Ah - I didn't even notice it was postgress - just saw the SQL. – Webbo Oct 28 '16 at 12:52
..but the same logic should work with the postgress equivalent http://stackoverflow.com/questions/27080505/dateadd-equivalent-in-postgresql – Webbo Oct 28 '16 at 12:53

SQL date calculations

2 Answers2