As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on databases and SQL but for the time being I need to overcome this brick wall.
The table in question has the following layout:
TaskID - - - 1. On-site DateStart | 2. Return-to-base DateEnd | 3. Chargeable Type - - - - - 4. Complaint Reference 5. Rebook Requester Approver Cost ...
I want to select all "repeat"
TaskID is a unique ID but a "repeat" is classed as any two tasks that share the same
Reference and where the
DateEnd of each task is within a 30 day period, so, I'm thinking something along the lines of:
SELECT BOTH TaskID, Reference FROM Tasks WHERE Row1.Reference = Row2.Reference AND ABS(Row1.DateEnd - Row2.DateEnd) <= 30;
Obviously the above isn't valid but hopefully it illustrates how I want to select the data. I'm guessing it will involve a self-join but I have no clue where to start... having the query return both TaskIDs with the reference is ideal but if it only returns one TaskID then that's no problem.
I'm version of PostgreSQL that I'm using is 9.1.3, if that helps.
Thanks for your help.