(Newbie, Postgresql 9.1)
I have a problem in a table that has some records that link to one another and need to be grouped.
Some records are 'sons' of other records so I can order them into a chain but I don't know how to
write a Group ID onto group of chained records.
I hope the problem can be solved by straighforward SQL but maybe psql will be necessary.
I haven't yet tried my hand at PSQL.
Here's the story :
Acme Company supplies office cleaning personnel to Companies Alpha, Beta, Gamma ....
Acme and her client Companies have one or more Contracts for a Cleaner - or a Replacement Cleaner.
If Acme sends a Replacement Cleaner, there is no need for a new Contract, but the change does appear in the Cleaner Activity table.
The successive replacement cleaners are all covered by the same Contract.
So one Contract can group together several Cleaners, 1 original Cleaner and any replacements.
Alas, Acme has lost the original Contracts table and is trying to contruct a new one based on the data found in the Cleaner Activity records : i.e. who worked when.
The Contracts field in the Cleaner Activity records was wiped by gremlins.
As the Contract table is gone for good, Acme is willing to give new IDs to the Contracts.
Priority number 1 is, for each contract, to
find the Start Date and Finishing Date for the first Cleaner (if there was only ever one cleaner in the Contract)
- or -
the Start Date for the first Cleaner and the Finishing Date for the last of all the replacement Cleaners (if any exist).
--- Please see the data in Cleaner Activity.png attached.
This data contains evidence of two Contracts :
The first discernable Contract
- concerns Cleaners A, B, C and D.
- started on 01/01/2008 and has no Date Finished, so we can conclude that it is ongoing.
The second discernable Contract
- concerns Cleaners E, F and G.
- started on 01/01/2009 and finished on 31/12/2011.
I would like to be able to update the Contract ID field with a sequence :
1 for the first Contract (and its Group of Cleaners),
2 for the second Contract (and its Group of Cleaners)
but I don't know how to do it.
I thought of GROUP BY but I have no common value in any field to group on.
I thought of Window fuctions but I have no common value to PARTITION on ...
So I imagine that the first step will be to get a common value into a field.
So I now have an empty Contract ID field ready and waiting and no idea how to fill it.
I will be happy when I am able to produce this new Contract table.
--- Please see the Contracts.png attached.
Any help appreciated,