We help IT Professionals succeed at work.
Get Started

Linq  Grouping based on range

Last Modified: 2013-11-11
Hello experts,

I have a linq query that I need to do a group by based on range of dates
Dim dt2=datetime.now

Dim qry = From tr In DataContext.TransactionMain _
Where tr.TransactionDate < dt2 _
Let DaysAging = DiffDays(tr.DueDate, dt2) _
Select tr.Customer.CustomerID,tr.Amount, DaysAging

Open in new window

The above query will return a list of transactions and the days aging exmaple

CustomerID      Amount     DaysAging
1000                    100.00              2
1000                      50.00              16
1000                    120.00              20
1000                      75.00              34
1000                    110.00              41
1000                      80.00              65
1200                      35.00              37
1200                      40.00              40

The desired output needed is to group and sum the abount of each customer transaction based on a rangeed period. like transactions that have aging days between 1-30, 31-60, 61-90, 91+
Aging Group   Max Days
1                            30
2                            60
3                            90
4                        9999

so the output needed is to be like
CustomerID      Amount     Aging Group
1000                    270.00              1
1000                    185.00              2
1000                      80.00              3
1200                      75.00              2

Watch Question
Distinguished Expert 2017
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE