Please consider this data:
Year Month Code Value
--------------------------------
2011 1 1 100
2011 2 1 200
2011 3 1 250
2011 1 2 70
2011 2 2 50
2011 3 2 80
I want to achieve this result:
Code FromYearMonth ToYearMonth Difference
----------------------------------------------------
1 2011-1 2011-2 100 <----200 - 100
1 2011-2 2011-3 50 <----250 - 200
2 2011-1 2011-2 -20 <----50 - 70
2 2011-2 2011-3 30 <----80 - 50
How can I create this result with LINQ
. Thanks
CodePudding user response:
You need to join datasource with itself to get all pairs of (monthStart, monthEnd) records, then calculate difference:
from monthStart in dataSource
join monthEnd in dataSource
on new { monthStart.Code, date = monthStart.Year * 12 monthStart.Month }
equals { monthEnd.Code, date = monthEnd.Year * 12 monthEnd.Month - 1}
select new {
monthStart.Code,
FromYear = monthStart.Year,
FromMonth = monthStart.Month,
ToYear = monthEnd.Year,
ToMonth = monthEnd.Month,
Difference = monthEnd.Value - monthStart.Value
}