Hi,
I have a model which contains the following elements:
- periods table - contains period interval for every type (date, week, month, year ...) along with attributes such as periodMin, periodMax
- periodDay table - lays the relationship between date type periods and periods with higher rank (week, month, year...)
My challenge is to present the value of the last available date for each period
so i have the following table
| periodId | PeriodNm | date | Base |
| 1 | 2016/jan | 1-jan | 1916 |
| 1 | 2016/jan | 2-jan | 1239 |
| 1 | 2016/jan | 3-jan | 1223 |
| 1 | 2016/jan | 4-jan | 1110 |
| 1 | 2016/jan | 5-jan | 1170 |
| 1 | 2016/jan | 6-jan | 1992 |
| 1 | 2016/jan | 7-jan | 1041 |
| 1 | 2016/jan | 8-jan | 1202 |
| 1 | 2016/jan | 9-jan | 1675 |
| 1 | 2016/jan | 10-jan | 1156 |
| 1 | 2016/jan | 11-jan | 1573 |
| 1 | 2016/jan | 12-jan | 1771 |
| 1 | 2016/jan | 13-jan | 1553 |
| 1 | 2016/jan | 14-jan | 1319 |
| 1 | 2016/jan | 15-jan | 1124 |
| 1 | 2016/jan | 16-jan | 1403 |
| 1 | 2016/jan | 17-jan | 1147 |
| 1 | 2016/jan | 18-jan | 1447 |
| 1 | 2016/jan | 19-jan | 1108 |
| 1 | 2016/jan | 20-jan | 1198 |
| 1 | 2016/jan | 21-jan | 1747 |
| 1 | 2016/jan | 22-jan | 1616 |
| 1 | 2016/jan | 23-jan | 1067 |
| 1 | 2016/jan | 24-jan | 1408 |
| 1 | 2016/jan | 25-jan | 1271 |
| 1 | 2016/jan | 26-jan | 1935 |
| 1 | 2016/jan | 27-jan | 1759 |
| 1 | 2016/jan | 28-jan | 1074 |
| 1 | 2016/jan | 29-jan | 1314 |
| 1 | 2016/jan | 30-jan | 1104 |
| 1 | 2016/jan | 31-jan | 1996 |
| 2 | 2016/feb | 1-feb | 1404 |
| 2 | 2016/feb | 2-feb | 1143 |
| 2 | 2016/feb | 3-feb | 1978 |
| 2 | 2016/feb | 4-feb | 1430 |
| 2 | 2016/feb | 5-feb | 1492 |
| 2 | 2016/feb | 6-feb | 1038 |
| 2 | 2016/feb | 7-feb | 1503 |
| 2 | 2016/feb | 8-feb | 1790 |
| 2 | 2016/feb | 9-feb | 1515 |
| 2 | 2016/feb | 10-feb | 1127 |
| 2 | 2016/feb | 11-feb | 1248 |
| 2 | 2016/feb | 12-feb | 1776 |
| 2 | 2016/feb | 13-feb | 1121 |
| 2 | 2016/feb | 14-feb | 1337 |
| 2 | 2016/feb | 15-feb | 1973 |
| 2 | 2016/feb | 16-feb | 1622 |
| 2 | 2016/feb | 17-feb | 1206 |
| 2 | 2016/feb | 18-feb | 1646 |
| 2 | 2016/feb | 19-feb | 1268 |
| 2 | 2016/feb | 20-feb | 1548 |
| 2 | 2016/feb | 21-feb | 1082 |
| 2 | 2016/feb | 22-feb | 1584 |
| 2 | 2016/feb | 23-feb | 1673 |
| 2 | 2016/feb | 24-feb | 1501 |
| 2 | 2016/feb | 25-feb | 1314 |
| 2 | 2016/feb | 26-feb | 1532 |
| 2 | 2016/feb | 27-feb | 1519 |
| 2 | 2016/feb | 28-feb | 1560 |
| 2 | 2016/feb | 29-feb | 1694 |
| 3 | 2016/mar | 1-mrt | 1731 |
| 3 | 2016/mar | 2-mrt | 1458 |
| 3 | 2016/mar | 3-mrt | 1439 |
| 3 | 2016/mar | 4-mrt | 1454 |
| 3 | 2016/mar | 5-mrt | 1895 |
| 3 | 2016/mar | 6-mrt | 1831 |
| 3 | 2016/mar | 7-mrt | 1055 |
| 3 | 2016/mar | 8-mrt | 1805 |
| 3 | 2016/mar | 9-mrt | 1792 |
| 3 | 2016/mar | 10-mrt | 1325 |
| 3 | 2016/mar | 11-mrt | 1776 |
| 3 | 2016/mar | 12-mrt | 1365 |
I need to see the following results
periodId 1, 2016/jan , 31-1-2016, 1996
period 2, 2016/feb, 29-2-2016 , 1694
Any idea's how this could be done?
Help much appreciated
Dror
