,
Hi,
Requirment:
Weekbegindate is Monday
weekenddate is Sunday
Weekbegindate should capture latest Numerator and Denominator within that week (Monday to Sunday)
I need help on calculating WeekBeginDate,ProjectID, sum(numerator) as Numerator ,Sum(Denominator) as Denominator based on below input dataset:
| Date | ProjectID | Numerator | Denominator |
|---|---|---|---|
| 1/1/2018 12:00:00 AM | P001 | 1 | 100 |
| 1/2/2018 12:00:00 AM | P001 | 2 | 100 |
| 1/3/2018 12:00:00 AM | P001 | 4 | 100 |
| 1/4/2018 12:00:00 AM | P001 | 5 | 100 |
| 1/5/2018 12:00:00 AM | P001 | 2 | 100 |
| 1/6/2018 12:00:00 AM | P001 | 34 | 100 |
| 1/7/2018 12:00:00 AM | P001 | 5 | 100 |
| 1/8/2018 12:00:00 AM | P001 | 6 | 100 |
| 1/9/2018 12:00:00 AM | P001 | 7 | 100 |
| 1/10/2018 12:00:00 AM | P001 | 8 | 100 |
| 1/11/2018 12:00:00 AM | P001 | 1 | 100 |
| 1/12/2018 12:00:00 AM | P001 | 12 | 100 |
| 1/15/2018 12:00:00 AM | P001 | 78 | 100 |
| 1/16/2018 12:00:00 AM | P001 | 1 | 100 |
| 1/17/2018 12:00:00 AM | P001 | 25 | 100 |
| 1/18/2018 12:00:00 AM | P001 | 1 | 100 |
Final output should be:
| WeekBeginDate | ProjectID | Numerator | Denominator |
|---|---|---|---|
| 1/1/2018 12:00:00 AM | P001 | 5 | 100 |
| 1/8/2018 12:00:00 AM | P001 | 12 | 100 |
| 1/15/2018 12:00:00 AM | P001 | 1 | 100 |