I have a calender table that include ISO_Year4WkPeriod. For people that aren't familiar with 4WkPeriods. Every 4 weeks of the year is a period. For example:
ISO_Year4WkPeriod 2021-01 = 2021-01-04/2021-01-31
I want to create a query that gives me the last date of each ISO_Year4WkPeriod. So in this example the desired output would be 2021-01-31
Does someone know how to do this?
| Date | ISO_Year4wkPeriod |
|---|---|
| 2021-01-04 | 2021-01 |
| 2021-01-05 | 2021-01 |
| 2021-01-06 | 2021-01 |
| 2021-01-07 | 2021-01 |
| ..... | ..... |
| 2021-01-29 | 2021-01 |
| 2021-01-30 | 2021-01 |
| 2021-01-31 | 2021-01 |
| 2021-02-01 | 2021-02 |
CodePudding user response：
If you have all the data in your table just do a group by ISO_Year4wkPeriod and take the MAX(Date).
So your query would look something similar to this: select MAX(Date) from <table> group by ISO_Year4wkPeriod