Search code examples
sqlpostgresqldategroup-bydate-difference

GROUP BY based on dates and number of months

I have a PostgreSQL (many) table with monitoring data. The table contains an ID and Date column. Now I need to classify (group) all this monitoring data in strata (groups) of 6 months. So, in case a monitoring occurs more than 6 months later than the latest date, it is considered as a new monitoring and should be added to a new 'instance group'. If the monitoring occurred WITHIN 6 months of the latest date, it is considered as the same monitoring and added to the first instance group.

So basically I need to group the data based on ID and Date by counting the number of months between the Dates. All dates that are < 6 months in between go into one group (with the same ID). Now here is the difficulty: In case a Date is more than 6 months (> 6 months) ahead of the (second latest) previous date the row needs to be added to a new/seperate Date group (but of the same ID). Below the input and desired output:

enter image description here

enter image description here

Of course, I need to calculate specific results (SUMS/AVERAGES) for each monitoring instance group, but that should be fairly easy if I know how to create the groups. Any idea how I can do this best?

Solution

  • In the end I solved it. I used LAG in a Window to calculate the distance (in days) between the dates. Then I used CASE to label the number of days into a monitoring group (eg. 2021-1 = less then 180 days of difference, 2021-2 = between 181 and 360 days of difference, etc). Then I grouped the labels. Worked fine. Thanks for the help Slava!