Databases
StackOverflow2013
Postsdbo
POSql: determine an event's active period from start date and end date split in 2 tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSql: determine an event's active period from start date and end date split in 2 tables
    primarykey
    data
    text
    <p>Sql problem: determine an event's active period from start date and end date split in 2 tables</p> <p>This is an adhoc report that I need to produce. I was provided 2 extract files pertaining to events (csv). I intend to insert these extracts to a single sql table #temp_events.</p> <p>File 1:</p> <pre><code>event, start date Core training, 01Jan2011 Certification reviews, 23Feb2011 Core training, 15May2011 Abc Event, 24Nov2011 </code></pre> <p>File 2:</p> <pre><code>event, end date Core training, 05Jan2011 Certification reviews, 24Feb2011 Core training, 18May2011 Abc Event, 30Nov2011 </code></pre> <p>I want to insert into a #temp_events, wherein an entry will define an event's active period. Note that there is no FK, as this is the primary problem at hand... the only way to relate data is via event name however there could be multiple occurrences of an event. An event's end cannot be calculated since there is no data that defines how long an event is. These are the restrictions that I need to deal with as extract provider has stipulated, unfortunately.</p> <p>Can anyone provide suggestions for the logic that I need to code, so that I can reliably link event's in File 1 to File 2 even though there is no natural key/id/FK? Any obvious loophole that I may run into?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USwildplasser
    1. USCarlos Jaime C. De Leon
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COare the events in the file are in proper order ? is order set ? if yes, just add ordering coulm by event name , then join both tables like Table1.name = Table2.name and Table1.order = Table2.order that would match first occurance of the event with first ending
      singulars
      1. POSql: determine an event's active period from start date and end date split in 2 tables
      1. USWKordos
    2. COIs there a guarantee that every event-start has a maching event-end? (or that *at most one event* (per eventtype) can be still uncompleted)
      singulars
      1. POSql: determine an event's active period from start date and end date split in 2 tables
      1. USwildplasser
    3. CO@WKordos - no definite order though, but will sorting it in temp table then re-inserting help?
      singulars
      1. POSql: determine an event's active period from start date and end date split in 2 tables
      1. USCarlos Jaime C. De Leon
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload