Problem solving

[Leopold]

Dear forum members. My first posting here. And it is a bit tricky and maybe bizarre. Assume the following scenario:

A war has been fought and has ended. I am a commandant for a completely new POW camp. A certain amount of prisoners has been transferred to my camp from three other camps. These prisoners have been registered in a database.

A year later there has been some changes: some new prisoners have arrived, others have died and others have been released. What I would like to know is exactly which ones of the original prisoners that was transferred to the camp a year back, that still remains in the camp.

How can I solve that problem?

Regards,
Leopold

 

[June7]

Depends on data. Might not be possible to know if adequate data not collected.
Are there fields for DateOfTransferIn and DateOfStatusChange (death, transfer out, release)?
SELECT * FROM table WHERE DateOfStatusChange IS NULL AND DateOfTransferIn = {date of first transfer}

 

[cheekybuddha]

Curious example!

SELECT
  p.*
FROM prisoners p
INNER JOIN transfers t
        ON p.PrisonerID = t.PrisonerID
WHERE p.DateOfDeath IS NULL
  AND p.ReleaseDate IS NULL
  AND t.TransferDate <= #2022-04-05#
;

 

[Leopold]

Actually no tables or fields have been decided yet. I hoped to have some suggestions regarding that.

Best,
Leopold

 

[jdraw]

Leopold,
I agree with David--- "interesting" example.
These prisoners have been registered in a database.
Actually no tables or fields have been decided yet. I hoped to have some suggestions regarding that.

What are we to assume of that database where prisoners have been registered?
Have you made a list of the things you would need to answer your question? Those are the things you will need to record, or from which you could derive any calculations to meet your needs.

An equally bizarre analogy:
Consider you are opening a new library, books are being transferred from an existing library(ies). The latest inventory of books was done 1 year ago. Some books have since been loaned and not returned; some have been destroyed because of condition, some are still in circulation, some new books have been added.
??Which books from that inventory review are at your new library??

 

[GPGeorge]

Dear forum members. My first posting here. And it is a bit tricky and maybe bizarre. Assume the following scenario:

A war has been fought and has ended. I am a commandant for a completely new POW camp. A certain amount of prisoners has been transferred to my camp from three other camps. These prisoners have been registered in a database.

A year later there has been some changes: some new prisoners have arrived, others have died and others have been released. What I would like to know is exactly which ones of the original prisoners that was transferred to the camp a year back, that still remains in the camp.

How can I solve that problem?

Regards,
Leopold

This has the ring of a school assignment. While most people are happy to help with technical questions, it seems to me that fewer people are likely to step in and do homework. If this is, in fact, a school assignment, the starting place would be the study materials provided with the course.

 

[Leopold]

Leopold,
I agree with David--- "interesting" example.
These prisoners have been registered in a database.
Actually no tables or fields have been decided yet. I hoped to have some suggestions regarding that.

What are we to assume of that database where prisoners have been registered?
Have you made a list of the things you would need to answer your question? Those are the things you will need to record, or from which you could derive any calculations to meet your needs.

An equally bizarre analogy:
Consider you are opening a new library, books are being transferred from an existing library(ies). The latest inventory of books was done 1 year ago. Some books have since been loaned and not returned; some have been destroyed because of condition, some are still in circulation, some new books have been added.
??Which books from that inventory review are at your new library??

I am sorry. I was rather vague when it comes to tables and fields. One table can be called "Prisoners" with Name, FirstName and BirthYear (possibly some more). Other than that it is open for suggestions.

 

[Leopold]

This has the ring of a school assignment. While most people are happy to help with technical questions, it seems to me that fewer people are likely to step in and do homework. If this is, in fact, a school assignment, the starting place would be the study materials provided with the course.

No, it is no school assignment. I am retired.

 

[June7]

Okay, I'll bite. What is incentive for this exercise? What do you really want to accomplish?

 

[Leopold]

Okay, I'll bite. What is incentive for this exercise? What do you really want to accomplish?

What I said in the first posting. Is it possible to solve this? And if so, how?

 

[cheekybuddha]

Yes it's possible to solve it, but it's much easier to answer with specifics if we have something specific to work with.

Abstract questions attract abstract answers.

What is the real world problem you are trying to solve? What experience do you have with databases?

Did the answer I posted suggest anything to you, or was it completely meaningless? (Which is fine, just trying to gauge where to start)

 

[MajP]

There are tons of ways to do it but in my prisoner table I would maybe have these fields

arrivalDate
departureDate
arrivalType (Original transfer, new capture, Subsequent transfer, other categories)
departureType (Transfer, death, released

Select records from some table where arrivalType = "original transfer" and departureType is null

If they are in the original batch and they have not departed then they are there and the original

 

[MarkK]

In my tDepartureType table there is a "Defenestrated" option.

 

[The_Doc_Man]

@MarkK - does that last option apply if the back end is a LINUX/SQL system?

 

[arnelgp]

How can I solve that problem?

my answer is general since this is a general question in general forum
and asked by former general.

i am sure you can ask whoever is in charge of the registry.
even in Shidler's list film the hero has a list, so there is a record of
everyone coming in and going out.

there is such a thing like gatepass or transfer form?
there is a copy given to the registry clerk to record
such movement.

even in stock inventory this is true.

 

[arnelgp]

These prisoners have been registered in a database

if there is a recording of "every" activities, you can easily obtain your results.
otherwise, no one cared to "document" anything, how can?

 

[Cronk]

This has the ring of a school assignment.

Or forward thinking by the Russian military?

 

[Leopold]

I can assure you that this is a completely fictitious thing. I live in Sweden btw and I am a bit of a novice when it comes to databases.

Could these tables be a start?

Table 1: Prisoners
PrisonerID = Primary Key
LastName
FirstName
DateofBirth
Camp (the camp from where they were transferred from)

Table 2: Camps
CampID = Primary Key
CampName

Table 3: PrisonersCamps (junction table)
PrisonerCampID = Primary Key
PrisonerID = Foreign Key
CampID = Foreign Key

All my best,
Leopold

 

[GPGeorge]

I would include a DateArrived in Table 3

 

[MarkK]

Camp, as a field, does not belong in Table 1. The table is called Prisoner, and one row should describe one prisoner.

I would include a DateArrived in Table 3

The junction table (Table 3) is where all the magic happens. There should be date arrived, date departed, departure type (including, but not limited to, defenestration), participation logs, grades on test, weight gain/loss, behaviour logs, whatever else happened for that prisoner at that camp should appear in--or be related to--Table 3.

The Camp, as an object, is mostly static. The Prisoner, as an object, is mostly static.

By contrast, the relationship between the Camp and the Prisoner (Table 3), is where all the moving parts are in this engine.

IMO