If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > PostgreSQL > Newbie, Create groups of chained records

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 1
Newbie, Create groups of chained records

Hi,
(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)
3 etc.....
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,
TIA

F. Neurth
Attached Thumbnails
Newbie, Create groups of chained records-cleaner-activity.png   Newbie, Create groups of chained records-contracts.png  

Last edited by fneurth; 12-19-12 at 03:30. Reason: typos, clarity
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On