query to get a child's age for each task

tjmahr commented 8 years ago

Should provide a table like:

ID	Study	Task	TaskDate	TaskAge
...	...	...	....	....
029L	TimePoint1	EVT	April 31, 2013	34
029L	TimePoint1	PPVT	August 21, 2013	38
...	...	...	....	....

Then, we can have a second query compute max(TaskAge) - min(TaskAge) to find lagged visits

It might be tricky to get data in long format in pure MySQL. Otherwise, we could spread Task over several columns (wide format), so there would be columns for EVTAge, PPVTAge, MinPairAge, etc.

tjmahr commented 7 years ago

If I do this, then #72 becomes trivial.

tjmahr commented 7 years ago

The terminology of "wide" versus "long" tables hasn't quite caught on in SQL land yet, so when googling for ideas use "pivot" and "unpivot" instead.

I can just stack many subtables on top of each other using union all as described here: http://stackoverflow.com/a/15192571/1084259

I tested it on our data and it seems to work fine so far. I just need to try it on many many tables

select 
  Study, 
  ResearchID, 
 "DELV_Variation" as Task, 
  DELV_Variation_Age as Age 
from 
  DELV_Variation 
union all 
  select 
    Study, 
    ResearchID, 
   "EVT" as Task, 
    EVT_Age as Age 
  from 
    EVT

LearningToTalk / L2TDatabase

query to get a child's age for each task #36