1

Long time lurker, first-time poster. I have been looking for a solution to this problem for a while and can't quite think of what to search for anymore.

I work for a farm and we have this farm management system that allows people to put in activities (such as planting/irrigating/etc...). I'm ultimately trying to calculate every activity day from planting so each field is comparable to each other. The current problem I'm running into is calculating the 'season' timeline as we might have multiple crop plantings in a field within the year.

BTW Postgres used as my DB

Essentially my table looks like this: Crop Plantings Table (simplified)

Ideally, I would like to somehow query this to create a date range seasonality table so I could cross-reference all other activities to see what "season" the activity falls under and as such can grab the correct planting date.

I'm thinking the table would look something like:

Ideal Season Ranges

I assume some kind of log transform query for user activities on websites might work similarly to categorize time ranges, but I haven't found anything helpful yet.

Let me know if you have any thoughts/ideas!

Thank you!

2
  • Welcome! Can you please provide sample data in the form of create table and insert statements -- possibly at dbfiddle.uk – Colin 't Hart Jan 29 '20 at 18:49
  • Looks like you need lead, partitioned by paddock, ordered by date. – Colin 't Hart Jan 29 '20 at 18:50
0

It seems you need in simple

SELECT Paddock, 
       Date_Time_Completed Season_Start,
       COALESCE(LEAD(Date_Time_Completed) OVER (PARTITION BY Paddock
                                                ORDER BY Date_Time_Completed),
                CURRENT_DATE) Season_End,
       Crop,
       YEAR(Date_Time_Completed) 
           || '_' 
           || ROW_NUMBER() OVER (PARTITION BY Paddock
                                 ORDER BY Date_Time_Completed) Season_Name
FROM source_table
1
  • Incredible! I had to adjust some syntax for postgres, but this works like a charm! Thank you so much!! – Emery Silberman Jan 29 '20 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.