Created
December 18, 2012 05:52
-
-
Save lewang/4325392 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT activities.id AS id, | |
| COALESCE(activities.completed_at, activities.started_at, activities.assigned_at, activities.created_at) AS sql_dt, | |
| author_companies.name AS author_name, | |
| companies.name AS farm_name, | |
| properties.region, | |
| paddocks.cropping_method, | |
| paddock_summaries.seeding_date, | |
| crop_varieties.name AS variety, | |
| tech_strings.tech AS tech_name, | |
| COALESCE(product_categories.name_en_au, product_categories.name) AS product_category, | |
| products.name AS product_name, | |
| pest_1, | |
| pest_2, | |
| pest_3, | |
| pest_4, | |
| pest_5, | |
| pest_6, | |
| activity_paddocks.area AS sql_ha, | |
| activity_inputs.units_per_area_scalar AS units_per_ha | |
| FROM "activities" | |
| INNER JOIN "activity_inputs" ON "activity_inputs"."activity_id" = "activities"."id" | |
| INNER JOIN "products" ON "products"."id" = "activity_inputs"."product_id" | |
| INNER JOIN "product_categories_products" ON "product_categories_products"."product_id" = "products"."id" | |
| INNER JOIN "product_categories" ON "product_categories"."id" = "product_categories_products"."product_category_id" | |
| INNER JOIN "activity_paddocks" ON "activity_paddocks"."activity_id" = "activities"."id" | |
| INNER JOIN "paddocks" ON "paddocks"."id" = "activity_paddocks"."paddock_id" | |
| INNER JOIN "properties" ON "properties"."id" = "paddocks"."property_id" | |
| INNER JOIN "paddock_crops" ON "paddock_crops"."activity_paddock_id" = "activity_paddocks"."id" | |
| INNER JOIN "crop_varieties" ON "crop_varieties"."id" = "paddock_crops"."crop_variety_id" | |
| INNER JOIN "segments" ON "segments"."id" = "crop_varieties"."segment_id" | |
| INNER JOIN companies AS author_companies ON activities.author_company_id = author_companies.id | |
| INNER JOIN companies ON activities.company_id = companies.id | |
| LEFT OUTER JOIN paddock_summaries ON activity_paddocks.paddock_id = paddock_summaries.paddock_id | |
| AND activity_paddocks.planned_season_id = paddock_summaries.planned_season_id | |
| LEFT OUTER JOIN | |
| (SELECT crop_variety_id, | |
| ARRAY_TO_STRING(ARRAY_AGG(DISTINCT crop_technologies.name), ' ') AS tech | |
| FROM crop_varieties | |
| INNER JOIN crop_technologies_crop_varieties ON crop_varieties.id = crop_technologies_crop_varieties.crop_variety_id | |
| INNER JOIN crop_technologies ON crop_technologies_crop_varieties.crop_technology_id = crop_technologies.id | |
| GROUP BY crop_variety_id) AS tech_strings ON crop_varieties.id = tech_strings.crop_variety_id | |
| LEFT OUTER JOIN | |
| (SELECT activity_id, | |
| MAX(pest_1) AS pest_1, | |
| MAX(pest_2) AS pest_2, | |
| MAX(pest_3) AS pest_3, | |
| MAX(pest_4) AS pest_4, | |
| MAX(pest_5) AS pest_5, | |
| MAX(pest_6) AS pest_6 | |
| FROM | |
| (SELECT activity_id, CASE position WHEN 0 THEN pests.name ELSE NULL END AS pest_1, CASE position WHEN 1 THEN pests.name ELSE NULL END AS pest_2, CASE position WHEN 2 THEN pests.name ELSE NULL END AS pest_3, CASE position WHEN 3 THEN pests.name ELSE NULL END AS pest_4, CASE position WHEN 4 THEN pests.name ELSE NULL END AS pest_5, CASE position WHEN 5 THEN pests.name ELSE NULL END AS pest_6 | |
| FROM activity_problems | |
| INNER JOIN pests ON activity_problems.pest_id = pests.id) AS pest_n | |
| GROUP BY activity_id) AS pest_positions ON activities.id = pest_positions.activity_id | |
| WHERE "activities"."author_company_id" IN (9852, | |
| 474, | |
| 1036, | |
| 12052, | |
| 11851, | |
| 11893, | |
| 12763, | |
| 12021, | |
| 10629, | |
| 9883, | |
| 485, | |
| 2496, | |
| 10125, | |
| 12762, | |
| 9555, | |
| 12443, | |
| 731, | |
| 12140, | |
| 16252) | |
| AND "paddocks"."id" IN | |
| (SELECT DISTINCT ON (companies.name, | |
| properties.name, | |
| paddocks.name, | |
| paddocks.id) paddocks.id | |
| FROM "paddocks" | |
| INNER JOIN "properties" ON "properties"."id" = "paddocks"."property_id" | |
| INNER JOIN "companies" ON "companies"."id" = "properties"."company_id" | |
| WHERE (companies.id IN (-1, | |
| 594, | |
| 474, | |
| 10239, | |
| 599, | |
| 597, | |
| 10684, | |
| 593, | |
| 10685, | |
| 592, | |
| 595, | |
| 13914, | |
| 14163, | |
| 9883, | |
| 773, | |
| 774, | |
| 790, | |
| 791, | |
| 792, | |
| 2593, | |
| 2612, | |
| 775, | |
| 776, | |
| 777, | |
| 778, | |
| 779, | |
| 780, | |
| 781, | |
| 782, | |
| 783, | |
| 784, | |
| 786, | |
| 787, | |
| 788, | |
| 789, | |
| 485, | |
| 16163, | |
| 16167, | |
| 16410, | |
| 10188, | |
| 10203, | |
| 10204, | |
| 10205, | |
| 10629, | |
| 13447, | |
| 9555, | |
| 9259, | |
| 9696, | |
| 9697, | |
| 9698, | |
| 9699, | |
| 9700, | |
| 9701, | |
| 9702, | |
| 9703, | |
| 9704, | |
| 9705, | |
| 9706, | |
| 9707, | |
| 9708, | |
| 9709, | |
| 9813, | |
| 10261, | |
| 12135, | |
| 10343, | |
| 9666, | |
| 9812, | |
| 9815, | |
| 9832, | |
| 9814, | |
| 9816, | |
| 9817, | |
| 9818, | |
| 9819, | |
| 9820, | |
| 9821, | |
| 9822, | |
| 9823, | |
| 9824, | |
| 9825, | |
| 9826, | |
| 9827, | |
| 9828, | |
| 9829, | |
| 9830, | |
| 9831, | |
| 9833, | |
| 9834, | |
| 9835, | |
| 9836, | |
| 12978, | |
| 12763, | |
| 12845, | |
| 12992, | |
| 12994, | |
| 12996, | |
| 12997, | |
| 12998, | |
| 12999, | |
| 13000, | |
| 13001, | |
| 13002, | |
| 13015, | |
| 13016, | |
| 13018, | |
| 13019, | |
| 13020, | |
| 13021, | |
| 13041, | |
| 13042, | |
| 13053, | |
| 13081, | |
| 13082, | |
| 13119, | |
| 13120, | |
| 486, | |
| 598, | |
| 600, | |
| 10410, | |
| 10411, | |
| 494, | |
| 2613, | |
| 2614, | |
| 2615, | |
| 10738, | |
| 12145, | |
| 10739, | |
| 12967, | |
| 12968, | |
| 12984, | |
| 13084, | |
| 12140, | |
| 12184, | |
| 12253, | |
| 12254, | |
| 12255, | |
| 12256, | |
| 12259, | |
| 12260, | |
| 12261, | |
| 12262, | |
| 493, | |
| 589, | |
| 591, | |
| 560, | |
| 558, | |
| 564, | |
| 565, | |
| 569, | |
| 570, | |
| 9287, | |
| 587, | |
| 590, | |
| 10107, | |
| 559, | |
| 588, | |
| 2484, | |
| 13127, | |
| 1036, | |
| 2482, | |
| 2483, | |
| 2485, | |
| 2486, | |
| 2487, | |
| 2488, | |
| 2489, | |
| 2490, | |
| 2491, | |
| 2493, | |
| 2494, | |
| 2495, | |
| 2492, | |
| 2481, | |
| 12383, | |
| 12384, | |
| 12385, | |
| 12386, | |
| 12425, | |
| 12426, | |
| 12427, | |
| 731, | |
| 797, | |
| 796, | |
| 800, | |
| 801, | |
| 802, | |
| 803, | |
| 798, | |
| 799, | |
| 804, | |
| 805, | |
| 806, | |
| 785, | |
| 822, | |
| 821, | |
| 820, | |
| 819, | |
| 817, | |
| 816, | |
| 815, | |
| 808, | |
| 809, | |
| 810, | |
| 811, | |
| 812, | |
| 813, | |
| 814, | |
| 10278, | |
| 818, | |
| 12443, | |
| 12462, | |
| 2496, | |
| 2603, | |
| 2604, | |
| 2605, | |
| 2606, | |
| 2607, | |
| 2608, | |
| 2599, | |
| 2600, | |
| 2601, | |
| 2602, | |
| 4305, | |
| 4307, | |
| 4308, | |
| 4309, | |
| 4310, | |
| 4311, | |
| 4312, | |
| 4313, | |
| 4314, | |
| 4315, | |
| 4316, | |
| 4317, | |
| 10384, | |
| 4306, | |
| 10383, | |
| 11893, | |
| 12046, | |
| 12323, | |
| 12324, | |
| 12325, | |
| 12326, | |
| 12327, | |
| 12328, | |
| 12329, | |
| 12330, | |
| 12331, | |
| 12332, | |
| 12333, | |
| 12334, | |
| 12980, | |
| 13051, | |
| 13052, | |
| 13054, | |
| 13056, | |
| 13057, | |
| 13058, | |
| 13060, | |
| 13066, | |
| 13112, | |
| 13126, | |
| 13140, | |
| 13178, | |
| 13181, | |
| 13245, | |
| 13247, | |
| 13320, | |
| 13510, | |
| 13222, | |
| 13220, | |
| 13219, | |
| 13831, | |
| 16074, | |
| 561, | |
| 584, | |
| 585, | |
| 610, | |
| 562, | |
| 611, | |
| 583, | |
| 563, | |
| 566, | |
| 567, | |
| 568, | |
| 571, | |
| 572, | |
| 573, | |
| 574, | |
| 575, | |
| 577, | |
| 578, | |
| 579, | |
| 581, | |
| 582, | |
| 596, | |
| 10686, | |
| 13150, | |
| 10056, | |
| 12629, | |
| 14381, | |
| 13767, | |
| 12052, | |
| 13187, | |
| 13188, | |
| 13189, | |
| 13190, | |
| 13191, | |
| 13192, | |
| 13193, | |
| 13194, | |
| 13195, | |
| 13196, | |
| 13197, | |
| 13198, | |
| 13199, | |
| 11851, | |
| 12238, | |
| 12379, | |
| 12484, | |
| 12485, | |
| 12486, | |
| 12749, | |
| 12803, | |
| 12938, | |
| 12991, | |
| 16095, | |
| 12021, | |
| 12449, | |
| 12550, | |
| 12551, | |
| 12552, | |
| 12554, | |
| 12555, | |
| 12556, | |
| 12557, | |
| 12558, | |
| 12559, | |
| 12560, | |
| 12561, | |
| 12562, | |
| 12563, | |
| 12564, | |
| 10125, | |
| 10174, | |
| 10184, | |
| 10196, | |
| 10197, | |
| 10198, | |
| 10199, | |
| 10201, | |
| 10202, | |
| 2617, | |
| 2618, | |
| 2619, | |
| 2620, | |
| 10057, | |
| 14333, | |
| 11412, | |
| 11577, | |
| 12762, | |
| 12850, | |
| 12904, | |
| 12905, | |
| 12906, | |
| 12907, | |
| 12908, | |
| 12909, | |
| 13022, | |
| 13023, | |
| 13024, | |
| 13025, | |
| 13026, | |
| 13027, | |
| 9852)) | |
| ORDER BY paddocks.id ASC, companies.name ASC, paddocks.name ASC) | |
| AND (segments.name ILIKE '%cotton%') | |
| AND ((((activities.type IN ('ActualActivity')) | |
| AND (activities.visible = TRUE)) | |
| AND (activities.assigned_at >= '2012-01-01 00:00:00')) | |
| AND (activities.assigned_at <= '2012-12-31 23:59:59')) | |
| AND (coalesce(activities.template, FALSE) = FALSE) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment