Databases
StackOverflow2013
Postsdbo
POSQL Group By Date Conflicts
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Group By Date Conflicts
    primarykey
    data
    text
    <p>I have a table with columns start_date and end_date. What we need to do is Select everything and group them by date conflicts for each Object_ID. </p> <p>A date conflict is when a row's start date and/or end date pass through another rows'. For instance, here are some examples of conflicts:</p> <p>Row 1 has dates 1st through the 5th, Row 2 has dates 2nd through the 3rd.</p> <p>Row 1 has dates 2nd through the 5th, Row 2 has dates 1st through the 3rd.</p> <p>Row 1 has dates 2nd through the 5th, Row 2 has dates 3rd through the 6th.</p> <p>Row 1 has dates 2nd through the 5th, Row 2 has dates 1st through the 7th.</p> <p>So for example, if we have some sample data (assume the numbers are just days of the month for simplicity):</p> <pre><code>id | object_id | start_date | end_date 1 | 1 | 1 | 5 2 | 1 | 2 | 4 3 | 1 | 6 | 8 4 | 2 | 2 | 3 </code></pre> <p>What i would expect to see is this:</p> <pre><code>object_id | start_date | end_date | numconflicts 1 | &lt;na&gt; | &lt;na&gt; | 2 1 | 6 | 8 | 0 or null 2 | 2 | 3 | 0 or null </code></pre> <p>And for a Second Test Case, Here is some sample data:</p> <pre><code>id | object_id | start_date | end_date 1 | 1 | 1 | 5 2 | 1 | 2 | 4 3 | 1 | 6 | 8 4 | 2 | 2 | 3 5 | 2 | 4 | 5 6 | 1 | 2 | 3 7 | 1 | 10 | 12 8 | 1 | 11 | 13 </code></pre> <p>And for the second Test Case, what I would expect to see as output:</p> <pre><code>object_id | start_date | end_date | numconflicts 1 | &lt;na&gt; | &lt;na&gt; | 3 1 | 6 | 8 | 0 or null 2 | 2 | 3 | 0 or null 2 | 4 | 5 | 0 or null 1 | &lt;na&gt; | &lt;na&gt; | 2 </code></pre> <p>Yes, I will need some way of differentiating the first and the second grouping (the first and last rows) but I haven't quite figured that out. The goal is to view this list, and then when you click on a group of conflicts you can view all of the conflicts in that group.</p> <p>My first thought was to attempt some GROUP BY CASE ... clause but I just wrapped by head around itself. </p> <p>The language I am using to call mysql is php. So if someone knows of a php-loop solution rather than a large mysql query i am all ears.</p> <p>Thanks in advance.</p> <p>Edit: Added in primary Keys to provide a little less confusion. </p> <p>Edit: Added in a Test case 2 to provide some more reasoning.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPeanut
    1. USPeanut
    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
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Group By Date Conflicts
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Group By Date Conflicts
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Group By Date Conflicts
      1. This table or related slice is empty.
      1. VTUpMod
    1. COno, object_id does not refer to the id of the table, it just refers to an object to a different table with that id. Meaning, only rows with similar object_id's can have a possibility of being a conflict.
      singulars
      1. POSQL Group By Date Conflicts
      1. USPeanut
    2. COJust a thought: If you are intending the conflict rows to be clickable & expandable, putting `<na>` instead of actual dates doesn't seem to make much sense. If I were you, I would possibly use `MIN(start_date) AS start_date` and `MAX(end_date) AS end_date` for the conflict rows. That way the user would be able to see immediately what date range the conflict group belongs to, without having to click/expand it first.
      singulars
      1. POSQL Group By Date Conflicts
      1. USAndriy M
 

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