Botanical propagation data analysis
Major Questions
Goals for data parsing:
- If subsequent treatment record is a location only and matches the date of a preceding treatment or activity record, then merge the location into a new field. The new UI has a field for Nursery Location.
- In the new CSpace UI design, concentration from the SAGE treatment table is moving up to CSpace propagation concentration and only being used for hormone treatments. However, there are propagations that have multiple treatments that have different concentration values and also different hormones. Do we need to add hormone and concentration back into the CSpace Event (activity) record? Or maybe the hormone remains the Event Type (which means we can't merge those treatment types). Do we have concentration values for treatments that are not hormones (e.g., fungicide)?
- The new model proposes merging Number Started from Propagation records and Propagule Count in Activity (growh_history) records. It seems like we need a field at the Propagation level to hold the legacy Number Started information (because there are 2884 conflicting records), but data analysis might help make sense of this.
- Can Treatment Types be merged by their Treatment Category so that we don't have so many Event Types in CSpace? We might have to ask UCBG about this.
- Can we derive the new top-level propagation value for the seeds/scar-strat dropdown? Use UDF? Need data analysis.
- Can we derive the new top-level propagation value for the cuttings/cutting type dropdown? Use UDF? Need data analysis.
- Can we derive the new top-level propagation value for the cuttings/hormone dropdown? Use UDF? Need data analysis.
- Can we derive the new top-level propagation value for the cuttings/concentration dropdown? Use UDF? Need data analysis.
Also: Search for "ASKUCBG" in notes.
propType drop down - Susan wants to prune the drop down list to three values: seeds, cuttings, live plants, unsure what the mapping is for the rest of the values, may go to new fields we're adding (cutting type?). FINDING: Several other values might map to seeds, cuttings, live plants. Or we can concatenate into propagation comments. SOme values might go into Cutting type. Also, if we can make inactive terms not appear in drop down lists, then we are in good shape.
** = legacy read-only field might be needed
** numberStarted in SAGE propagation table is moving down to CSpace activity propaguleCount (merged where possible). FINDING: There are 2884 situations where propagation.number_started differs from growth_history.propagule_count. What do we do? Presumably we take the growth_history.propagule_count value, but we then lose propagation.number_started.
propaguleCount in SAGE treatment table is moving up to CSpace activity propaguleCount (merged where possible)
order in SAGE treatment table is moving up to CSpace activity order (collated). FINDING: Ignore treatment.propagule_count.
type in SAGE treatment table is moving up to CSpace activity type - though values from both of these might move up to new fields 'scar/stratification' in propagation, 'hormone' in propagation, 'location in nursery' in activity, or 'fungicide' in activity. FINDING: Might merge treatment types by Treatment Category.
notes in SAGE treatment table is moving up to CSpace activity comments. Finding: We're OK since we are getting the union of SAGE activity records and SAGE treatment records.
germination/rooting date in SAGE activity table is moving up to CSpace propagation germination/rooting date - will have to do data analysis to make sure there is one per propagation. FINDING: Always null so can ignore!
successRate in SAGE activity table is moving up to CSpace propagation successRate - will have to do data analysis to make sure there is one per propagation. FINDING: Only 3 propagations have different growth_history.percent_success values (416 for 92.1029; 746 for 81.0829; 2836 for 2002.0330) If we can clean those up (set one value), then we'll be OK
treatment dates in SAGE treatment table are moving up to CSpace activity dates. Finding: We're OK since we are getting the union of SAGE activity records and SAGE treatment records..
** concentration in SAGE treatment table is potentially moving up to CSpace propagation concentration - will have to do data analysis to make sure there is one per propagation - or we can keep this as a hidden field in the activity group for historical data. FINDING: There are approximately 20 propagation records that have treatments with different concentration values. Might need a legacy field if these can't be "cleaned up" or if it doesn't make sense to clean them up (examples: 567 for 90.0231; 1551 for 77.0717). Does this break the model? ASKUCBG.
OTHER MAJOR QUESTIONS:
- If treatment type is one of the locations (e.g., lath house), we want to move that to the Location in Nursery field. What are all the location treatment types? Ideally, we would merge these with the matching activity-treatment record, but these might be hard to find.
- How do we derive the new top-level propagation value for the seeds/scar-strat dropdown? Use UDF? Need data analysis.
- How do we derive the new top-level propagation value for the cuttings/cutting type dropdown? Use UDF? Need data analysis.
- How do we derive the new top-level propagation value for the cuttings/hormone dropdown? Use UDF? Need data analysis.
- How do we derive the new top-level propagation value for the cuttings/concentration dropdown? Use UDF? Need data analysis.
Notes from analysis
spent some quality time this afternoon analyzing data in the propagation tables for the Botanical Garden. We have a situation where they have requested a design that is significantly different from what they use now in SAGE. Effectively they are going from a situation where three core entities support the information (propagation efforts can have multiple activities; activities can have multiple treatments) to one where they have two entities (propagation efforts can have multiple activities). So the treatment data is getting moved around in different places. This is certainly a simpler model for new data going into the system (the old one was too complex), but we needed to do some quick data analysis to see what this would mean for legacy data.
The simplest approach (though still hard) is to combine records in activity and treatment together and map those fields to new CSpace fields. But we knew of several cases where they wanted to do some more involved mapping to combine multiple legacy activity-treatment records into one CSpace activity record. In my notes below I basically conclude that it is not feasible to clean up data like this, that in fact it does not buy much and is a very expensive operation.
Lam and Amy, you should take a look at this and see if you concur or think we should look at anything else.
Amy, I am certain we'll have to add some of those fields to hold the legacy treatment_type and such. As discussed I think it might be good to make those read-only and clearly labeled ("SAGE Treatment" or something like that).
John, Glen, and Ray, I'd definitely be interested in your thoughts here, though I'm not expecting you to spend serious time on this.
Thanks,
Chris
--------
SAGE schema (all tables have create and update info too)
propagation: 6738 recs
prop_id
accession_number
prop_date
early_prop_date
late_prop_date
prop_type
purpose
number_started
prop_comments
extra_seed_flag
gh_sequence
growth_history: 19929
prop_id
growth_id
activity_date
activity_begin_date
activity_end_date
activity_type_id
propagule_count
pot_type
pot_size_id
percent_success
growth_comments
medium_name
germination_date
treatment: 20451
prop_id
growth_id
treatment_type_id
concentration
begin_date
end_date
propagule_count
treatment_notes
display_sequence
This query gets the activity and treatment display names too:
NOTE: outside joins needed because there are activities that don't have treatments attached
select gh.prop_id, gh.growth_id, gh.activity_type_id, atc.activity_type, t.display_sequence treatment_seq, t.treatment_type_id, tt.treatment_type_name from growth_history gh join activity_type_code atc on (gh.activity_type_id=atc.activity_type_id) left outer join treatment t on (gh.prop_id=t.prop_id and gh.growth_id=t.growth_id) left outer join treatment_type tt on (t.treatment_type_id=tt.treatment_type_id) order by gh.prop_id, gh.growth_id, t.display_sequence
with data like:
25 1 4 started 1 16 benlate
25 1 4 started 2 25 lesan
25 1 4 started 3 15 prop house
25 1 4 started 4 1 bottom heat
25 2 14 results (null) (null) (null)
25 3 2 changed pot 1 15 prop house
25 4 14 results 1 15 prop house
25 5 2 changed pot 1 14 holding area
25 6 3 planted out (null) (null) (null)
showing 6 activities, the first of which has 4 treatments. two activities have no joined treatment records. Without hard work this would end up as 9 activities in CSpace for this activity. And we would be adding the read-only legacy treatment type fields, etc.
How are activity_types distributed?
select atc.activity_type, count\(*) from growth_history gh, activity_type_code atc where gh.activity_type_id=atc.activity_type_id group by activity_type order by count\(*) desc
potted 4359
started 3620
changed pot 3256
results 3139
pretreat 2210
planted out 1120
changed location 1001
applied treatment 544
dead 358
discarded 145
changed medium 62
repotted 61
checked pot 21
divided 10
plant sale 7
moved 4
sow 3
cold strat 2
cutting taken 2
surplus 1
damped off 1
distributed 1
deaccessioned 1
returned to nursery 1
Treatment types, interesting, there is a treatment category field that identifies treatment types in several categories: null, disinfectant, environment, fertilizer, fungicide, hormone, none, pesticide, physical, scarification, stratification
How are treatment types distributed?
select tt.treatment_type_name, count\(*) from treatment t, treatment_type tt where t.treatment_type_id=tt.treatment_type_id group by tt.treatment_type_name order by order by count\(*) desc
lath house 4346
prop house 3119
intermittent mist 1677
cold stratification 1338
bottom heat 1309
prop house 1 1128
holding area 914
benlate 845
physan wash 813
lights 747
dip and grow 630
physan 590
IBA 543
soak in water 431
benlate & captan 261
IBA talc 250
benlate & lesan 211
warm stratification 162
research greenhouse 135
Hormex 97
sulphuric acid 95
water tray 92
prop house shade 90
smoke plus 79
subdue 58
shade house 51
boiling water on soil surface 50
scarify 40
GA3 32
lesan 31
Rootone 25
Truban 25
prop house 2 shade 24
hormone 18
benlate & physan 17
benlate & subdue 17
glass cover 16
physan soak 12
nursery 11
wardian case 11
lath house shade 10
Nurs 7
wound cuttings 7
boiling water 6
captan 5
fungicide 5
stratification 5
2-4-2 4
freeze 4
microwave oven 4
osmocote 4
pericarp removed 4
outside prop house 3
refrigerator 3
central prop house 2
fire 2
heat 2
liquid fertilizer 2
pericarp trimmed 2
physan dip 2
rear prop house 2
Serenade 2
bleach 1
bright location 1
cactus house 1
chipped 1
clorox soak 1
darkness 1
drenched in hot water 1
dusted with sulphur 1
force dormant 1
gravel mulch 1
green house #2 1
iron sulphate 1
metal ponds 1
NAA 1
pericarp ruptured 1
prop house 2 1
seed coat removed 1
shade 1
sterilized 1
sulphur 1
under mist bench 1
wash & clean 1
water & let go dry 1
water basin 1
There must be 4 unused treatments (91 in the table). Many of the top ones are related to location (lath house, prop house)
OK, so how are counts of combined activity-treatment records distributed?
select atc.activity_type, tt.treatment_type_name, count\(*) from growth_history gh join activity_type_code atc on (gh.activity_type_id=atc.activity_type_id) left outer join treatment t on (gh.prop_id=t.prop_id and gh.growth_id=t.growth_id) left outer join treatment_type tt on (t.treatment_type_id=tt.treatment_type_id) group by atc.activity_type, tt.treatment_type_name order by count\(*) desc
275 rows, but here they are. See Excel attachment too.
results (null) 3089
potted lath house 1822
potted intermittent mist 1572
changed pot lath house 1377
planted out (null) 1116
started cold stratification 1102
started prop house 996
potted prop house 858
changed pot prop house 857
pretreat physan wash 792
started bottom heat 697
started benlate 613
pretreat dip and grow 613
started lath house 567
pretreat IBA 527
started physan 526
started lights 497
changed location lath house 484
started prop house 1 473
potted holding area 447
pretreat soak in water 428
changed pot (null) 361
dead (null) 355
potted (null) 353
changed location prop house 320
potted bottom heat 316
changed pot holding area 308
potted prop house 1 255
changed pot prop house 1 248
pretreat IBA talc 247
changed location bottom heat 226
applied treatment cold stratification 215
discarded (null) 145
started benlate & lesan 133
changed location prop house 1 130
started warm stratification 128
potted lights 125
started holding area 119
pretreat benlate 111
applied treatment benlate & captan 108
pretreat sulphuric acid 95
pretreat Hormex 94
pretreat smoke plus 77
changed pot benlate & captan 68
started prop house shade 64
started research greenhouse 64
changed location lights 58
changed pot lights 57
started water tray 56
started intermittent mist 54
started (null) 53
pretreat physan 53
started benlate & captan 53
potted benlate 52
applied treatment bottom heat 50
started boiling water on soil surface 48
changed pot research greenhouse 40
changed pot shade house 38
applied treatment benlate 37
applied treatment prop house 37
pretreat scarify 34
applied treatment benlate & lesan 33
pretreat GA3 31
potted water tray 31
changed location holding area 30
applied treatment warm stratification 30
changed pot benlate 29
applied treatment subdue 29
pretreat Rootone 25
changed medium (null) 24
potted benlate & captan 24
changed medium lath house 24
potted research greenhouse 24
results lath house 22
potted prop house shade 22
changed pot benlate & lesan 21
started lesan 19
results prop house 19
changed location (null) 18
started Truban 18
pretreat hormone 18
repotted lath house 18
changed pot intermittent mist 18
repotted prop house 17
started benlate & physan 17
started prop house 2 shade 16
pretreat (null) 15
started glass cover 15
applied treatment IBA 15
pretreat intermittent mist 15
changed pot subdue 14
changed pot bottom heat 14
potted benlate & subdue 13
potted cold stratification 13
checked pot (null) 12
potted shade house 12
pretreat benlate & lesan 12
repotted (null) 11
repotted prop house 1 11
applied treatment lath house 11
applied treatment physan wash 11
potted subdue 10
changed pot nursery 10
potted benlate & lesan 10
applied treatment dip and grow 9
started physan wash 8
checked pot lath house 8
applied treatment lesan 8
changed medium prop house 8
plant sale (null) 7
pretreat lath house 7
pretreat physan soak 7
changed location wardian case 7
applied treatment (null) 6
started dip and grow 6
potted lath house shade 6
applied treatment scarify 6
pretreat benlate & captan 6
changed location intermittent mist 6
potted physan 5
changed pot Nurs 5
pretreat stratification 5
applied treatment lights 5
repotted intermittent mist 5
changed location prop house 2 shade 5
changed location research greenhouse 5
moved (null) 4
divided (null) 4
potted Truban 4
repotted subdue 4
started physan soak 4
results holding area 4
started boiling water 4
pretreat microwave oven 4
pretreat wound cuttings 4
applied treatment captan 4
applied treatment osmocote 4
pretreat cold stratification 4
applied treatment intermittent mist 4
divided prop house 1 3
changed pot water tray 3
started wound cuttings 3
applied treatment physan 3
pretreat pericarp removed 3
potted warm stratification 3
changed medium holding area 3
sow (null) 2
cold strat (null) 2
cutting taken (null) 2
potted lesan 2
pretreat fire 2
pretreat heat 2
started freeze 2
dead prop house 2
pretreat lights 2
repotted benlate 2
changed pot 2-4-2 2
divided lath house 2
potted physan wash 2
potted dip and grow 2
potted wardian case 2
pretreat physan dip 2
pretreat prop house 2
pretreat bottom heat 2
results prop house 1 2
started refrigerator 2
started wardian case 2
planted out lath house 2
applied treatment 2-4-2 2
applied treatment Hormex 2
started lath house shade 2
pretreat pericarp trimmed 2
repotted benlate & captan 2
results intermittent mist 2
applied treatment IBA talc 2
applied treatment Serenade 2
started outside prop house 2
applied treatment fungicide 2
changed location water tray 2
changed medium prop house 1 2
applied treatment smoke plus 2
applied treatment holding area 2
applied treatment prop house 1 2
changed pot prop house 2 shade 2
applied treatment boiling water 2
applied treatment soak in water 2
changed location lath house shade 2
changed location prop house shade 2
applied treatment benlate & subdue 2
applied treatment liquid fertilizer 2
applied treatment central prop house 2
changed location cold stratification 2
surplus (null) 1
damped off (null) 1
distributed (null) 1
deaccessioned (null) 1
potted Nurs 1
results IBA 1
pretreat NAA 1
potted Hormex 1
started shade 1
divided lights 1
pretreat lesan 1
results lights 1
started captan 1
started subdue 1
dead lath house 1
potted IBA talc 1
pretreat Truban 1
pretreat bleach 1
pretreat freeze 1
results benlate 1
started sulphur 1
dead bottom heat 1
potted fungicide 1
pretreat chipped 1
sow prop house 1 1
changed pot lesan 1
changed pot Truban 1
changed pot physan 1
potted metal ponds 1
potted water basin 1
pretreat fungicide 1
repotted fungicide 1
started sterilized 1
potted cactus house 1
potted gravel mulch 1
results bottom heat 1
started clorox soak 1
potted force dormant 1
applied treatment GA3 1
changed location Nurs 1
changed medium lights 1
pretreat wash & clean 1
repotted holding area 1
started soak in water 1
checked pot prop house 1
dead intermittent mist 1
planted out prop house 1
potted rear prop house 1
pretreat iron sulphate 1
potted under mist bench 1
results benlate & lesan 1
started bright location 1
started rear prop house 1
applied treatment Truban 1
applied treatment freeze 1
changed location nursery 1
started benlate & subdue 1
potted outside prop house 1
potted water & let go dry 1
applied treatment darkness 1
changed medium bottom heat 1
changed pot green house #2 1
potted dusted with sulphur 1
pretreat pericarp ruptured 1
pretreat seed coat removed 1
results prop house 2 shade 1
divided research greenhouse 1
changed location shade house 1
changed pot benlate & subdue 1
changed pot prop house shade 1
repotted research greenhouse 1
applied treatment glass cover 1
applied treatment physan soak 1
changed location prop house 2 1
changed location refrigerator 1
started drenched in hot water 1
returned to nursery lath house 1
planted out cold stratification 1
changed location benlate & lesan 1
applied treatment pericarp removed 1
applied treatment prop house shade 1
pretreat boiling water on soil surface 1
applied treatment boiling water on soil surface 1
What we'd be really interested in finding out is if we can identify the situations where an activity has multiple treatment records, but the second is just a location. Then we could fold those together. But what if there are more than two? Use the date? What if there are only two but they have different dates? Then maybe they should be separate CSpace records. Ugh, too hard. Maybe just find all combinations of activity and treatment. Are there activities that don't have treatment records? Yes! Queries adjusted to do outside join to treatment table.
FYI, the number of records produced by getting all combinations of activity and treatment:
select count\(*) from growth_history gh join activity_type_code atc on (gh.activity_type_id=atc.activity_type_id) left outer join treatment t on (gh.prop_id=t.prop_id and gh.growth_id=t.growth_id) left outer join treatment_type tt on (t.treatment_type_id=tt.treatment_type_id)
26027
growth_history has 19.9K and treatment has 20.5K so by loading all combinations we are not growing the number of records by very much beyond the ideal. Maybe you could argue that this is 5500K extra records.
---
Next day:
Propagation type
SELECT
prop_type,
count(*)
FROM
dbo.propagation
group by prop_type
seed 3630
cutting 1704
living plant 1104
division 111
bulbs 88
seedlings 33
corms 17
bulb 12
transplant 11
corm 6
rooted pieces 5
spores 5
seedling 3
bulblets 3
rooted cuttings 2
bulb scales 2
bare root 1
tuber 1
ASKUCBG: Susan wants to reduce prop type to three values. Can we map these existing values to those three? That is, if we can't figure out how to not display inactive terms?
Germination Date
Good news: Germination date is always null!
Success rate
successRate in SAGE activity table is moving up to CSpace propagation successRate - will have to do data analysis to make sure there is one per propagation
SELECT
p.prop_id,
accession_number,
prop_date,
prop_type,
gh.growth_id,
gh.activity_type_id,
atc.activity_type,
gh.percent_success
FROM
dbo.propagation p
left outer join growth_history gh on (p.prop_id = gh.prop_id)
left outer join activity_type_code atc on (gh.activity_type_id=atc.activity_type_id)
order by prop_id, growth_id
When there is a non-null percent_success value it looks like the activity type is results. However, success_value can still be null even if activity type is "results"
The question is: Can there be different activity.percent_success values for a single propagation record?
SELECT
p.prop_id, accession_number,
atc.activity_type,
gh.percent_success
FROM
dbo.propagation p
left outer join growth_history gh on (p.prop_id = gh.prop_id)
left outer join activity_type_code atc on (gh.activity_type_id=atc.activity_type_id)
where percent_success is not null
order by prop_id, growth_id
Only 3 propagations have different results in growth_history records.
416 92.1029 results 5
416 92.1029 results 26
746 81.0829 results 71
746 81.0829 changed pot 99
746 81.0829 changed pot 99
2836 2002.0330 potted 5
2836 2002.0330 results 89
Concentration
** concentration in SAGE treatment table is potentially moving up to CSpace propagation concentration - will have to do data analysis to make sure there is one per propagation - or we can keep this as a hidden field in the activity group for historical data
SELECT
p.prop_id, accession_number,
atc.activity_type,
t.display_sequence treatment_seq,
tt.treatment_type_name,
t.concentration
FROM
dbo.propagation p
left outer join growth_history gh on (p.prop_id = gh.prop_id)
left outer join activity_type_code atc on (gh.activity_type_id=atc.activity_type_id)
left outer join treatment t on (gh.prop_id=t.prop_id and gh.growth_id=t.growth_id)
left outer join treatment_type tt on (t.treatment_type_id=tt.treatment_type_id)
where t.concentration is not null
order by prop_id, gh.growth_id, display_sequence
NOTE: Might use these queries to create merge fragments to merge with main propagation SQL. Otherwise have to find a way to get the non-null values, etc., in the main query.
NOTE: Might need to have the treatment_type come along in this query
567 90.0231 pretreat 2 dip and grow 1:5
567 90.0231 pretreat 3 IBA 0.8
610 83.1172 pretreat 2 dip and grow 1:3
610 83.1172 potted 2 bottom heat 1:3
610 83.1172 potted 3 intermittent mist 1:3
670 93.1051 pretreat 1 dip and grow 1:5
670 93.1051 pretreat 2 IBA talc 4.5
1402 94.0310 pretreat 2 dip and grow 1:10
1402 94.0310 pretreat 3 IBA talc 4.5
1478 91.1474 pretreat 1 dip and grow 1:5
1478 91.1474 potted 1 dip and grow 1:5
1551 77.0717 pretreat 2 dip and grow 1:5
1551 77.0717 pretreat 3 IBA 4.5
So I'm only about a 3rd through the 1375 records and there are 6 propagations that have different concentration values (and different treatment_types too)
Are the concentrations unique within an activity?
Here's an interesting query:
SELECT
p.prop_id,
tt.treatment_type_name,
t.concentration, count(*)
FROM
dbo.propagation p
left outer join growth_history gh on (p.prop_id = gh.prop_id)
left outer join activity_type_code atc on (gh.activity_type_id=atc.activity_type_id)
left outer join treatment t on (gh.prop_id=t.prop_id and gh.growth_id=t.growth_id)
left outer join treatment_type tt on (t.treatment_type_id=tt.treatment_type_id)
group by p.prop_id,
tt.treatment_type_name,
t.concentration
having count(*) > 1
order by prop_id
---
10/9
Combined activity-treatment records
select gh.prop_id, gh.growth_id, gh.activity_date, gh.activity_begin_date, gh.activity_end_date, gh.activity_type_id, atc.activity_type, gh.propagule_count, gh.pot_type, gh.pot_size_id, gh.percent_success, gh.growth_comments, gh.medium_name, gh.germination_date, gh.date_entered, gh.entered_staff_id, gh.last_change_staff_id, gh.last_change_date, t.display_sequence, t.treatment_type_id, tt.treatment_type_name, t.concentration, t.begin_date, t.end_date, t.propagule_count, t.treatment_notes, t.date_entered, t.entered_staff_id, t.last_change_date, t.last_change_staff_id from growth_history gh join activity_type_code atc on (gh.activity_type_id=atc.activity_type_id) left outer join treatment t on (gh.prop_id=t.prop_id and gh.growth_id=t.growth_id) left outer join treatment_type tt on (t.treatment_type_id=tt.treatment_type_id) order by gh.prop_id, gh.growth_id, t.display_sequence
ASKUCBG: Activity date in CSpace is calendar, but there are fuzzy dates in SAGE. 1058 dates of 18.5K are fuzzy.
Propagule count GH and T
Reconcile propagule count in GH and T:
SELECT
p.prop_id, accession_number,
atc.activity_type, gh.propagule_count,
t.display_sequence treatment_seq,
tt.treatment_type_name,
t.propagule_count
FROM
dbo.propagation p
left outer join growth_history gh on (p.prop_id = gh.prop_id)
left outer join activity_type_code atc on (gh.activity_type_id=atc.activity_type_id)
left outer join treatment t on (gh.prop_id=t.prop_id and gh.growth_id=t.growth_id)
left outer join treatment_type tt on (t.treatment_type_id=tt.treatment_type_id)
where gh.propagule_count is not null or t.propagule_count is not null
order by prop_id, gh.growth_id, display_sequence
> 5000 records returned, mostly GH by the looks of it.
where gh.propagule_count is not null or t.propagule_count is not null: 16216
where gh.propagule_count is not null and t.propagule_count is not null: 2
where gh.propagule_count is not null: 16216
where 2.propagule_count is not null: 2
For the 2 where t.propagule_count is not null, the values are identical in GH. Therefore use GH only.
Reconcile propagation.number_started with gh.propagule_count
SELECT
p.prop_id,
accession_number,
prop_date,
prop_type,
number_started,
gh.activity_type_id,
atc.activity_type,
gh.propagule_count
FROM
dbo.propagation p
left outer join growth_history gh on (p.prop_id = gh.prop_id)
left outer join activity_type_code atc on (gh.activity_type_id=atc.activity_type_id)
order by prop_id, growth_id
where p.number_started is not null or gh.propagule_count is not null: 13556
where p.number_started is not null and gh.propagule_count is not null: 10136
where p.number_started is not null: 11011
where gh.propagule_count is not null: 12681
where p.number_started = gh.propagule_count: 7252
where p.number_started <> gh.propagule_count: 2884
ASKUCBG: What to do about 2284 where the values are not equal? e.g.,
22 85.1541 November 20 1985 seed 15 2 changed pot 1
22 85.1541 November 20 1985 seed 15 6 changed location 1
22 85.1541 November 20 1985 seed 15 9 dead 1
6771 96.1050 Oct 19, 2010 cutting 20 13 potted 11
6771 96.1050 Oct 19, 2010 cutting 20 13 potted 1
6773 96.1051 Oct 26, 2010 cutting 20 13 potted 16
6773 96.1051 Oct 26, 2010 cutting 20 13 potted 1
Do we need to keep number started in top level propagation record?
Instead? Get the union of activity records and treatment records? Try this out SQL.
March 5, 2013
Here's the SQL we are currently using to bring together the activity and treatment records:
-- activity records with good dates select p.accession_number, p.prop_type, 'activity' record_type, gh.prop_id prop_id, gh.growth_id growth_id, 0 treatment_id, atc.activity_type type_name, null treatment_category, gh.activity_date event_date, convert(char(12), dateadd(dd,(gh.activity_begin_date - 2415021), convert(datetime, '1900-01-01', 103)), 101) begin_date, convert(char(12), dateadd(dd,(gh.activity_end_date - 2415021), convert(datetime, '1900-01-01', 103)), 101) end_date, gh.propagule_count propagule_count, gh.growth_comments comments, null concentration, gh.pot_type pot_type, gh.pot_size_id pot_size_id, gh.percent_success percent_success, gh.medium_name medium_name, gh.germination_date germination_date, gh.date_entered date_entered, gh.entered_staff_id entered_staff_id, gh.last_change_date last_change_date, gh.last_change_staff_id last_change_staff_id from growth_history gh, activity_type_code atc, propagation p where gh.activity_type_id=atc.activity_type_id and gh.prop_id=p.prop_id union -- treatment records look OK too: select p.accession_number, p.prop_type, 'treatment' record_type, t.prop_id prop_id, t.growth_id growth_id, t.display_sequence treatment_id, tt.treatment_type_name type_name, tt.treatment_category treatment_category, null event_date, convert(char(12), t.begin_date, 101) begin_date, convert(char(12), t.end_date, 101) end_date, t.propagule_count propagule_count, t.treatment_notes comments, t.concentration concentration, null pot_type, null pot_size_id, null percent_success, null medium_name, null germination_date, t.date_entered date_entered, t.entered_staff_id entered_staff_id, t.last_change_date last_change_date, t.last_change_staff_id last_change_staff_id from treatment t, treatment_type tt, propagation p where t.treatment_type_id=tt.treatment_type_id and t.prop_id=p.prop_id order by prop_id, growth_id, treatment_id