,
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 |