Retrieve rows that have a first entry in 2014 in MySQL

I want to retrieve all rows from a table that have their first entry on or after 01/01/2014 but no later than 31/12/2014

Example of the table:

OID    FK_OID    Treatment    Trt_DATE
 1      100       19304       2011-05-24
 2      100       19304       2011-08-01
 3      100       19306       2014-03-05
 4      200       19305       2012-02-02
 5      300       19308       2014-01-20
 6      400       19308       2014-06-06

For example. I would like to pull all entries that have STARTED treatment in 2014. So above i would to extract FK_OID's 300 and 400 because their first entry is in 2014, but i would like to omit FK_OID 100 because they have 2 entries prior to 2014.

How do i go about this? I can extract all entries within a date range etc but that brings back all entries for that date and doesn't omit anyone who has an entry prior to the start of the date range. It just returns their first entry in 2014.

For the ones who need to see that i have tried something. See below. I am not an experienced coder and this is the best i can get because i don't have the knowledge.

SELECT 
    mod,
    (select NHSNum from person p
    WHERE
        p.oid = t.fk_oid) as 'NHS'
    FROM
        timeline t
    Where trt_date BETWEEN '2014-01-01' AND '2014-12-31'
    ORDER BY trt_date ASC

This returns every treatment for 2014 regardless of whether it is the first ever one for that person. I want to omit anyone from this list who has had treatment before 01/01/2014 as well as only return the first treatment per person. For example, this code returns all treatments for all people in 2014. I only want their first one and only if it is their first one ever.

Thanks.

Answers


 create table aThing
 (   oid int auto_increment primary key,
     fk_oid int not null,
     treatment int not null,
     trt_date date not null
 );

 insert aThing (fk_oid,treatment,trt_date) values
 (100,       19304,       '2011-05-24'),
 (100,       19304,       '2011-08-01'),
 (100,       19306,       '2014-03-05'),
 (200,       19305,       '2012-02-02'),
 (300,       19308,       '2014-01-20'),
 (400,       19308,       '2014-06-06');


select fk_oid,dt 
from 
(   select fk_oid,min(trt_date) as dt 
    from aThing 
    group by fk_oid 
) xDerived 
where year(dt)=2014;
+--------+------------+
| fk_oid | dt         |
+--------+------------+
|    300 | 2014-01-20 |
|    400 | 2014-06-06 |
+--------+------------+

The inner part, the nested one, become a derived table, and is given a name xDerived. This means that even though it is just a result set, by making it a derived table, it can be referred to by name. So it is not a physical table, but a derived one, or virtual one.

So that derived table is a very simple group by with an aggregate function. It says, for every fk_oid, bring back one row and only 1 row, with its minimum value for trt_date.

So if you have 10 million rows in that table called aThing, but only 17 distinct values for fk_oid, it will return only 17 rows. Each row being the minimum of trt_date for its fk_oid.

So now that that is achieved, the outer wrapper says just show me those two columns (but with a year check). There is a complicated to explain reason why I had to do that, so I will try to do it here.

But I might need a little time to explain it well, so bear with me.

This will be a shortcut way to say it. I had to get the min into an alias, and I only had access to that alias if resolved in a derived table, to cleanse it so to speak, and then access it with an outer wrapper.

An alias of aggregate column, like as dt, is not available (as a pseudo like column name which is what an alias is) ... it is not available in a where clause. But by wrapping it in a derived table name, I cleanse it so to speak, and then I can access it in a where clause.

So I can't access it directly in its own query in the where clause, but when I wrap it in an envelope (a derived table), I can access it on the outside.

I will try better to explain it later, maybe, but I would have to show alternative attempts to gain access to results, and the syntax errors that would result.


There's probably a more elegant solution, but this seems to satisfy the requirement...

SELECT x.*
  FROM my_table x
  JOIN
     ( SELECT fk_oid
            , MIN(trt_date) min_date
         FROM my_table 
        GROUP 
           BY fk_oid
       HAVING min_date > '2014-01-01'
     ) a
    ON a.fk_oid = x.fk_oid
  LEFT
  JOIN my_table b
    ON b.fk_oid = a.fk_oid
   AND b.trt_date > '2014-12-31'
 WHERE b.oid IS NULL;

Need Your Help

Nested properties wont "Stay" set

c# debugging properties nested designer

I am trying to make a custom library of controls. I at first thought I had the form finished and totally working but I didn't. Instead of asking on how to fix my problem with the form, I will as...

How to get selectedItem from ListView from a button click in the listView Item

c# .net wpf

I have a list view item which has a button and displays properties on an object. I have the command property set up for the button using the mvvm pattern which fires correctly.