Link to home
Create AccountLog in
Avatar of Teknosoft
Teknosoft

asked on

Linq Grouping based on range

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

Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Hi Teknosoft;

One way to do what you need is two have three queries one for 30, 60 and over 90 days as shown in the cod snippet below.

Fernando
Dim dt2 As DateTime = DateTime.Now

Dim qry30 = From tr In DataContext.TransactionMain _
            Where tr.TransactionDate < dt2 _
            Let DaysAging = SqlMethods.DateDiffDay(tr.DueDate, dt2) _
            Where DaysAging > 0 AndAlso DaysAging < 31 _
            Select tr.Customer.CustomerID,tr.Amount, DaysAging        

Dim qry60 = From tr In DataContext.TransactionMain _
            Where tr.TransactionDate < dt2 _
            Let DaysAging = SqlMethods.DateDiffDay(tr.DueDate, dt2) _
            Where DaysAging > 30 AndAlso DaysAging < 61 _
            Select tr.Customer.CustomerID,tr.Amount, DaysAging                    

Dim qry90 = From tr In DataContext.TransactionMain _
            Where tr.TransactionDate < dt2 _
            Let DaysAging = SqlMethods.DateDiffDay(tr.DueDate, dt2) _
            Where DaysAging > 60 _
            Select tr.Customer.CustomerID,tr.Amount, DaysAging

Open in new window

Avatar of Teknosoft
Teknosoft

ASKER

Sorry for the late response,

I have looked at your answer, and I did have something similar in place, but somehow it runs very slow and I have multipple entities that I fetch data from. I have look at other solution such as taking the number of days

                    Dim daysAging3 = From tr In DataContext.TransactionMain _
                                    Where tr.Customer.AccountNumber = Me.txtEntryField.Text _
                                    Group tr By mKey = (DiffDays(tr.DueDate, dt2) / 30) Into g = Group _
                                    Select New With {.DaysAging = mKey, .Value = g.Sum(Function(x) x.Amount)}

Open in new window


but the result of of mKey is decimal i.e. result of (7/30) = 0.2333
and What I am thinkg of is that to convert using math.floor to the lower number, but linq does not seem to like the math functions.

any Idea on how to accomplish this?
if I can get the function to convert the result of (DiffDays(tr.DueDate, dt2) / 30) to floor then i would get numbers that would represent my aging period. I have seen this example  in MSDN here is a link
http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/7c86a3f6-2517-4943-8c14-50147c5420a6/
Bust some how I can't get rid of the decimals of the calculated result
Are you using Linq to SQL or are you using Linq to Entity Framework?
Linq to Entities
Hi Teknosoft;

I have modified your last query. I am using the Entity Framework version of DiffDays

Fernando
' Add a Reference to the dll System.Data.Entity also add the Imports statment:
Imports System.Data.Objects

Dim daysAging3 = From tr In DataContext.TransactionMain _
                 Where tr.Customer.AccountNumber = Me.txtEntryField.Text _
                 Group tr By mKey = (EntityFunctions.DiffDays(tr.DueDate, dt2) / 30) Into g = Group _
                 Select New With {.DaysAging = mKey, .Value = g.Sum(Function(x) x.Amount)}

Open in new window

Hello Fernando,

I have that refrenced on my class
Imports System.Data.Objects.EntityFunctions
that had allowed me to use the DiffDays

But my second question maybe it was not very clear.
The result of the query would like
DaysAging=0.96666666666666667, Value=372D
DaysAging=0.93333333333333335, Value=3891.03D
DaysAging=1.2666666666666666, Value=1310.12D
DaysAging=1.6333333333333333, Value=2109.42D

The daysAging is a result of (EntityFunctions.DiffDays(tr.DueDate, dt2) / 30)
and what I need to see if I can use Math.Floor((EntityFunctions.DiffDays(tr.DueDate, dt2) / 30)) to allow the Days Aging to be similar to  
DaysAging=0, Value=372D
DaysAging=0, Value=3891.03D
DaysAging=1, Value=1310.12D
DaysAging=1, Value=2109.42D
and when they are grouped they will produce a result similar to
DaysAging=0, Value=4263.03D
DaysAging=1, Value=3419.54D

but somehow that Math.Floor function does not seem to work with it sincet he result of DiffDays(tr.DueDate, dt2) is integer and when it gets devided the result is double
it will give me an error when I try Math.Floor(DiffDays(tr.DueDate, dt2) / 30)
Overload resolution failed because no accessible 'Floor' can be called without a narrowing conversion:
    'Public Shared Function Floor(d As Double) As Double': Argument matching parameter 'd' narrows from 'Double?' to 'Double'.
    'Public Shared Function Floor(d As Decimal) As Decimal': Argument matching parameter 'd' narrows from 'Double?' to 'Decimal'
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks for the reply,

I used this approach since I had seen a post on MSDN that uses similar method in C#
Here is the post MSDN POST
Do you want to age by numbers of days from DateAssigned, or using calendar months? If the former:


DateTime dt = DateTime.Now;


var q = from a in dc.Accounts

group a by (dt - a.DateAssigned).Days / 30 into w

select new { Status = w.Key, Accounts = w.Count() };


If the latter:


var dt = DateTime.Today;
dt = dt.AddDays (1-dt.Day);

var q = from a in dc.Accounts
group a by (dt - a.DateAssigned.Date.AddDays (1 - a.DateAssigned.Day)).Days / 30 into w
select new { Status = w.Key, Accounts = w.Count() };


This will return a status of 0 for 0-30 days, 1 for 30-60, 2 for 60-90, etc.

Joe


But I guess I need to use different approach for get my results
I just tried your code and it seemed to be working !!!! I need to validate my data now based on the results
it seems that integer devision is doing the trick. great job I wasn't sure if this was supported in linq
Not a problem, glad to help.  ;=)